October 14, 2015 at 5:30 am
We have been investigating some deadlocks caused by one of our applications recently and have noticed that in the deadlock xml the trancount is always either 0 or 2.
<process id="process4c0b048" trancount="2" taskpriority="0" logused="4340" waitresource="OBJECT: 7:1850541726:2 " waittime="312" ownerId="3818415" transactionname="user_transaction" lasttranstarted="2015-10-08T14:31:00.893" XDES="0x258b77970" lockMode="X" schedulerid="1" kpid="3852" status="suspended" spid="281" sbid="0" ecid="0" priority="0"
A value of zero I'm assuming means that the sql statement isn't being called in a transaction.
At first I was thinking that the value of 2 was meaning that we have some nested transactions within our code, we've checked however and that doesn't seem to be the case. I then did some google-ing of "deadlock trancount=2" and got a number of matches (where people had posted sample deadlocks). I search of "deadlock trancount=1" returns no matching results. So it appears that this value can only be 0 or 2 and never 1.
Can anyone confirm that this is correct and/or point me at where it is documented?
thank you in advance
David
October 14, 2015 at 5:47 am
Documented? What's that? 🙂
It looks like if there's only auto-commit transactions (every statement starts and commits a transaction) then it's 0, if there's an explicit transaction, then you get a count of 2 or higher, the explicit transaction(s) and the automatically created and committed transaction that every statement generates.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 14, 2015 at 6:43 am
I personally think it's a bit misleading, as couldn't it be argued that in the first case that the trancount is actually 1?
The main thing is however that we don't seem to be missing anything. 🙂
thank you
David
October 14, 2015 at 6:55 am
Your hypothesis is false as the following code will show a trancount=1:
BEGIN TRAN
SELECT @@trancount;
COMMIT;
My guess is that your deadlock is happening in a trigger, in a transaction...and triggers do operate in their own implicit transaction.
October 14, 2015 at 7:18 am
Bill Talada (10/14/2015)
Your hypothesis is false as the following code will show a trancount=1:
BEGIN TRAN
SELECT @@trancount;
COMMIT;
Yes, that will return @@trancount of 1, but the OP's not talking about what @@trancount returns, rather what the trancount property of a deadlock graph shows.
If the following statement were to deadlock, the graph would show trancount of 2, no trigger necessary
BEGIN TRANSACTION
UPDATE SomeTable SET SomeColumn = 'abc';
COMMIT
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 14, 2015 at 9:06 am
I ran the test below in two sessions to create a simple deadlock and you're both correct that the graph does show a trancount of 2. My interpretation is that the graph process is creating a transaction before reporting and rolling back.
CREATE TABLE t1 (t1key int, name varchar(10));
INSERT into t1 values (1,'t1test');
CREATE TABLE t2 (t2key int, name varchar(10));
INSERT into t2 values (1,'t2test');
GO
SELECT * FROM t1;
SELECT * FROM t2;
GO
DBCC traceon (1222, -1);
GO
-- session 1
BEGIN TRAN;
UPDATE t1 SET name = 's1t1update' where t1key=1;
WAITFOR DELAY '00:00:10';
UPDATE t2 SET name = 's1t2update' where t2key=1;
WAITFOR DELAY '00:01:00';
ROLLBACK;
GO
-- session 2 (start within 10 sec of session 1)
BEGIN TRAN;
UPDATE t2 SET name = 's2t2update' where t2key=1;
WAITFOR DELAY '00:00:10';
UPDATE t1 SET name = 's2t1update' where t1key=1;
WAITFOR DELAY '00:01:00';
ROLLBACK;
GO
-- look in sql server log for deadlock xml
October 14, 2015 at 9:09 am
Bill Talada (10/14/2015)
My interpretation is that the graph process is creating a transaction before reporting and rolling back.
If that was the case, then it would be impossible to get a trancount of 0 in a deadlock graph, which is not the case. When statements that are not in a user transaction are involved in a deadlock, the trancount returns 0.
I suspect that the 'logic' (correct or otherwise) is that when there is no user transaction, the trancount is 0. When there is a user transaction, it's the trancount that would be returned by @@trancount within the insert, update, delete, which is 1 higher than the number of BEGIN TRANSACTION statements because each statement is itself part of an transaction that's started when the statement starts and autocommitted.
CREATE TABLE TestTrans (
Trancount INT
)
GO
BEGIN TRANSACTION
INSERT INTO TestTrans
SELECT @@TRANCOUNT;
COMMIT
SELECT * FROM dbo.TestTrans AS tt
DROP TABLE TestTrans
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 14, 2015 at 10:23 am
Done some more testing and it appears that yes..
* If a select statement deadlocks whilst in a transaction then the tran count is 1
* If an update deadlocks whilst in a transaction then the tran count is 2
* If an update deadlocks whilst in a nested (2 level) transaction then the tran count is 3
So it sort of makes sense now!
regards
David
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply