Rozwiązuję problem zakleszczenia, gdy zauważyłem, że zachowanie blokady jest inne, gdy korzystam z indeksu klastrowego i nieklastrowanego w polu id. Wydaje się, że problem zakleszczenia został rozwiązany, jeśli do pola id zostanie zastosowany indeks klastrowany lub klucz podstawowy.
Mam różne transakcje, które wykonują jedną lub więcej aktualizacji dla różnych wierszy, np. Transakcja A zaktualizuje tylko wiersz o ID = a, tx B dotknie tylko wiersza o ID = b itp.
Rozumiem, że bez indeksu aktualizacja uzyska blokadę aktualizacji dla wszystkich wierszy i w razie potrzeby ukrytą blokadę wyłączności, co ostatecznie doprowadzi do impasu. Ale nie wiem, dlaczego przy indeksie nieklastrowanym impas wciąż istnieje (chociaż wskaźnik trafień wydaje się spadać)
Tabela danych:
CREATE TABLE [dbo].[user](
[id] [int] IDENTITY(1,1) NOT NULL,
[userName] [nvarchar](255) NULL,
[name] [nvarchar](255) NULL,
[phone] [nvarchar](255) NULL,
[password] [nvarchar](255) NULL,
[ip] [nvarchar](30) NULL,
[email] [nvarchar](255) NULL,
[pubDate] [datetime] NULL,
[todoOrder] [text] NULL
)Ślad impasu
deadlock-list
deadlock victim=process4152ca8
process-list
process id=process4152ca8 taskpriority=0 logused=0 waitresource=RID: 5:1:388:29 waittime=3308 ownerId=252354 transactionname=user_transaction lasttranstarted=2014-04-11T00:15:30.947 XDES=0xb0bf180 lockMode=U schedulerid=3 kpid=11392 status=suspended spid=57 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2014-04-11T00:15:30.953 lastbatchcompleted=2014-04-11T00:15:30.950 lastattention=1900-01-01T00:00:00.950 clientapp=.Net SqlClient Data Provider hostname=BOOD-PC hostpid=9272 loginname=getodo_sql isolationlevel=read committed (2) xactid=252354 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
executionStack
frame procname=adhoc line=1 stmtstart=62 sqlhandle=0x0200000062f45209ccf17a0e76c2389eb409d7d970b0f89e00000000000000000000000000000000
update [user] WITH (ROWLOCK) set [todoOrder]=@para0 where id=@owner
frame procname=unknown line=1 sqlhandle=0x00000000000000000000000000000000000000000000000000000000000000000000000000000000
unknown
inputbuf
(@para0 nvarchar(2)<c/>@owner int)update [user] WITH (ROWLOCK) set [todoOrder]=@para0 where id=@owner
process id=process4153468 taskpriority=0 logused=4652 waitresource=KEY: 5:72057594042187776 (3fc56173665b) waittime=3303 ownerId=252344 transactionname=user_transaction lasttranstarted=2014-04-11T00:15:30.920 XDES=0x4184b78 lockMode=U schedulerid=3 kpid=7272 status=suspended spid=58 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2014-04-11T00:15:30.960 lastbatchcompleted=2014-04-11T00:15:30.960 lastattention=1900-01-01T00:00:00.960 clientapp=.Net SqlClient Data Provider hostname=BOOD-PC hostpid=9272 loginname=getodo_sql isolationlevel=read committed (2) xactid=252344 currentdb=5 lockTimeout=4294967295 clientoption1=671088672 clientoption2=128056
executionStack
frame procname=adhoc line=1 stmtstart=60 sqlhandle=0x02000000d4616f250747930a4cd34716b610a8113cb92fbc00000000000000000000000000000000
update [user] WITH (ROWLOCK) set [todoOrder]=@para0 where id=@uid
frame procname=unknown line=1 sqlhandle=0x00000000000000000000000000000000000000000000000000000000000000000000000000000000
unknown
inputbuf
(@para0 nvarchar(61)<c/>@uid int)update [user] WITH (ROWLOCK) set [todoOrder]=@para0 where id=@uid
resource-list
ridlock fileid=1 pageid=388 dbid=5 objectname=SQL2012_707688_webows.dbo.user id=lock3f7af780 mode=X associatedObjectId=72057594042122240
owner-list
owner id=process4153468 mode=X
waiter-list
waiter id=process4152ca8 mode=U requestType=wait
keylock hobtid=72057594042187776 dbid=5 objectname=SQL2012_707688_webows.dbo.user indexname=10 id=lock3f7ad700 mode=U associatedObjectId=72057594042187776
owner-list
owner id=process4152ca8 mode=U
waiter-list
waiter id=process4153468 mode=U requestType=waitCiekawym i możliwym powiązanym ustaleniem jest to, że indeks klastrowany i nieklastrowany wydaje się mieć różne zachowania blokujące
Podczas korzystania z indeksu klastrowego istnieje wyłączna blokada klucza, a także wyłączna blokada RID podczas aktualizacji, co jest oczekiwane; podczas gdy istnieją dwa wyłączne blokady na dwóch różnych RID, jeśli używany jest indeks nieklastrowany, co mnie myli.
Byłoby pomocne, gdyby ktokolwiek mógł wyjaśnić, dlaczego.
Testuj SQL:
use SQL2012_707688_webows;
begin transaction;
update [user] with (rowlock) set todoOrder='{1}' where id = 63501
exec sp_lock;
commit;Z id jako Indeks klastrowany:
spid dbid ObjId IndId Type Resource Mode Status
53 5 917578307 1 KEY (b1a92fe5eed4) X GRANT
53 5 917578307 1 PAG 1:879 IX GRANT
53 5 917578307 1 PAG 1:1928 IX GRANT
53 5 917578307 1 RID 1:879:7 X GRANTZ id jako Indeks nieklastrowany
spid dbid ObjId IndId Type Resource Mode Status
53 5 917578307 0 PAG 1:879 IX GRANT
53 5 917578307 0 PAG 1:1928 IX GRANT
53 5 917578307 0 RID 1:879:7 X GRANT
53 5 917578307 0 RID 1:1928:18 X GRANTEDYCJA 1: Szczegóły impasu bez żadnego indeksu
Powiedzmy, że mam dwa tx A i B, każde z dwiema instrukcjami aktualizacji, inny rząd oczywiście
tx A
update [user] with (rowlock) set todoOrder='{1}' where id = 63501
update [user] with (rowlock) set todoOrder='{2}' where id = 63501tx B.
update [user] with (rowlock) set todoOrder='{3}' where id = 63502
update [user] with (rowlock) set todoOrder='{4}' where id = 63502Od tego czasu {1} i {4} mieliby szansę na impas
w {1} wymagana jest blokada U dla wiersza 63502, ponieważ musi ona wykonać skanowanie tabeli, a blokada X mogła zostać zatrzymana w wierszu 63501, ponieważ odpowiada warunkowi
w {4} wymagana jest blokada U dla wiersza 63501, a blokada X jest już wstrzymana dla 63502
więc mamy txA trzyma 63501 i czeka 63502, podczas gdy txB trzyma 63502 i czeka na 63501, co jest impasem
EDIT2: Okazało się, że błąd w moim przypadku testowym robi tutaj różnicę Przepraszam za zamieszanie, ale błąd robi różnicę i wydaje się, że ostatecznie powoduje impas.
Ponieważ analiza Paula naprawdę pomogła mi w tym przypadku, więc przyjmuję to jako odpowiedź.
Z powodu błędu mojego przypadku testowego dwie transakcje txA i txB mogą zaktualizować ten sam wiersz, jak poniżej:
TX A
update [user] with (rowlock) set todoOrder='{1}' where id = 63501
update [user] with (rowlock) set todoOrder='{2}' where id = 63501tx B.
update [user] with (rowlock) set todoOrder='{3}' where id = 63501{2} i {3} mieliby szansę na impas, gdy:
txA żąda blokady U na kluczu, podczas gdy trzyma X lock na RID (z powodu aktualizacji {1}) txB żąda blokady U na RID, gdy trzyma U lock na kluczu
