Deadlock in SQL Server 2000

Question

Deadlock in SQL Server 2000

Benny Lopez

Ten Centuries

Points: 1171
More actions
September 9, 2005 at 6:48 pm

#94883

Can somebody help me analyze this problem... This SQL Server has a database behind an ASP application. The application handles many users. We are getting tons of deadlocks. I have captured Trace data as well as turned on trace 1204 which produced the following information. Mind you, this is a little sample of the many deadlocks occurring. What is this telling me? What is Key:12:905158370:1 (2000c500d465), and KEY: 12:905158370:1 (e7003231e2c1)?
Thank you, DBAs.
.. Printing deadlock information
spid3
spid3     Wait-for graph
spid3
spid3     Node:1
spid3     KEY: 12:905158370:1 (2000c500d465) CleanCnt:1 Mode: X Flags: 0x0
spid3      Grant List 2::
spid3        Owner:0x41c57640 Mode: X        Flg:0x0 Ref:0 Life:02000000 SPID:206 ECID:0
spid3        SPID: 206 ECID: 0 Statement Type: SELECT Line #: 22
spid3        Input Buf: Language Event: exec usp_ValidateWebMember 'ngoldburt', 'natalya80'
spid3      Requested By:
spid3        ResType:LockOwner Stype:'OR' Mode: S SPID:222 ECID:0 Ec0x93FED540) Value:0xb27a4d20 Cost0/234)
spid3
spid3     Node:2
spid3     KEY: 12:905158370:1 (e7003231e2c1) CleanCnt:1 Mode: X Flags: 0x0
spid3      Grant List 3::
spid3        Owner:0x93859a40 Mode: X        Flg:0x0 Ref:0 Life:02000000 SPID:222 ECID:0
spid3        SPID: 222 ECID: 0 Statement Type: SELECT Line #: 22
spid3        Input Buf: Language Event: exec usp_ValidateWebMember 'rayvazyan', 'prime'
spid3      Requested By:
spid3        ResType:LockOwner Stype:'OR' Mode: S SPID:206 ECID:0 Ec0x9667D540) Value:0x800ff500 Cost0/254)
spid3     Victim Resource Owner:
spid3      ResType:LockOwner Stype:'OR' Mode: S SPID:222 ECID:0 Ec0x93FED540) Value:0xb27a4d20 Cost0/234)
spid3
.. Printing deadlock information
spid3
spid3     Wait-for graph
spid3
spid3     Node:1
spid3     KEY: 12:905158370:1 (4800cbc59677) CleanCnt:1 Mode: X Flags: 0x0
spid3      Grant List 1::
spid3        Owner:0xbd8d1d20 Mode: X        Flg:0x0 Ref:0 Life:02000000 SPID:229 ECID:0
spid3        SPID: 229 ECID: 0 Statement Type: SELECT Line #: 22
spid3        Input Buf: Language Event: exec usp_ValidateWebMember 'mcooper', 'mc1125'
spid3      Requested By:
spid3        ResType:LockOwner Stype:'OR' Mode: S SPID:206 ECID:0 Ec0x9667D540) Value:0x800ff500 Cost0/254)
spid3
spid3     Node:2
spid3     KEY: 12:905158370:1 (2000c500d465) CleanCnt:1 Mode: X Flags: 0x0
spid3      Grant List 2::
spid3        Owner:0x41c57640 Mode: X        Flg:0x0 Ref:0 Life:02000000 SPID:206 ECID:0
spid3        SPID: 206 ECID: 0 Statement Type: SELECT Line #: 22
spid3        Input Buf: Language Event: exec usp_ValidateWebMember 'ngoldburt', 'natalya80'
spid3      Requested By:
spid3        ResType:LockOwner Stype:'OR' Mode: S SPID:229 ECID:0 Ec0x96605540) Value:0x57a7a560 Cost0/21C)
spid3     Victim Resource Owner:
spid3      ResType:LockOwner Stype:'OR' Mode: S SPID:229 ECID:0 Ec0x96605540) Value:0x57a7a560 Cost0/21C)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

It looks like the procedure usp_ValidateWebMember is having problems when executed simultaneously by more than one connection.

You need to look at the code inside usp_ValidateWebMember in order to find what may be causing the deadlocks.

For the most parts, deadlocking is a design phenomenon - it happens when you have two different connections doing some procedure, but in reversal order to each other, so you end up with both waiting for resources to be released that is held by the other. This will never be resolved by just waiting, so when the server sees this, it kills one connection in order to let the other continue, and reports a deadlock in the log.

/Kenneth

rsharma SSCarpal Tunnel Points: 4865 More actions · Answer 2

One of your Deadlock outputs:

/***********************************************************************************************************/

spid3 Wait-for graph

spid3

spid3 Node:1

spid3 KEY: 12:905158370:1 (2000c500d465) CleanCnt:1 Mode: X Flags: 0x0

spid3 Grant List 2::

spid3 Owner:0x41c57640 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:206 ECID:0

spid3 SPID: 206 ECID: 0 Statement Type: SELECT Line #: 22

spid3 Input Buf: Language Event: exec usp_ValidateWebMember 'ngoldburt', 'natalya80'

spid3 Requested By:

spid3 ResType:LockOwner Stype:'OR' Mode: S SPID:222 ECID:0 Ec0x93FED540) Value:0xb27a4d20 Cost0/234)

spid3

spid3 Node:2

spid3 KEY: 12:905158370:1 (e7003231e2c1) CleanCnt:1 Mode: X Flags: 0x0

spid3 Grant List 3::

spid3 Owner:0x93859a40 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:222 ECID:0

spid3 SPID: 222 ECID: 0 Statement Type: SELECT Line #: 22

spid3 Input Buf: Language Event: exec usp_ValidateWebMember 'rayvazyan', 'prime'

spid3 Requested By:

spid3 ResType:LockOwner Stype:'OR' Mode: S SPID:206 ECID:0 Ec0x9667D540) Value:0x800ff500 Cost0/254)

spid3 Victim Resource Owner:

spid3 ResType:LockOwner Stype:'OR' Mode: S SPID:222 ECID:0 Ec0x93FED540) Value:0xb27a4d20 Cost0/234)

spid3

/***********************************************************************************************************/

As ken has mentioned, a deadlock (or a deadly embrace) cannot be resolved by waiting and that is why the database engine choses one of the spids as the victim and rolls back its work. In this deadlock output, there are two spids involved (i.e. it is not a deadlock chain): 206 and 222.

Out of these two, 222 was chosen as the deadlock victim and its work was rolled back.

Here is how you can interpret this information:

Node represents the entry number in the deadlock chain :

SPID: x ECID: x

Identifies the system process ID thread in cases of parallel processes. The entry SPID x ECID 0 represents the main thread, and SPID x ECID > 0 represents the sub-threads for the same SPID. In your case, it is the main thread in both.

Statement Type : Represents update, select, insert, delete, execute (SELECT in your case is because that is the statement that that spid was executing and was blocked on).

Mode: This is the lock mode that was granted for that spid - X denotes the exclusive lock.

All the locks in your case are related to KEY.

KEY identifies the key range within an index on which a lock is held or requested.

KEY is represented KEY: db_id:object_id:index_id; So, for:

1) SPID 206: DB_ID = 12, object_id = 905158370 and index_id = 1

2) SPID 222: DB_ID = 12, object_id = 905158370 and index_id = 1

By running this in Query Analyzer against the database :

select db_name(12) -- you will get the database name

select object_name(905158370) --replace the other one as well, you will get the object-name

For getting the index information, use the script from BOL: (replace carton_hdr with the table name that you find from above)...and don't get scared by the "_WA_" indexes...those are system generated optimization techniques...not physical indexes.

/******************************************************/

-- Declare variables to use in this example.

DECLARE @id int, @type char(2),@msg varchar(80),

@indid smallint, @indname sysname, @status int,

@indkey int, @name varchar(30)

SET NOCOUNT ON

SELECT @id = id, @type = type, @name = 'carton_hdr'

FROM sysobjects

WHERE name = 'carton_hdr' and type = 'U'

-- Start printing the output information.

print 'Index information for the '+@name+' table'

print '---------------------------------------'

-- Loop through all indexes in the authors table.

-- Declare a cursor.

DECLARE i cursor

FOR

SELECT indid, name, status

FROM sysindexes

WHERE id = @id

-- Open the cursor and fetch next set of index information.

OPEN i

FETCH NEXT FROM i INTO @indid, @indname, @status

IF @@FETCH_STATUS = 0

PRINT ' '

-- While there are still rows to retrieve from the cursor,

-- find out index information and print it.

WHILE @@FETCH_STATUS = 0

BEGIN

SET @msg = NULL

-- Print the index name and the index number.

SET @msg = ' Index number ' + CONVERT(varchar, @indid)+

' is '+@indname

SET @indkey = 1

-- @indkey (equivalent to key_id in the syntax diagram of

-- INDEX_COL) can be from 1 to 16.

WHILE @indkey <= 16 and INDEX_COL(@name, @indid, @indkey)

IS NOT NULL

BEGIN

-- Print different information if @indkey <> 1.

IF @indkey = 1

SET @msg = @msg + ' on '

+ index_col(@name, @indid, @indkey)

ELSE

SET @msg = @msg + ', '

+ index_col(@name, @indid, @indkey)

SET @indkey = @indkey + 1

END

PRINT @msg

SET @msg = NULL

FETCH NEXT FROM i INTO @indid, @indname, @status

END

CLOSE i

DEALLOCATE i

SET NOCOUNT OFF

/*****************************************************************************/

Basically what happened was this:

Spid 206 is blocked on its request for a SELECT (S) lock on a record in the table 905158370 because spid 222 has an exlusive (X) lock on it.

Spid 222 is blocked from its request for a SELECT (S) lock on a record in the table 905158370 because spid 206 has an exclusive (X) lock on it.

In order to break the deadlock, you have to ensure to break this deadly embrace. Since this involves a SELECT, please remember that in SQL Server (under the default ISOLATION LEVEL of READ COMMITTED), readers (select) block writers (inserts, deletes, updates) and vice-versa. So, in your procedure "usp_ValidateWebMember", check your select statements and ensure that those are written such they do not qualify the record that other spids might be locking. If they a scan of an index rather than an index seek operation, optimize that SQL/create useful indexes/modify existing indexes to disallow it selecting other records.

Since I do not have the stored procedure code, another thing to keep in mind would be that when an insert occurs in a table, if a FK exists, then a look-up is done on the FK (either index if the FK column(s) are indexed as they should be) or to the parent table - that is an implicit select. If you are not aware of this, it can lead to an issue as well. An example:

/**********************************************************************************************/

Two tables: TableA and TableB which are related by a foreign key relationship (See the script below).

Session1: User 1 holds a lock on TableA (The parent table) through an update statement. He does not commit/rollback the transaction.

Session2: User 2 tries to do an insert into TableB. Since the lock timeout setting is set to 5 seconds, the client gets an error that the Lock Request timed out. (Occurs because internally the engine tries to validate the insert criteria by doing a select on the primary Table "TableA").

Script to reproduce the issue:

DROP TABLE TABLEB

DROP TABLE TABLEA

CREATE TABLE TABLEA(COL1 INT NOT NULL, COL2 VARCHAR(10), COL3 INT, COL4 VARCHAR(10))

GO

ALTER TABLE TABLEA ADD CONSTRAINT PK_TABLEA PRIMARY KEY CLUSTERED (COL1)

GO

CREATE TABLE TABLEB (COL1 INT NOT NULL, COL2 VARCHAR(30) NOT NULL, COL3 VARCHAR(30))

GO

ALTER TABLE TABLEB ADD CONSTRAINT PK_TABLEB PRIMARY KEY CLUSTERED (COL1, COL2)

GO

ALTER TABLE TABLEB ADD CONSTRAINT FK_TABLEB_TO_TABLEA FOREIGN KEY (COL1) REFERENCES TABLEA (COL1)

GO

INSERT INTO TABLEA (COL1, COL2, COL3, COL4) VALUES (1, 'TEST', 1, 'TEST-SQL')

INSERT INTO TABLEA (COL1, COL2, COL3, COL4) VALUES (2, 'TEST2', 1, 'TEST-SQL2')

INSERT INTO TABLEA (COL1, COL2, COL3, COL4) VALUES (3, 'TEST3', 1, 'TEST-SQL3')

INSERT INTO TABLEB (COL1, COL2, COL3) VALUES (1, 'TEST', 'TEST-SQL')

GO

--SESSION 1:

set cursor_close_on_commit off

set ansi_defaults off

BEGIN TRAN

UPDATE TABLEA SET COL4 = 'TEST-SQL1' WHERE COL1 = 2

--Do Not Commit Yet

--SESSION 2:

set cursor_close_on_commit off

set ansi_defaults off

set transaction isolation level read uncommitted

begin tran

select * from tableA with (nolock) where col1 = 2

set lock_timeout 5000

INSERT INTO TABLEB (COL1, COL2, COL3) VALUES (2, 'TEST-MOD', 'TESTSQL1')

--you will get the lock timeout error after 5 seconds

--ROLLBACK

/**********************************************************************************************/

A solution in the above mentioned "INSERT" scenario of a block would be to create the PK as non-clustered index.

Hth.

Benny Lopez Ten Centuries Points: 1171 More actions · Answer 3

Ken/Sharma, Thank you very much for your help.

And Sharma, your explanation on how to interpret the information was great!!!.

Phenomenal was your insight that too many rows are qualified by the SELECT statement and I believe that it is the root cause of this problem. As a result we have re-written the query and moved it into the pre-production environment. We also changed the index and maded it as a non-clustering index. It will be few days before we move the changes into production. I will let everybody know if these changes result in no deadlocks in our production environment.

Thank you very much.

Ben

rsharma SSCarpal Tunnel Points: 4865 More actions · Answer 4

You are welcome Ben.

Since you are operating under the default isolation level of READ COMMITTED, another thing that you may want to look into for your SELECT statement(s) is the usage of READPAST (reading past locked records) or NOLOCK (dirty reads) locking hints. That is necessary only if there is no other way of avoiding the contention (I have seen some cases like this in ERP applications)...and be aware of what those two locking hints do before/if you decide to use them. BOL has more information on these.

Hth