Отличие состоит в том, что SERIALIZABLE удерживает блокировку наложенную транзакцией, а UPDLOCK накладывает и удерживает блокировку обновления. Т. е.
Код: Выделить всё
BEGIN TRANSACTION
UDDATE Table WITH(UPDLOCK) ...
Код: Выделить всё
BEGIN TRANSACTION
UDDATE Table WITH(SERIALIZABLE) ...
Но:
Код: Выделить всё
BEGIN TRANSACTION
SELECT * FROM Table WITH(UPDLOCK) WHERE ...
Код: Выделить всё
BEGIN TRANSACTION
SELECT * FROM Table WITH(SERIALIZABLE) WHERE ...
2. UPDLOCK при UPDATE
UPDLOCK полезен не только при SELECT, когда надо заблочить запись на время транзакции.
Рассмотрим следующую ситуацию. Есть таблица Table с некластеризованным ключом Field.
1. Session 1:
Код: Выделить всё
BEGIN TRANSACTION
UPDATE Table SET ... WHERE Field = 1
Код: Выделить всё
BEGIN TRANSACTION
UPDATE Table SET ... WHERE Field = 1
3. Session 1:
Код: Выделить всё
UPDATE Table SET ... WHERE Field = 1
Если добавить во все скрипты WITH(UPDLOCK), то дедлока не будет. После завершения транзакции в сессии 1 будет выполнен скрипт в сессии 2. Дедлока также не произойдет, если Field будет кластеризованным ключом.
Это происходит потому что сессия 1 после шага 1 удерживает только блокировку X. Сессия 2 после шага 2 удерживает блокировку U и не может получить блокировку X. Сессия 1 на шаге 3 хочет получить блокировку U, но не может из-за сессии 2, что и приводит к дедлоку.
В случае с UPDLOCK сессия 1 после первого шага удерживает блокировки X и U. Поэтому сессия 2 накладывает на шаге 2 только блокировку намерения IX и не мешает сессии 1 на шаге 3 сделать еще один апдейт.
Примечание: при замене UPDLOCK на SERIALIZABLE будет аналогичное поведение, т. к. SERIALIZABLE удерживает наложенные блокировки (в данном случае U, т. к. инструкция UPDATE).
http://www.sqlnotes.info/2012/10/10/upd ... h-updlock/
no UPDLOCK
Session 1: Transaction Starts
Session 1: Lock Acquired: IX lock on the table
Session 1: Lock Acquired: U lock on the key of the non clustered primary key (and IU lock on the page the index key located)
Session 1: Lock Acquired: X lock on RID (and IX lock on the page the Row located)
Session 1: Lock Released: U lock on the key of the non clustered primary key (and IU lock on the page the index key located). This is because the index key is not changed. There is no lock conversion on the index key
Session 1 status: update query is done. Tansaciton is open. X lock will be held until transaction complete
Session 2: Transaction Starts
Session 2: Lock Acquired: IX lock on the table
Session 2: Lock Acquired: U lock on the key of the non clustered primary key (and IU lock on the page the index key located)
Session 2: Lock Acquiring: X lock on RID — this is blocked
Session 2 status: this session is blocked by the X lock held on RID by Session 1. Currently this session owns U lock on the index key
Session 1: Lock Acquired: IX lock on the table — trying to update Description field to Value2
Session 1: Lock Acquiring: U lock on the key of the none clustered primary key — this is blocked
Session 1 status: Trying to get U lock on the index key and get X lock on the Row(although it already owns the X lock on the row)
Session 1 owns X lock on the RID and trying to get U lock on the index key where Session 2 owns U lock on the index key and trying to get X lock to the RID. This is the locking behavior behind.
WITH(UPDLOCK)
Session 1: Transaction Starts
Session 1: Lock Acquired: IX lock on the table
Session 1: Lock Acquired: U lock on the key of the non clustered primary key (and IU lock on the page the index key located)
Session 1: Lock Acquired: X lock on RID (and IX lock on the page the Row located)
Session 1 status: update query is done. Tansaciton is open. X lock on RID will be held until transaction complete. U lock on the index key will be held until transaction complete
Session 2: Transaction Starts
Session 2: Lock Acquired: IX lock on the table
Session 2: Lock Acquiring: U lock on the key of the non clustered primary key — blocked becuase U lock on the key has hold by session 1
Session 2 status: this session is blocked. It only owns IX lock on the table and IU lock on the index page
Session 1: Lock Acquired: IX lock on the table — trying to update Description field to Value2
Session 1: Lock Acquired: U lock on the key of the non clustered primary key — this is no blocked
Session 1: continue to run until end of the transaction