INF: ????? ? ???? Deadlocks ?? SQL Server

?????? ????????? ?????? ?????????
???? ???????: 169960 - ??? ???????? ???? ????? ????? ??? ???????.
????? ???? | ?? ????

?? ??? ??????

??????

????? Microsoft SQL Server ????? ????? ???????? ?????? ????????? ?????? ??????? ???????. ??????? 6.5 ?? SQL Server ???? ??????? ?????? ??????? ??? ????? ???? ??????? ??????? ??????? ??????? ??? ????? ?????? ?????? ??? ?????? ????. ??? ?? ????? ?? ?? ???? ????? ?????? ??????? ????? ?? ???? ??? deadlocks ??? ??????????.

??? ???? ??????? ???? ????????1 (?? Connection1) ???? ????? ??? ???????? ?????? "A" ????? ????? ??? ???? ?????? "?" ???? ????? ??? ???? ?????? "B" User2 ????? ????? ??? ???? ?????? "?" ???? ?? ??? ????????? SQL Server ????????1 ?? User2 ???? ?????? ???? ???? ??? ?? ???? ??? ??????? ??????? ???????? ?????.

?? SQL Server? ???? ????? ???? ??????? ??????? ???? ???? ??????? ?????? ???? ???? ??? ???????? DEADLOCK_PRIORITY SET. ??? ?? ?????? ????? ?????? deadlocks, ???? SQL Server ?????? ???? ???? ??? ?? ???? ?????? ????? ????? ????? ?????? ?????????.

????? ????? ???????? ?? ???? ????? ???? ????? ??? ??????? ?? ????? ?????? ??????? ???? ??? ???? ????? ????? ???? ????? ???????? ?????????. ????? ????? ?? ????? ????? ?? ????????? ??????. ???? ??? ?????? ????? ????? deadlocks ?? SQL Server ??????? ????? ??????? ????? ?????.

??????? ????

???? ??? ?????? ???????? ??????? ????? ???? T1204 ?????? deadlocks. ??? ????? ????? ???? T1204 SQL Server ????? ??????? ??? ???? ???? ??? ???? ?????. ???????? ??? ??????? ?????? ?????? ????? ?????? ?? ???? ????? ???? ????? SQL Server:
   sqlservr -c -T1204
				

??? ????? ????? ?????? ??? ???? ???? ?????? ??? ??? ?? ????? ????? ???? T3605 ? ???? ???? ????? ?????? ??? ??? ???????.

?? ???? deadlocks ??????? ????? ??????? ?? ????? ????. ??? ???? ??????? ????? ???? ????? ?? ???? "example1" ????? ?? ??? "example2" ????? ????? ????? ??? ?? ???? "example2" ????? ?? ??? "example1" ???? ??????. ??????? ???? ?????? ?????? ????? ???? deadlocks.

???? ??? ?????? SQL ????????? ?? ????? ???? ????????? ?? ??? ??????:
   create table example1 (column1 int, column2 char(20), column3 char(50))
   go
   create table example2 (column1 int, column2 char(20), column3 char(50))
   go
   declare @lvar int
   select @lvar = 0
   while @lvar < 500
   begin
   insert into example1 values (@lvar, 'AAA', 'CCC')
   insert into example2 values (@lvar, 'AAA', 'CCC')
   select @lvar = @lvar + 1
   end
   go
   create unique clustered index ex1ind1 on example1 (column1, column2)
   with fill factor = 90, PAD_INDEX
   go
   create unique clustered index ex2ind1 on example2 (column1, column2)
   with fill factor = 90, PAD_INDEX
   go
				

???? 1: ?????? ????? ???? ?? ????? ????

