Locks Set by Different SQL State

SELECT ... FROM
is a consistent read, reading a snapshot of the database and setting no locks unless the transaction isolation level is set to SERIALIZABLE
. ForSERIALIZABLE
level, the search sets shared next-key locks on the index records it encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.

SELECT ... FROM ... LOCK IN SHARE MODE
sets shared next-key locks on all index records the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.

SELECT ... FROM ... FOR UPDATE
sets an exclusive next-key lock on every record the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.
For index records the search encounters, SELECT ... FROM ... FOR UPDATE
blocks other sessions from doing SELECT ... FROM ... LOCK IN SHARE MODE
or from reading in certain transaction isolation levels. Consistent reads ignore any locks set on the records that exist in the read view.

UPDATE ... WHERE ...
sets an exclusive next-key lock on every record the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.

When UPDATE
modifies a clustered index record, implicit locks are taken on affected secondary index records. The UPDATE
operation also takes shared locks on affected secondary index records when performing duplicate check scans prior to inserting new secondary index records, and when inserting new secondary index records.

DELETE FROM ... WHERE ...
sets an exclusive next-key lock on every record the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.

INSERT
sets an exclusive lock on the inserted row. This lock is an index-record lock, not a next-key lock (that is, there is no gap lock) and does not prevent other sessions from inserting into the gap before the inserted row.