Рубрики
Без рубрики

Флаги трассировки взаимоблокировки SQL Server

Узнайте, как получить дополнительную информацию о причине взаимоблокировки SQL Server с помощью флагов трассировки и журнала ошибок SQL.

Автор оригинала: Vlad Mihalcea.

Вступление

В этой статье я объясню, как можно найти причину взаимоблокировки SQL Server с помощью флагов трассировки и журнала ошибок SQL.

Как правило, чем больше блокировок получено, тем выше вероятность взаимоблокировки. И, поскольку SQL Server по умолчанию использует 2PL, нередко приходится отслеживать проблемы взаимоблокировки, влияющие на производительность приложений.

Блокировка базы данных

Все системы реляционных баз данных используют блокировку. Каждый раз, когда необходимо изменить запись базы данных, создается эксклюзивная блокировка, гарантирующая изменение исходного состояния из одного согласованного состояния в другое.

Когда системы реляционных баз данных были впервые внедрены, для обеспечения согласованности и целостности данных использовался механизм управления параллелизмом 2PL (Двухфазная блокировка). Хотя алгоритм 2PL прост в реализации, он требует слишком большой блокировки, так как перед чтением любой записи базы данных требуется получить общую блокировку.

И, поскольку блокировка может повлиять на пропускную способность транзакций, большинство систем реляционных баз данных (например, Oracle, PostgreSQL и движок MySQL InnoDB) используют MVCC (Управление параллелизмом нескольких версий), чтобы избежать общих блокировок при чтении записи базы данных.

Только SQL Server по умолчанию использует механизм управления параллелизмом 2PL, хотя вы также можете переключать MVCC, используя изоляцию моментальных снимков с фиксацией чтения или уровни изоляции моментальных снимков.

Взаимоблокировка SQL

Таким образом, независимо от того, используете ли вы 2PL или MVCC, эксклюзивные блокировки всегда получаются при изменении строки таблицы, и по этой причине всегда существует вероятность возникновения взаимоблокировки базы данных.

При выполнении инструкций SQL, изображенных на диаграмме выше, мы получаем следующую ошибку взаимоблокировки SQL Server:

-- Alice updates the PostDetails entity
UPDATE post_details SET updated_by = 'Alice' WHERE post_id = 1

-- Bob updates the Post entity
UPDATE post SET title = 'ACID' WHERE id = 1

-- Bob wants to update the PostDetails entity
UPDATE post_details SET updated_by = 'Bob' WHERE post_id = 1

-- Alice wants to update the Post entity
UPDATE post SET title = 'BASE' WHERE id = 1

Transaction (Process ID 66) was deadlocked on lock resources with another process 
and has been chosen as the deadlock victim. Rerun the transaction.

Для отслеживания ресурсов, вовлеченных в взаимоблокировку, мы будем использовать флаги трассировки взаимоблокировки SQL Server.

Флаги трассировки взаимоблокировки SQL Server

SQL Server предоставляет широкий спектр флагов трассировки , которые можно активировать во время выполнения и изменять поведение компонента database engine по умолчанию.

Для отслеживания взаимоблокировок можно использовать два флага трассировки: 1204 , 1222 .

Как только флаги активированы, вы можете найти информацию о взаимоблокировке, обратившись к журналу ошибок sp_read хранимой процедуре.

С активацией этих флагов трассировки связаны накладные расходы, поэтому вам следует отключить их, как только вы закончите сбор информации о взаимоблокировке, которая вас интересовала.

Флаг трассировки взаимоблокировки SQL Server 1204

В 1204 флаг фиксирует некоторую базовую информацию об объектах блокировки, участвующих в взаимоблокировке.

Вы можете активировать 1204 флаг трассировки, подобный этому:

DBCC TRACEON (1204, -1)

Теперь, когда флаг установлен, мы можем зафиксировать информацию о тупике:

DBCC TRACEON 1204, server process ID (SPID) 58. 
This is an informational message only; no user action is required.
Deadlock encountered .... Printing deadlock information

Node:1
KEY: 7:72057594186301440 (1b7fe5b8af93) CleanCnt:2 Mode:X Flags: 0x1
 Grant List 1:
   Owner:0x00000202B2119CC0 Mode: X
   Flg:0x40 Ref:0 Life:02000000 
   SPID:61 ECID:0 XactLockInfo: 0x00000202A4E3B0A0
   SPID: 61 ECID: 0 Statement Type: UPDATE Line #: 1
   Input Buf: Language Event: 
      (@P0 nvarchar(4000),@P1 bigint)
      update post_details 
      set updated_by=@P0 
      where post_id=@P1                
Requested by: 
  ResType:LockOwner Stype:'OR'Xdes:0x00000202A4E4E408
  Mode: X SPID:58 BatchID:0 ECID:0 
  TaskProxy:(0x00000202B57508A0) Value:0xb2119300 Cost:(0/308)

Node:2
KEY: 7:72057594186366976 (1b7fe5b8af93) CleanCnt:2 Mode:X Flags: 0x1
 Grant List 1:
   Owner:0x00000202B2118800 Mode: X        
   Flg:0x40 Ref:0 Life:02000000 
   SPID:58 ECID:0 XactLockInfo: 0x00000202A4E4E440
   SPID: 58 ECID: 0 Statement Type: UPDATE Line #: 1
   Input Buf: Language Event: 
      (@P0 nvarchar(4000),@P1 bigint)
      update post 
      set title=@P0 
      where id=@P1                