?? ??? ??????? ?? ??????? ?????? ???????? ??????? ?? ??? ???? ???? ???. ???? ????? ?? ???? deadlocks ??? ????? ?? ???? ?? ????????? ????? ??????? ?? ??? ??? ????? ???????? ???????.
   Connection1 > BEGIN TRANSACTION
   Connection2 > BEGIN TRANSACTION
   Connection1 > INSERT INTO example1 VALUES (100, 'AAAA', 'CCC')
   Connection2 > INSERT INTO example2 VALUES (200, 'AAAB', 'CCC')
   Connection2 > INSERT INTO example1 VALUES (200, 'AAAB', 'CCC')
				

?? ??? ???????? ?? ???? Connection1 Connection2 ???? ?? ???? ?? ????? Connection2 ??? ??? ?????? ??? Connection1 ??? ?????? ????? ?? ? ????? ?????.
   Connection1 > INSERT INTO example2 VALUES (100, 'AAAA', 'CCC')
				

?? ??? ???????? ?? ???? Connection2 Connection1 ? ???? ?? ???? ?? ????? Connection1 ??? ??? ?????? ??? ?? ?????? ????? ?? Connection2 ? ????? ?????. ?????? ??? ?? ???? ???? ???.

?????? ?? ??????? ????? ???? 1204 ??? ?? ???? ???? ???:
97/04/20 11:51:57.88 spid13   *** DEADLOCK DETECTED with spid 14 ***
   spid 13 requesting EX_PAGE (waittype 0x8005), blocked by:
     EX_PAGE: spid 14, dbid 6, page 0x188, table example2, indid 0x1
     pcurcmd INSERT(0xc3), input buffer: INSERT INTO example2 VALUES (100,
     'AAAA', 'CCC')
   spid 14 waiting for EX_PAGE (waittype 0x8005), blocked by:
     EX_PAGE: spid 13, dbid 6, page 0x180, table example1, indid 0x1
     pcurcmd INSERT(0xc3), input buffer: INSERT INTO example1 VALUES (200,
   'AAAB', 'CCC')
   VICTIM: spid 13, pstat 0x0000 , cputime 30
				

???? ??? ??? ?? ???? ???? ???? ??? ???? ?????????? ?????? ??? ???? ???? ???. Connection1 spid 13 ??? Connection2 spid 14 (????? ????? spid ???????? ????? ???????? ????? ?????? ?????? sp_who).
   >> 97/04/20 11:51:57.88 spid13   *** DEADLOCK DETECTED with spid 14 ***
   The deadlock was detected between spid 13 and spid 14.
   >> spid 13 requesting EX_PAGE (waittype 0x8005), blocked by:
   >>   EX_PAGE: spid 14, dbid 6, page 0x188, table example2, indid 0x1
   >>   pcurcmd INSERT(0xc3), input buffer: INSERT INTO example2 VALUES
   (100, 'AAAA', 'CCC')
				

?? ??? ????? EX_PAGE 13 Spid ?? ?? ??? ??? spid 14, ???? ?????? ????? EX_PAGE ???? 0x188 ??? example2 ?????? ?? dbid 6. ??? ????? ??????? ??? ?????? ???? ????? ??? ???? ?????? ????????.
      Indid Value         Description
-------------------------------------
         0                Data page if there is no clustered index, or the
                          leaf page of a clustered index if there is one
         1                Non-leaf page of the clustered index page
       255                Text/image page
    Any other value       Non-clustered secondary index
				

??? ??????? ?????? spid 13 ?? ????? ?????? INSERT ?????? ?????? ??? ?? ???? ??????? ??????.
   >> spid 14 waiting for EX_PAGE (waittype 0x8005), blocked by:
   >>   EX_PAGE: spid 13, dbid 6, page 0x180, table example1, indid 0x1
   >>   pcurcmd INSERT(0xc3), input buffer: INSERT INTO example1 VALUES
   (200, 'AAAB', 'CCC')
				

14 Spid ??????? ????? EX_PAGE ? ??? ?????? spid 13, ???? ????? ????? EX_PAGE ?????? ??? ??? ??????.
   >> VICTIM: spid 13, pstat 0x0000 , cputime 30
   SQL Server has chosen spid 13 as the deadlock victim.
				

