Автор оригинала: 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 могут быть полезны при анализе ситуации взаимоблокировки. Первый предоставляет основную информацию, в то время как второй предоставляет больше информации, но его также гораздо сложнее читать.
Не забудьте отключить флаги трассировки, когда вы закончите собирать интересующую вас информацию о взаимоблокировке.