SQL Server意向锁工作原理、各类锁作用及识别问题咨询

Hey there! Let's break down intent locks clearly—those acronyms can get confusing fast, and understanding them is key to debugging those lock-related performance headaches you're seeing.

At their core, intent locks are table-level locks that act as a "heads up" to the database. Here's the problem they solve: without intent locks, if a transaction wanted to acquire a full table lock (like an exclusive table lock), the database would have to scan every single row in the table to check if any row-level locks are already held. That's brutal for performance, especially on large tables.

Intent locks let the database quickly answer: "Can I safely grant this table-level lock right now?" without scanning all rows. They coordinate between row-level locks and table-level locks, reducing overhead and preventing unnecessary full-table checks. That's why you'll spot them in lock stats—they're the middle layer keeping lock management efficient.

Let's go through each one with real-world scenarios:

• Intent Shared Lock (IS)
  You'll see this when a transaction plans to acquire row-level shared locks (S). Before locking individual rows for reading (e.g., with SELECT ... FOR SHARE), the transaction first grabs an IS lock on the table. It signals: "I'm going to read some rows—other transactions can also read those rows or add their own IS locks, but don't try to lock the whole table exclusively."

• Intent Exclusive Lock (IX)
  This is the counterpart for write operations. When a transaction plans to acquire row-level exclusive locks (X) (like for UPDATE, DELETE, or SELECT ... FOR UPDATE), it first adds an IX lock to the table. It says: "I'm going to modify some rows—you can't lock the entire table exclusively, but other transactions can add IS/IX locks to work on separate rows."

• Shared Intent Exclusive Lock (SIX)
  Think of this as a combo: a shared table lock (S) plus an IX lock. It means: "I need to read the entire table, and I also plan to modify some rows within it." For example, if you run LOCK TABLE my_table IN SHARE MODE and then update specific rows, you'll get a SIX lock. It allows other transactions to add IS locks for row-level reads, but blocks any attempts to add table-level exclusive locks or shared locks.

• Intent Update Lock (IU)
  This is specific to databases that use update locks (U) (like SQL Server). Update locks are used to prevent deadlocks when a transaction wants to read a row with the intent to update it later. The IU lock is the table-level heads up: "I'm going to add update locks to some rows."

• Shared Intent Update Lock (SIU)
  Another combo: a shared table lock (S) plus an IU lock. It signals: "I'm reading the entire table, and I plan to add update locks to some rows." You might see this if you lock a table for shared access first, then start prepping rows for updates.

• Update Intent Exclusive Lock (UIX)
  This is the transition lock when a transaction wants to upgrade row-level update locks (U) to exclusive locks (X). The UIX lock says: "I already hold update locks on some rows, and I'm going to turn them into exclusive locks to modify those rows." It blocks other transactions from acquiring table-level locks that would conflict with this upgrade.

If you're seeing a lot of these locks in your stats leading to bottlenecks, here are quick red flags:

• A flood of SIX/SIU/UIX locks often means long-running transactions holding table-level locks while working on rows—check for unnecessary LOCK TABLE statements or unoptimized long queries.
• Frequent lock waits involving IX/IS might mean transactions are stepping on each other's rows more than they should—look for missing indexes that are forcing full-table scans, or transactions updating overlapping row sets.

内容的提问来源于stack exchange，提问作者Alfin E. R.