?????? ??? ???? ???? ????? ???????? ?? ??????:

SH_INT ? EX_INT
????? ??? ??? ??? ??? ???? ??????? ???? (??? ???? ??????? ????) ??? ????? ??????? ?????? (??? ???? ??????? ????) ????? ???? ? ??? ????? ????? ????? ??? ?? ??????? ??? ????? ?????? ?? ??????? (?? ??? ??????, ??????? ????????). ??? ?? ????? EX_INT ?? ?????? ?? ??? ?????? ??? ????? ????? EX_PAG ??? ??????? ?????? ??? ?????? ??? ????? EX_TAB ?? ??? ?????? ?? ?? ?? ???? ???? ????? ???? ?????. ??????? ?? SQL Server ????? ??? ??? ??? ???????. ???? ????? ?? ????? ???: ????? ???? (EX_INT) "?" ????? (SH_INT).

ex_page
??? ?? ????? ???? ??? ???? ??? ????? ???? ????? DELETE UPDATE, ?? ????? ????? INSERT ?? ????? ????? ?????? (IRL) ?????.

up_page
??? ?? ????? ???? ????? ???? ?? ???????? ????? ?? ????? ?????? ???????? ??? ??? ???? ?????? ???? ???? ????? ?????? (?? ??????? ????? UPDLOCK).

PR_EXT ? NX_EXT UPD_EXT ? EX_EXT
??? ??? ??? ????????? ??? ????? ?? ????? ????? ????? ?????. ???? UPD_EXT ??? ????? ?? ????? ????? ???? ?? ??? ?????? ??????? ?????? ??? ????? ?? ????? ????? ?????? ??????.

IX_PAGE ? LN_PAGE
??? ?? IRL ???????. IX_PAGE ????? ??? - ??? - - ???? - ??????? ??? ????. ??? ??? LN_PAGE ??? ?????? ???? IRL ??? ??? ?????? ???????? ???? ?????.

RLOCK ? XRLOCK
??? ??? ??? ????? ????? ????? ??? ?????? b ???? - ????. ???? ????? ?? ??? ????? ?? ?????: ??????? (RLOCK) ? ???? (XRLOCK). ??? ??? ????? ???????? ????? ?????, ????? ??? ???????? ?????? ??????? ?????? ??? ????? ???? ????? ?????.

ex_tab
??? ?? ????? ???? ??? ???? ????? ???? ?????? ???? SQL ?? ???? ???? ?? ??????? ?????? ????? ??? ?? ??????? ????? (??? ???? ??????? ??? ???? ?? ????? ?? ????). ???? ?????? ??????? EX_TAB ????? ??? ????? ?????? ?? ????? TABLOCKX ?? SQL Server escalates ????? ?????? ??? ???? ??? ????? ????.

sh_tab
??? ?? ???????? ???? ???? ????? ?????? ???????? ??? ?????? ????? ?? ???? ?????? (?? ????? ?????? escalates) ?? ??? ??????? ????? TABLOCK.

???? ?? ??? ???? ?????? ???? ???? ??? ?????? ??? ?????? ????????? ??? ??????? ?? ??????? ??????:
   Connection1 > BEGIN TRANSACTION
   Connection2 > BEGIN TRANSACTION
   Connection1 > INSERT INTO example1 VALUES (100, 'AAAA', 'CCC')
   Connection2 > INSERT INTO example1 VALUES (200, 'AAAB', 'CCC')
   Connection2 > INSERT INTO example2 VALUES (200, 'AAAB', 'CCC')
   Connection1 > INSERT INTO example2 VALUES (100, 'AAAA', 'CCC')
				

???? 2: ?????? ??????? ??? ????? ?????? ?? ??? ??????

???? ?? ???? ??? ???? ???? ??? ????? ??? ????? ??????? ?? ????? ?????? ?? ??? ?????? ???????? ??????? ??? ?????? ???? ???????. ??? ???? ??????:
   Connection1 > BEGIN TRANSACTION
   Connection2 > BEGIN TRANSACTION
   Connection1 > INSERT INTO example1 VALUES (100, 'AAAA', 'CCC')
   Connection2 > INSERT INTO example1 VALUES (400, 'AAAB', 'CCC')
   Connection1 > INSERT INTO example1 VALUES (400, 'AAAA', 'CCC')
				

?? ??? ?????? ??????? ???? ???? ?????? ???????? ??? ?????? ????? ?? ?????? example1. ??? ???? ?????? ???? ??? ?????? ????? ??? ??? ?? ??? ??????. ?? ??????? ???? ?????? ?????? ?????? Connection1 ??? ??????? ?? ??? ??? ??? ?????? ??? ???? ????? ?????? ?????? Connection2 ? ??? ??? ????? ???? ???? ?????? ???????? ?? 400. ???? ??? Connection2 ??? ???? Connection1.
   Connection2 > INSERT INTO example1 VALUES (100, 'AAAB', 'CCC')
				

???? Connection2 ??? ????? ???? ?? ?? ???? ?????? Connection1 ??? ??? ???? ???? ???. ?????? ?? ???? ???? ???? ???:
   97/04/20 12:56:01.40 spid16   *** DEADLOCK DETECTED with spid 15 ***
   spid 16 requesting EX_PAGE (waittype 0x8005), blocked by:
     EX_PAGE: spid 15, dbid 6, page 0x2c5, table example1, indid 0
     pcurcmd INSERT(0xc3), input buffer: INSERT INTO example1 VALUES (100,
   'AAAB', 'CCC')
   spid 15 waiting for EX_PAGE (waittype 0x8005), blocked by:
     EX_PAGE: spid 16, dbid 6, page 0x8bd, table example1, indid 0
     pcurcmd INSERT(0xc3), input buffer: INSERT INTO example1 VALUES (400,
   'AAAA', 'CCC')
   VICTIM: spid 16, pstat 0x0000 , cputime 130
				

?? ??? ??? spid 16 ????? EX_PAGE ????? 0x2c5 ??? spid 15, ???? ????? ????? EX_PAGE ????? 0x2c5 ?????? ??? ??? ????? ?????. ?? ?????? ??? ??? spid 15 ????? ?????? spid 16 ?? ?????? ????? EX_PAGE ????? ?????? 0x8db ??? ???? ???? ???.

???? ?? ??? ???? ??? ???? ???? ??? ???????? ????? ?????? ?????? IRL example1 ??????:
   sp_tableoption 'example1', 'insert row lock', true
				

???? 3: ?????? ??????? ??????? IRL

???? IRL ?????????? ????? ?? ???? ?? ?????? ???? ??? ??? ????? ?????? ???? ???? ????? ????????? ???? ????. ??? ???? ????? IRL ?? ????? ????? deadlocks. ?? ??? ???????? ?? ???? IRL deadlocks.
   Connection1 > BEGIN TRANSACTION
   Connection2 > BEGIN TRANSACTION
   Connection1 > INSERT INTO example1 VALUES (100, 'AAAA', 'CCC')
   Connection2 > INSERT INTO example1 VALUES (105, 'AAAB', 'CCC')
				

?? ????? IRL ? ??? ??????? ?????? ??? ????? IX_PAGE ??? ?????? ???? ????? ??? ???? ????. ??? ?? ????? IRL Connection1 ?? ?????? ??? ????? EX_PAGE ?? Connection2 ?? ?? ??? ?????.
   Connection2 > UPDATE example1 SET column3 = 'CCCB' where column1 = 105
   and column2 = 'AAAB'
				

