Dead lock occured for table variable

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Dead lock occured for table variable

Author

Topic

shaggy
Posting Yak Master

248 Posts

Posted - 2009-05-19 : 03:18:46

I beleive table variable creation is based on every instance.when concurrent users are accessing i can see from errorlog that dead lock occured on insert of table variable.
Insert Query what iam using Eg:-
Insert @table1 select * from table2 (nolock)
can anyone explain about how table variable works and how deadlock occured for insert of table variable.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-05-19 : 03:23:39

A table variable is ONLY accessible from the scope where it is running.
Can you post the complete and full error message?

E 12°55'05.63"
N 56°04'39.26"

shaggy
Posting Yak Master

248 Posts

Posted - 2009-05-19 : 03:34:53

Thanks for your reply Peso

Please find the error msg below

2009-05-17 15:34:22.200,spid7s,Node:1
2009-05-17 15:34:23.410,spid7s,OBJECT: 7:904390291:0 CleanCnt:60 Mode:S Flags: 0x1
2009-05-17 15:34:23.410,spid7s, Grant List 0:
2009-05-17 15:34:23.410,spid7s, Grant List 2:
2009-05-17 15:34:23.410,spid7s, Grant List 3:
2009-05-17 15:34:23.410,spid7s, Owner:0x1245A780 Mode: S Flg:0x40 Ref:2 Life:00000001 SPID:91 ECID:0 XactLockInfo: 0x14D8E2A8
2009-05-17 15:34:23.410,spid7s, SPID: 91 ECID: 0 Statement Type: INSERT Line #: 76
2009-05-17 15:34:23.410,spid7s, Input Buf: RPC Event: Proc [Database Id = 7 Object Id = 1031010754]
2009-05-17 15:34:23.410,spid7s,Requested by:
2009-05-17 15:34:23.410,spid7s, ResType:LockOwner Stype:'OR'Xdes:0x1AA1C280 Mode: IX SPID:64 BatchID:0 ECID:0 TaskProxy:(0x43382354) Value:0x1a9f6260 Cost:(0/0)
2009-05-17 15:34:23.410,spid7s,NULL
2009-05-17 15:34:23.410,spid7s,Node:2
2009-05-17 15:34:23.410,spid7s,OBJECT: 7:904390291:0 CleanCnt:60 Mode:S Flags: 0x1
2009-05-17 15:34:23.410,spid7s, Grant List 0:
2009-05-17 15:34:23.410,spid7s, Grant List 2:
2009-05-17 15:34:23.410,spid7s, Grant List 3:
2009-05-17 15:34:23.410,spid7s, Owner:0x1245A780 Mode: S Flg:0x40 Ref:2 Life:00000001 SPID:91 ECID:0 XactLockInfo: 0x14D8E2A8
2009-05-17 15:34:23.410,spid7s,Requested by:
2009-05-17 15:34:23.410,spid7s, ResType:LockOwner Stype:'OR'Xdes:0x51BBF250 Mode: IX SPID:329 BatchID:0 ECID:0 TaskProxy:(0x34CD0354) Value:0x37acbd00 Cost:(0/0)
2009-05-17 15:34:23.410,spid7s,NULL
2009-05-17 15:34:23.410,spid7s,Node:3
2009-05-17 15:34:23.410,spid7s,OBJECT: 7:904390291:0 CleanCnt:60 Mode:S Flags: 0x1
2009-05-17 15:34:23.410,spid7s, Grant List 0:
2009-05-17 15:34:23.410,spid7s, Grant List 2:
2009-05-17 15:34:23.410,spid7s, Owner:0x2C3B49C0 Mode: S Flg:0x40 Ref:2 Life:00000001 SPID:329 ECID:0 XactLockInfo: 0x51BBF278
2009-05-17 15:34:23.410,spid7s, SPID: 329 ECID: 0 Statement Type: INSERT Line #: 76
2009-05-17 15:34:23.410,spid7s, Input Buf: RPC Event: Proc [Database Id = 7 Object Id = 1031010754]
2009-05-17 15:34:23.410,spid7s, Grant List 3:
2009-05-17 15:34:23.410,spid7s,Requested by:
2009-05-17 15:34:23.410,spid7s, ResType:LockOwner Stype:'OR'Xdes:0x14D8E280 Mode: IX SPID:91 BatchID:0 ECID:0 TaskProxy:(0x3F29C354) Value:0x36708bc0 Cost:(0/0)
2009-05-17 15:34:23.420,spid7s,NULL
2009-05-17 15:34:23.420,spid7s,Victim Resource Owner:
2009-05-17 15:34:23.420,spid7s, ResType:LockOwner Stype:'OR'Xdes:0x1AA1C280 Mode: IX SPID:64 BatchID:0 ECID:0 TaskProxy:(0x43382354) Value:0x1a9f6260 Cost:(0/0)
2009-05-17 15:34:23.420,spid7s,NULL
2009-05-17 15:34:23.420,spid7s,Victim Resource Owner:
2009-05-17 15:34:23.420,spid7s, ResType:LockOwner Stype:'OR'Xdes:0x51BBF250 Mode: IX SPID:329 BatchID:0 ECID:0 TaskProxy:(0x34CD0354) Value:0x37acbd00 Cost:(0/0)
2009-05-17 15:34:50.910,spid7s,Deadlock encountered .... Printing deadlock information
2009-05-17 15:34:50.910,spid7s,Wait-for graph
2009-05-17 15:34:50.910,spid7s,NULL

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2009-05-19 : 10:13:39

Where do you see the reference to a table variable?

The table involved is in database 7 and has the object ID 904390291. Use the Object_Name function to get its name.
Also use the object_name function to get the procedure with an ID of 1031010754, and check what the insert on line 76 is, also check what other queries are done against that table earlier in the proc.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources