SQL server--deadlock in single reply

Many times, deadlocks are requested by two or more sessions to hold the other session of the lock while holding other session, but there are some special deadlock only by a single session lock triggered, today saw a related article, transported to share with you!

The code that caused the deadlock:

Execution Environment SQL SERVER 2012 (11.0.5058)

BEGIN TRANSACTION;GOSELECT 'CREATE TYPE EmailAddress'GOCREATETYPE EmailAddress from VARCHAR( the);GOSELECT 'CREATE Dbo.foo'GOCREATE PROCEDUREDbo.foo (@paramEmailAddress) asBEGIN     SETNOCOUNT on; DECLARE @x TABLE(e EmailAddress); INSERT  @x    SELECT  @param;END;GOSELECT 'EXEC Dbo.foo'GODECLARE @xEmailAddress;SET @x =N'whatever';EXECDbo.foo@param =N'whatever';SELECT "'GOSELECT 'ROLLBACK TRANSACTION'ROLLBACK TRANSACTION;

After executing the above code, the following deadlock view can be captured through profile:

Using sp_lock to view the lock will find:

On the metadata lock 7 (101:0:0), answer 61 has been granted the lock, but again requested the lock, resulting in the deadlock.

===================================================

This scenario rarely happens in the actual business, and no one typically puts the creation of stored procedures and user-defined types into a transaction, especially in the same transaction where the stored procedure is created and called. The way to avoid this problem is to put the creation of stored procedures and user-defined types into separate transactions to commit.

===================================================

Posture has gone up, the sister town stickers!

SQL server--deadlock in single reply