??? ??? ??????? ????? Connection2 ??? ????? ???? ??? ?????? ????? UPDATE ??? ??????? ?? ????? IX_PAGE ????? Connection1. ????? ????? Connection2.
   Connection1 > UPDATE example1 SET column3 = 'CCCA' where column1 = 100
   and column2 = 'AAAA'
				

???? Connection1 ??? ???? ?? ?? ???? ?????? Connection2 ??? ??? ???? ???? ???. ?????? ?? ???? ???? ???? ???:
   97/04/20 15:13:50.07 spid17   *** DEADLOCK DETECTED with spid 18 ***
   spid 17 requesting UP_PAGE (waittype 0x8007), blocked by:
     IX_PAGE: spid 18, dbid 6, page 0x2c5, table example1, indid 0
     pcurcmd UPDATE(0xc5), input buffer: UPDATE example1 SET column3 =
   'CCCA' where column1 = 100 and column2 = 'AAAA'
   spid 18 waiting for UP_PAGE (waittype 0x8007), blocked by:
     IX_PAGE: spid 17, dbid 6, page 0x2c5, table example1, indid 0
     pcurcmd UPDATE(0xc5), input buffer: UPDATE example1 SET column3 =
   'CCCB' where column1 = 105 and column2 = 'AAAB'
   VICTIM: spid 17, pstat 0x0000 , cputime 20
				

??? ?????? 17 Spid (????? ???) ????? UP_PAGE ????? ?? ?????? ?????? ??? ?????? ??? ????? ???? ???. ?? ???? ??? ??? spid 18 ? ???? ????? ????? IX_PAGE ??? ?????? 0x2c5. 18 Spid ??????? ????? UP_PAGE ??? ??? ?????? ? ??? ?????? ????? IX_PAGE ??????? spid 17. ??????? ??? ??? ???? ???? ??? ??? ????? IX_PAGE ??? ???? ???????? ? ????? ?? ??? UP_LOCK. ????? ????? ????? ?? spids ?????? ??? ????? IX_PAGE ??? ??? ?????? ? ???? ??? ?????? ????? ??????? ??????? ??? ????? UP_PAGE ??? ???? ???? ????? UP_PAGE ???.

???? ??? ???? ?? ???? ???? ??? ?? ????? ?????? ???? ?? ??????? ?????? ?? ???? ????? ?? ????? ?????? ???? ?? ???????? ?????. ??? ?? ??? ??? ????, ???????? ????? ?????? ?????? IRL ????? ????? ???? ???? ???:
   sp_tableoption 'example1', 'insert row lock', false
				

???? 4: ?????? ??????? ??? ?????? ???????? ??? ??? ????

????? ?? ???? ???? ???? ??? ??? ?????? ????? spids ????? ??? ?????? ???? ????? ??? ??? ??????.
   Connection1 > BEGIN TRANSACTION
   Connection2 > BEGIN TRANSACTION
   Connection1 > INSERT INTO example1 VALUES (100, 'AAAA', 'CCC')
   Connection2 > INSERT INTO example1 VALUES (400, 'AAAB', 'CCC')
   Connection1 > UPDATE example1 SET column3 = 'CCCA' where column1 = 405
   and column2 = 'AAAA'
				

?? ??? ???????? Connection1 ??? ???? ?? ?? ???? ?????? Connection2. ?? ???? ??? ?????? ???? ???? Connection1 ?????? ?? ?? ???? ??? ????? Connection2 ????? ?? ??? ??????.
   Connection2 > UPDATE example1 SET column3 = 'CCCB' where column1 = 105
   and column2 = 'AAAB'
				

