PostgreSQL is using an engine called Multiversion concurrency control or (MVCC for short). Such way of operating is used by other Databases such as: Oracle and MySQL (InnoDB). In a nutshell, this engine allows you to store different versions of the data at the same time in the shared buffers, depending on the consistency needs which your query must fulfill.

In PostgreSQL, we have several types of locks: Firstly the locks are separated between:

• Table Locks
• Row Locks

Let's start with the Table Locks

• Access Share: Taken when person performs select.
• Row Share: Taken when Select for UPDATE / for SHARE is issued.
• Row Exclusive: Taken when DML Operations (Update / Insert / Delete) is issued.
• Share Update Exclusive: This lock is imposed when one of the following: CREATE INDEX CONCURRENTLY, ANALYZE/ALTER TABLE (SOME FORMS), VALIDATE and VACUUM (but not FULL).
• Share: When Index is created.
• Share Row Exlusive: When CREATE TRIGGER and some forms of Alter table
• EXCLUSIVE & ACCESS EXCLUSIVE: The most restrict ones by far. They are taken upon DROP TABLE request.

Below you can see also, conflict matrix for all of the above Lock modes:

As I already said, a part of the Table locks, there are Row locks:

In addition to table-level locks, there are row-level locks, which are listed as below with the contexts in which they are used automatically by PostgreSQL. Note that a transaction can hold conflicting locks on the same row, even in different subtransactions; but other than that, two transactions can never hold conflicting locks on the same row. Row-level locks do not affect data querying; they block only writers and lockers to the same row.

• FOR UPDATE: FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for update. This prevents them from being locked, modified or deleted by other transactions until the current transaction ends. That is, other transactions that attempt UPDATE, DELETE, SELECT FOR UPDATE, SELECT FOR NO KEY UPDATE, SELECT FOR SHARE or SELECT FOR KEY SHARE of these rows will be blocked until the current transaction ends; conversely, SELECT FOR UPDATE will wait for a concurrent transaction that has run any of those commands on the same row, and will then lock and return the updated row (or no row, if the row was deleted). Within a REPEATABLE READ or SERIALIZABLE transaction, however, an error will be thrown if a row to be locked has changed since the transaction started. The FOR UPDATE lock mode is also acquired by any DELETE on a row, and also by an UPDATE that modifies the values on certain columns. Currently, the set of columns considered for the UPDATE case are those that have a unique index on them that can be used in a foreign key (so partial indexes and expressional indexes are not considered), but this may change in the future.

• FOR NO KEY UPDATE: Behaves similarly to FOR UPDATE, except that the lock acquired is weaker: this lock will not block SELECT FOR KEY SHARE commands that attempt to acquire a lock on the same rows. This lock mode is also acquired by any UPDATE that does not acquire a FOR UPDATE lock.

• FOR SHARE: Behaves similarly to FOR NO KEY UPDATE, except that it acquires a shared lock rather than exclusive lock on each retrieved row. A shared lock blocks other transactions from performing UPDATE, DELETE, SELECT FOR UPDATE or SELECT FOR NO KEY UPDATE on these rows, but it does not prevent them from performing SELECT FOR SHARE or SELECT FOR KEY SHARE.

• FOR KEY SHARE: Behaves similarly to FOR SHARE, except that the lock is weaker: SELECT FOR UPDATE is blocked, but not SELECT FOR NO KEY UPDATE. A key-shared lock blocks other transactions from performing DELETE or any UPDATE that changes the key values, but not other UPDATE, and neither does it prevent SELECT FOR NO KEY UPDATE, SELECT FOR SHARE, or SELECT FOR KEY SHARE.

PostgreSQL doesn't remember any information about modified rows in memory, so there is no limit on the number of rows locked at one time. However, locking a row might cause a disk write, e.g., SELECT FOR UPDATE modifies selected rows to mark them locked, and so will result in disk writes.

You can check again the table below for compatability between the different types of Row Locks: