sql-server - 了解 SQL Server 中的六锁

Question

有人能解释一下进程如何获取SIX锁定页面？我在我的死锁图 xml 文件中看到在 下运行的进程RC 隔离级别 (在死锁时刻执行 select 语句)持有 SIX锁定页面。

这是什么意思以及如何获得该锁？
从我得到的 http://msdn.microsoft.com/en-us/library/aa213039%28v=sql.80%29.aspx SIX锁具保护S - 锁定所有资源和 IX锁定层次结构中较低的一些资源。

对于我的情况，这将是 IX -行上的锁？可以 IX - 锁要放在一排吗？ (我猜没有)。我很困惑。

另一件事是我期待几个X - 行上的锁并且没有 S - 完全锁定(因为 IL 是 ReadCommited )。为什么我用 SIX 锁定了整个页面如果我只在之前的语句中插入了几条记录？

起来!

Answer 1

来自 dba 的回答 Sebastian Meine :

To answer that I have to take a little detour, so bear with me. If two sessions take a lock on the same resource SQL Server checks the lock compatibility map and if the second request is not "compatible" with the first, the second session has to wait. There are three lock types "S"hared, "U"pdate and e"X"clusive. S locks are taken to read from a resource and X locks are taken to write to a resource. S locks are compatible with each other, X locks are not compatible with anything else. U locks are a hybrid that is used in some cases for deadlock prevention.

Now, SQL Server can take locks on several levels:Table, Partition, Page and Row. So if session one takes a table lock and session two takes a non-compatible lock on one row of the table, those two locks are not on the same resource and SQL Server won't detect the collision. To protect against that, SQL Server always starts to take a lock on the table level and works its way down the hierarchy. Now the point of page and row locks is higher concurrency, so if one session wants to write to one row and another session wants to write to another row, they should not block each other. If a session in addition to taking a lock on a row also has to take the same lock on the table, that advantage is gone. So instead of taking an exclusive lock (X) on the table, the session requests an intend-exclusive lock (IX). This lock is compatible with other intend locks but not with other "real" locks. So another session can take an intend-exclusive lock on the same table as well. The intend-exclusive lock says, that the session intends to take an exclusive lock on a lower level resource. The same happens on the page level, if the intended lock is a row lock, so after all is done, the session has an IX lock on the table and on one of the pages and an X lock on one of the rows in that page. This also means, that you will never find an intend lock on a row as rows are the lowest level in the lock hierarchy.

In some circumstances a session holds an S lock on the table or a page. If the session now (within the same transaction) requests an X lock on a row in that same table, it first has to take an IX lock on the table/page. However, a session can hold only one lock on any given resource. So to take the IX lock, it would have to release the S lock wich is probably not desired, so SQL Server offers a combination: SIX.

The reason why you have a page lock is due to SQL Server sometimes deciding that it would be better to lock the page instead of locking each row. That happens often if there are very many locks taken between al sessions already, but can have many other reasons too.

So far the theory.

Now in your case the SIX lock is held by a three table join select query. A select never takes any type of lock that is not a shared lock unless you explicitly tell it to (e.g. with a XLOCK hint). Such a hint is not visible within the input buffer, so I assume the IX part is a left over from the last batch on this connection. If you are using connection pooling and forget to cleanup all open transactions, such a lock can live potentially forever. But it becomes also very hard to troubleshoot.

You could start by running an XEvent session that pairs OPEN TRANs with COMMITs and see if you can find the culprit that way.

来源: https://dba.stackexchange.com/questions/45284/understanding-six-lock-in-microsoft-sql-server