?? ??? ???????? Connection2 ??? ????? ???? ?? ?? ???? ?????? Connection1 ??? ???? ??? ???? ???? ???. ?? ???? ??? ?????? ????? ???? Connection2 ?????? ?? ?? ???? ??? ????? Connection1 ????? ??. ?????? ?? ???? ???? ???? ???:
   97/04/20 15:48:21.18 spid20   *** DEADLOCK DETECTED with spid 19 ***
   spid 20 requesting UP_PAGE (waittype 0x8007), blocked by:
     EX_PAGE: spid 19, dbid 6, page 0x2c4, table example1, indid 0
     pcurcmd UPDATE(0xc5), input buffer: UPDATE example1 SET column3 =
   'CCCB' where column1 = 105 and column2 = 'AAAB'
   spid 19 waiting for UP_PAGE (waittype 0x8007), blocked by:
     EX_PAGE: spid 20, dbid 6, page 0xc48, table example1, indid 0
     pcurcmd UPDATE(0xc5), input buffer: UPDATE example1 SET column3 =
   'CCCA' where column1 = 405 and column2 = 'AAAA'
   VICTIM: spid 20, pstat 0x0000 , cputime 60
				

???? ?? ??? ???? ??? ???? ???? ??? ?????? ???????? ???? ?????? ??? ????? ??????. ????? ???? ?????? ???? ?? ????? ????? ???? ?????? ???????? ?? ??? ?????? ?? ???? ????? ?????. ?????? ?? ????? ????? ???? ?????? ???????? ?? ???? ????? ?? 50 ?? ??????:
   create unique clustered index ex1ind1 on example1 (column1, column2)
   with fill factor = 50, PAD_INDEX
				

???? ???????? ??? ??? ????? ???? ?????? ???????? ??? ??? ??????? ??????? ??? ???? ????????? ??? ????? ???? ?????? ???????? (????? ???? PAD_INDEX). ?????? ???? ????? ?????? ????? ????? ??? ?????? ??? ???? ??? ???? ????.

?? ??? ???????? ???? ??????? ??? ????; re-organized ?? ???? ??????? ?????? ?????? ??? ????? ????? ??????. ?? ???? ?????? ???? ????? ?????? ??? ???? ?? ???? ??????? ?????? ????? ????? ??????. ??? ???? ???? ?? ???? ???????? ????? ????? ???? ?????? ???????? ?? ???? ??????? ???????.

??? ?? ??? ???? ?????? ???? ???? ??? ??????? ?? ??? ?????? ?? ??????? ????? (??? ???? ??????? char(255)) dummy1. ??? ???? ??? ????? ??? ???? ??? ???? ??? ???? ??? ??? ???? (?????? ?? ???? ??? ????). ???? ??? ????? ?? ????? ????? ????? ?? ????? ??? ????? ????? ???? ?????? ???????? ???????? ????? (??? ?? ??? ?? ????? ??? ????? ????? ???? ?????? ???????? ?????? ????). ???? ??? ??????? ??? ??????? ????? ??????? ??? ???? ?????.

??? ???? ?????? 5: ??????? ??????

???? ?????? ???? ??? ???? ??? ??? ???? (???????? deadlocks ???) ? ??? ??? ??? ?? ??????? ?????? deadlocks.

?? ??? ?????? ??????? ??? ????? example1 ????? ?? ???? ??? ????. ???? ??? ?????? ???????? ?? ????? ?????? ?? ??? ??? ??????:
   create table example1 (column1 int, column2 char(20), column3 char(50),
   dummy_column4 char (255), dummy_column5 char (255), dummy_column6 char
   (255))
   go
   create unique index ex1ind5 on example1 (column3, column2, column1,
   dummy_column4, dummy_column5, dummy_column6) with fill factor = 85
   go
   Connection1 > BEGIN TRANSACTION
   Connection2 > BEGIN TRANSACTION
   Connection1 > INSERT INTO example1 VALUES (100, 'AAAA', 'CCC', ' ', ' ',
   ' ', ' ')
   Connection2 > INSERT INTO example1 VALUES (400, 'AAAB', 'CCC', ' ', ' ',
   ' ', ' ')
   Connection1 > UPDATE example1 SET column3 = 'CCCA' where column1 = 401
   and column2 = 'AAAA'
				

