MSSQL LOCK: UIX , SIX, SIU example

Question

who can have the lock example for each of UIX , SIX, SIU?? Thanks

Answer 1

Data Platform MVP Dmitri Korotkevitch has a good coverage of locking, including example of these combo-locks, in his book Expert SQL Server Transactions and Locking: Concurrency Internals for SQL Server Practitioners. https://www.amazon.com/Expert-Server-Transactions-Locking-Practitioners/dp/1484239563/ref=sr_1_2?dchild=1&keywords=dmitri+korotkevitch&qid=1604759040&sr=8-2

Full disclosure: Dmitiri is a friend of mine. But it is a worthwhile book.

Answer 2

Hi @sakuraime ,

Please reference this doc.: sql-server-transaction-locking-and-row-versioning-guide and then test on your own side firstly.

More information: locking-sql-server

BR,
Mia

---

If the answer is helpful, please click "Accept Answer" and upvote it.

Answer 3

You get a SIX lock on a resource, when you already have an S lock on it, and you then need an intent lock on. The similar applies for the other two. Here is a quite contrived example where you get a SIX lock. With this as pattern, you may be able to create similar examples for the other two:

CREATE TABLE Columns (id int NOT NULL,
                      id2 int NOT NULL,
                      name sysname NOT NULL,
                      PRIMARY KEY (id, id2))

INSERT Columns(id, id2, name)
  SELECT object_id, column_id, name FROM sys.columns

go
BEGIN TRANSACTION

SELECT COUNT(*) FROM Columns WITH (REPEATABLEREAD, TABLOCK)

UPDATE Columns
SET    name = 'Fritte'
WHERE  id = 8
  AND  id2 = 1

SELECT * FROM sys.dm_tran_locks WHERE request_session_id = @@spid
go
ROLLBACK TRANSACTION
go
DROP TABLE Columns