Requested by: 
  ResType:LockOwner Stype:'OR'Xdes:0x00000202A4E3B068 Mode: X 
  SPID:61 BatchID:0 ECID:0 
  TaskProxy:(0x00000202A4E408A0) Value:0xb21191c0 Cost:(0/312)
null
Victim Resource Owner:
 ResType:LockOwner Stype:'OR'Xdes:0x00000202A4E4E408 Mode: 
 X SPID:58 BatchID:0 ECID:0 
 TaskProxy:(0x00000202B57508A0) Value:0xb2119300 Cost:(0/308)

Вы можете видеть заблокированные сеансы, а также инструкции, которые они выполняли, и эксклюзивные блокировки, которые они оба удерживают.

После того, как вы закончите захват информации о взаимоблокировке, вам следует отключить флаг трассировки, например:

DBCC TRACEOFF (1204, -1)

Флаг трассировки взаимоблокировки SQL Server 1222

В 1222 флаг фиксирует более подробную информацию об объектах блокировки, которые заблокированы.

Вы можете активировать 1222 флаг трассировки, подобный этому:

DBCC TRACEON (1222, -1)

Как только флаг активирован, мы получаем следующую информацию, напечатанную в журнале ошибок SQL при возникновении предыдущей ситуации взаимоблокировки.

DBCC TRACEON 1222, server process ID (SPID) 58. 
This is an informational message only; no user action is required.
deadlock-list
 deadlock victim=process202a2968ca8
  process-list
   process id=process202a2968ca8 taskpriority=0 logused=308 
   waitresource=KEY: 7:72057594186432512 (1b7fe5b8af93) waittime=429 
   ownerId=1423029 transactionname=implicit_transaction lasttranstarted=2021-03-31T12:20:27.677 
   XDES=0x202b6691068 lockMode=X schedulerid=2 kpid=18156 status=suspended 
   spid=58 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2021-03-31T12:20:27.753 
   lastbatchcompleted=2021-03-31T12:20:27.750 lastattention=1900-01-01T00:00:00.750 
   hostpid=0 loginname=sa isolationlevel=read committed (2) xactid=1423029 currentdb=7 
   lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058
    executionStack
     frame procname=adhoc line=1 stmtstart=62 stmtend=136 
     sqlhandle=0x02000000b635e21472fa213cb4a5435aacfea564eca44a2d0000000000000000000000000000000000000000
     
     frame procname=unknown line=1 
     sqlhandle=0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
    inputbuf
       (@P0 nvarchar(4000),@P1 bigint)
       update post 
       set title=@P0 
       where id=@P1                    
   process id=process202a2969468 taskpriority=0 logused=312 
   waitresource=KEY: 7:72057594186498048 (1b7fe5b8af93) waittime=429 
   ownerId=1423040 transactionname=implicit_transaction lasttranstarted=2021-03-31T12:20:27.743 
   XDES=0x202a9482408 lockMode=X schedulerid=2 kpid=22028 status=suspended 
   spid=61 sbid=0 ecid=0 priority=0 trancount=2 lastbatchstarted=2021-03-31T12:20:27.753 
   lastbatchcompleted=2021-03-31T12:20:27.750 lastattention=1900-01-01T00:00:00.750 
   hostpid=0 loginname=sa isolationlevel=read committed (2) xactid=1423040 currentdb=7 
   lockTimeout=4294967295 clientoption1=671088672 clientoption2=128058
    executionStack
     frame procname=adhoc line=1 stmtstart=62 stmtend=172 
     sqlhandle=0x02000000e470980c370b74d85a01cb6c76dd9883cc4b56970000000000000000000000000000000000000000
     
     frame procname=unknown line=1 
     sqlhandle=0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
    inputbuf
       (@P0 nvarchar(4000),@P1 bigint)
       update post_details 
       set updated_by=@P0 
       where post_id=@P1                    
  resource-list
   keylock hobtid=72057594186432512 dbid=7 objectname=high_performance_java_persistence.dbo.post 
   indexname=PK__post__3213E83F3748A10F id=lock202b2146900 mode=X associatedObjectId=72057594186432512
    owner-list
     owner id=process202a2969468 mode=X
    waiter-list
     waiter id=process202a2968ca8 mode=X requestType=wait
   keylock hobtid=72057594186498048 dbid=7 objectname=high_performance_java_persistence.dbo.post_details 
   indexname=PK__post_det__3ED787667769DE93 id=lock202a640cf00 mode=X associatedObjectId=72057594186498048
    owner-list
     owner id=process202a2968ca8 mode=X
    waiter-list
     waiter id=process202a2969468 mode=X requestType=wait

Как вы можете видеть, 1222 информация о трассировке более подробная. Мало того, что мы знаем информацию о сеансе и инструкции, но мы можем видеть уровни изоляции транзакций и объекты блокировки, вовлеченные в тупик (например,”)

После того, как вы закончите захват информации о взаимоблокировке, вам следует отключить флаг трассировки, например:

DBCC TRACEOFF (1222, -1)

Вывод

Оба 1204 и 1222 Флаги трассировки взаимоблокировки SQL Server могут быть полезны при анализе ситуации взаимоблокировки. Первый предоставляет основную информацию, в то время как второй предоставляет больше информации, но его также гораздо сложнее читать.

Не забудьте отключить флаги трассировки, когда вы закончите собирать интересующую вас информацию о взаимоблокировке.