??? ??? ??????? ??? ??? Connection1 ?????? Connection2 ????? ????? ????. ???? ??? ?? SQL Server ?? ???????? ??? ?????? ????? ???? ????? ?????? ??????? "?" ?????? ??????? "?" ???? ???? ??? ??????. ??? ????? Connection2 ????? ??? ?????? ??????? "? ??? ???????? Connection1 ??? Connection2 ????? ????????.
   Connection2 > UPDATE example1 SET column3 = 'CCCB' where column1 = 101
   and column2 = 'AAAB'
				

??? ??? ??????? ??? ??? Connection2 ?????? Connection1 ???? ??? ????? ?????? ??????? ???? ?? ??????? ?????? ?? ??? Connection1. ???? ??????? ???? ???? ???. ?????? ?? ???? ???? ???? ???:
   spid 20 requesting UP_PAGE (waittype 0x8007), blocked by:
     EX_PAGE: spid 19, dbid 6, page 0x12b5, table example1, indid 0
     pcurcmd UPDATE(0xc5), input buffer: UPDATE example1 SET column3 =
   'CCCB' where column1 = 101 and column2 = 'AAAB'
   spid 19 waiting for UP_PAGE (waittype 0x8007), blocked by:
     EX_PAGE: spid 20, dbid 6, page 0x1531, table example1, indid 0
     pcurcmd UPDATE(0xc5), input buffer: UPDATE example1 SET column3 =
   'CCCA' where column1 = 401 and column2 = 'AAAA'
   VICTIM: spid 20, pstat 0x0000 , cputime 300
				

???? ?? ??? ???? ??? ???? ???? ??? ?? ???? ????? ???? ????? ??? ?????? ???? ??? ????? ? ??????? ?? ?????. ??? ???? ??????? ??? ???? ???? Connection1 (????? ?? ??????? ?? ?????) ???????? pk ???? = 1 ? Connection2 ???? ?? pk ???? = 5 ????? ?? ??? ???? ??? (??? ?? ????? ??? pk = 3) ??? ???? deadlocks. ??? ??????? ????? ????? ??? ?????? ???? ?? ???? ???? ?? ??? ??????? ????? ?????? ?????? ??? ???????.

???? 6: ????? Nonclustered

?? ??? ???????? ?? ???? ????? ??? ??????? ???????? ??????? deadlocks. ?? ??? ??????? ???? ??????? ???? ??????? ???? ???? ???.

