PostgreSQL – Equivalent of Microsoft SQL Server’s IX Lock

lockingpostgresql

In Microsoft SQL Server, the code below is used to get the IX (Intent Exclusive) lock:

SELECT request_mode FROM sys.dm_tran_locks WHERE resource_database_id = DB_ID() AND resource_associated_entity_id = OBJECT_ID(N'dbo.cars');

In PostgreSQL, pg_locks table is available to tell locks information. However, there are many types of locktype, which are:

relation, extend, frozenid, page, tuple, transactionid, virtualxid, object, userlock, or advisory

Which types of locktype from pg_locks is equivalent to IX (Intent Exclusive) lock from Microsoft SQL Server?

If locktype is the wrong column to look at pg_locks, please advise which column in pg_locks should I use?

Thanks

Best Answer

You need to take a step back and check what Intent Locks are made for.

Transaction locking for MS-SQL server is broadly explained here: https://docs.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide

And for PostgreSQL it's broadly explained here https://www.postgresql.org/docs/current/mvcc.html

MS-SQL server doc says:

Intent locks are named intent locks because they are acquired before a lock at the lower level, and therefore signal intent to place locks at a lower level.

Intent locks serve two purposes:

To prevent other transactions from modifying the higher-level resource in a way that would invalidate the lock at the lower level.
To improve the efficiency of the SQL Server Database Engine in detecting lock conflicts at the higher level of granularity.

There is no equivalent to intent locks in PostgreSQL. The IS, IX, SIX, IU, SIU, UIX lock modes have no equivalent because they refer to a concept that PostgreSQL doesn't have and seemingly doesn't need.

Related Question