May 24, 2018 at 6:13 am
Hi Experts,
I want to capture all the spid info and their associated queries,lock info till the point where deadlock scenario has occured.
From system health check session xevent , I can see only the sql stmts involved in the deadlock but not the history of all the sql stmts ran under those spids.
So, looking to capture all that info. If I am able to get that piece of information, I can build a story and explain the business team what could have happened which lead to the deadlock situation.
Currently, deadlocks are happening from application code which is written in Entity framework code first approach and we arent getting much help and every one is back on DB Team as they deadlock is a database thing.
They are doing some bulk insert using .net code and they wanted to know why Tablelock is getting acquired why not rowlocks. Before that, I want to see if I can collect all the associated statement from connection 1 & connection 2.
demo :
CREATE TABLE t1 (c1 INT);
INSERT INTO t1 VALUES (1);
GO
CREATE TABLE t2 (c1 INT);
INSERT INTO t2 VALUES (1);
GO
Assume below deadlock scenario
Connection 1 or SPID = 55
=========================
BEGIN TRAN
UPDATE t1 SET c1 = 2;
SELECT * FROM t2;
Connection 2 or SPID = 56
=========================
BEGIN TRAN
UPDATE t2 SET c1 = 2;
SELECT * FROM t1;
Thanks,
Sam
May 25, 2018 at 8:55 am
TABLOCK is an option on BULK INSERT so find out if they are using that option, if they are actually using the BULK INSERT statement. The other reason that they would be getting a Table Lock is lock escalation, the lock manager is determining that is more efficient to lock the whole table because of the number of row locks that will have to be managed.
Deadlocks are normally caused by order of operations. SPID 1 updates table A and then Table B in a transaction and SPID 2 updates table B then table A in a transaction and because that creates circular blocking a deadlock occurs to allow operations to continue. The devs should be able to tell just from the code provided in the deadlock event where the code is that is causing the deadlock.
You can create an XE session and turn on Causality Tracking and I think you'll get what you want. Be sure to filter ALL the events based on the database and if you can the application that is experiencing the deadlocks.
Another option is to evaluate the indexes on the tables involved in the deadlock scenario. Better indexing will speed up transactions and may allow for row or key locks instead of table locks.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
May 25, 2018 at 9:13 pm
Jack Corbett - Friday, May 25, 2018 8:55 AMTABLOCK is an option on BULK INSERT so find out if they are using that option, if they are actually using the BULK INSERT statement. The other reason that they would be getting a Table Lock is lock escalation, the lock manager is determining that is more efficient to lock the whole table because of the number of row locks that will have to be managed.Deadlocks are normally caused by order of operations. SPID 1 updates table A and then Table B in a transaction and SPID 2 updates table B then table A in a transaction and because that creates circular blocking a deadlock occurs to allow operations to continue. The devs should be able to tell just from the code provided in the deadlock event where the code is that is causing the deadlock.
You can create an XE session and turn on Causality Tracking and I think you'll get what you want. Be sure to filter ALL the events based on the database and if you can the application that is experiencing the deadlocks.
Another option is to evaluate the indexes on the tables involved in the deadlock scenario. Better indexing will speed up transactions and may allow for row or key locks instead of table locks.
Thank you.
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply