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?



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.

  • Hmm, sounds like they use double-checked locking which is considered an anti-pattern: en.wikipedia.org/wiki/Double-checked_locking Jan 7 at 11:42
  • @Daniel Vérité, if I use FOR UPDATE in a SELECT statement, which locktype in pg_locks can tell whether FOR UPDATE has been used? Please advise. Thanks
    – hunterex
    Jan 7 at 11:48
  • @hunterex: please ask this in a new specific question. Jan 7 at 12:10
  • You won't see the row locks taken by FOR UPDATE in the lock table, because row locks are not kept in the shared lock table - they are stored on the row itself (no lock escalation!). If someone ran SELECT ... FOR UPDATE on a table, you will see a RowShare lock on the relation in pg_locks. The locking systems of these two databases work quite differently. Jan 7 at 12:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.