?????? ?? ????? ????????? ?? ????? ?????? ??????? ???????? ?? ??? ??????:
   create index ex1ind2 on example1 (column3) with fill factor = 90,
   PAD_INDEX
   Connection1 > BEGIN TRANSACTION
   Connection2 > BEGIN TRANSACTION
   Connection1 > INSERT INTO example1 VALUES (100, 'AAAA', 'CCBA', ' ', '
   ', ' ', ' ')
   Connection2 > INSERT INTO example1 VALUES (300, 'AAAB', 'CCCZ', ' ', '
   ', ' ', ' ')
   Connection2 > UPDATE example1 SET column3 = 'CCBA' where column1 = 105
				

?? ??? ???????? Connection2 ??? ???? ?? ?? ???? ?????? Connection1 ??? Connection1 ?? ???? ????? ????? ?? ???? ?????? ??? ?????? ??????? ??? Connection2 ????? ??? ?????.
   Connection1 > UPDATE example1 SET column3 = 'CCCZ' where column1 = 305
				

?? ??? ???????? Connection1 ??? ???? ?? ?? ???? ?????? Connection2 ??????? ?? ???? ???? ???. ???? ?? ???? ??? ?????? ??? ?????? Connection1 ????? ?????? ??? ??????? ???????? ??????? ?????? ??? ?? ????? ?????? Connection2 ? ????? ????? ??? ??? ??????. ?????? ?? ???? ???? ???? ??? ?????? ??? ??? ?????? ???? ???? ???:
   97/04/20 19:05:38.75 spid11   *** DEADLOCK DETECTED with spid 12 ***
   spid 11 requesting EX_PAGE (waittype 0x8005), blocked by:
     EX_PAGE: spid 12, dbid 6, page 0x112f, table example1, indid 0x2
     pcurcmd UPDATE(0xc5), input buffer: UPDATE example1 SET column3 =
   'CCCZ' where column1 = 305
   spid 12 waiting for EX_PAGE (waittype 0x8005), blocked by:
     EX_PAGE: spid 11, dbid 6, page 0x1108, table example1, indid 0x2
     pcurcmd UPDATE(0xc5), input buffer: UPDATE example1 SET column3 =
   'CCBA' where column1 = 105
   VICTIM: spid 11, pstat 0x0000 , cputime 50
				

???? ?? ??? ???? ???? ???? ??? ??? ?????? ????? ???? ???????. ??? ??? ?????? ????? ??? ?? ???? ??? ???? ? ???????? ???? ?? ??? ???? ??? ?????? ??? ?? ???? ????? ???? ??????? ??? ??????? ???????? ?? ?? ???? ????? ???????.

?? ???? deadlocks ?? ??????? ???? ?? ????? ??? ??? ?????? ???? ???? ???? ???? ??? spids ???????? ?? ???? ???? ??? ?? ?????? ????? ?????????. ?? ???? deadlocks ?? ?????? ??????? RLOCK ? XRLOCK ???? ???? ????? ?????? ??????. ?? ???? deadlocks ????? ???? ????? ?????? (PR_EXT NX_EXT ? UPD_EXT & EX_EXT).

?????? ??? ??????? ?????? ??? ????? deadlocks ????? ????? ???????? ?????? ???????:

t1200
????? ???? ??????? ?????/????? ??????? ??? ???? ???? ?? ??? ???? ???? ???? ??? ??????? ?? ??. ??? ????? ?? ???? ???? ???? ?? ???? ?????? ?? ??? ???????.

t1206
????? ???? ????????? ???? ?? ??????? ?? ??? spids ???????? ?? ???? ???? ???.

t1208
????? ??? ?????? ???? ???????? ???? ?? ?????? ?? ??? ??????. ????? ??? ?? ?????? ??? ???? ???????? ?? ???? ???? ??? ? ??????? ?? ???? ?????? ???? ????? ??? ?????.

???????

???? ???????: 169960 - ????? ??? ??????: 20/?????/1424 - ??????: 3.0
????? ???
  • Microsoft SQL Server 6.5 Standard Edition
????? ??????: 
kbmt kbhowto kbusage KB169960 KbMtar
????? ????
???: ??? ????? ??? ?????? ???????? ?????? ????? ???? ????? ?????????? ????? ?? ????????? ?????? ????. ???? ???? ?????????? ???? ?? ???????? ???????? ?????? ????????? ????? ????????? ???????? ????? ???????? ?????? ?? ?????? ??? ?? ???????? ???????? ?? ????? ??????? ?????? ??? ??????? ?????? ??. ?????? ?? ???? ??? ??????? ???????? ????? ?? ???? ????? ?????? ??? ????? ??? ????? ??????? ?? ????? ?? ?????? ??? ??? ??????? ??????? ?? ????? ????? ????? ????? ?????. ?? ????? ???? ?????????? ??????? ??? ????? ?? ??????? ?? ????? ?????? ?? ??? ????? ?? ????? ??????? ?? ???????? ?? ??? ???????. ???? ???? ?????????? ???????? ??? ????? ?????? ??????? ??????
???? ??? ????? ??????? ?????? ??????????169960
????? ??????? ?? ????? ???? ?? ????? ???????
?? ????? ????? ?????? ???? ???? ???? ??? ??????? ??? ?? ? ?? ??? ??????? Microsoft ??? ????? ??? ??????? ????? ?????? ?????

????? ???????

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com