Sleeping COMMIT transaction blocking

Question

Post reply

Sleeping COMMIT transaction blocking

alex.palmer

SSCrazy

Points: 2292
More actions
July 13, 2018 at 4:56 am

#407180

Hi
yesterday a simple update was run against a table, which should have updated 3 rows in a table, executed from SSMS, running under the default, Read Committed isolation.
begin transaction
    update Table1
    set COL1=COL1 & 'Update'
    where COL2 between 85 and 87
--commit
After this was run the COMMIT was sent. I can see in my SQL monitoring tools that the COMMIT for that SPID then sleeps and blocks other threads with exclusive locks on Table1.
It stays sleeping and blocking until the SPID was killed.
I'm having difficulty explaining why this commit didn't complete. It's a really small update.
Any Ideas?
Cheers
Alex

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

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

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 1

can you consistently replicate this issue by running the same SQL?

prior to issuing commit open a new session and run
select * from sys.sysprocesses
where spid=add spid

what does the last waittype and open_tran say?

issue the commit and then check again

do you have any underlying triggers ?

***The first step is always the hardest *******

SGT_squeequal SSCertifiable Points: 7167 More actions · Answer 2

should also added, what i'm thinking is that your update could be part of a nested transaction, even though you have issued a commit no data has been committed and the data is still locked, i know a lot of people who misunderstand how transactions work.

test steps to replicate your issue

create table test (col1 int,col2 int)

insert into test select 1,2 insert into test select 2,23 insert into test select 3,24 insert into test select 4,25

begin tran select 1 begin tran doupdate update test set col2=9

now jump to one of the other sessions

run
select * from test

query appears to hang

jump back to the first session and run commit (only once )

now jump to the third session

run
select * from sys.sysprocesses where spid=addspid or blocked=1addspid

now you should see the the select getting blocked, this is because the data in session one has not yet been committed, only when the session trancount = 0 will data be committed/rolled back
if you look at the info from the sys.sysproceses you will see a trancount of 1
im sure someone can give a more technical explanation i like to keep it simple 🙂

***The first step is always the hardest *******

alex.palmer SSCrazy Points: 2292 More actions · Answer 3

Thanks for the reply

This is a production server so I'd be taken out and shot if I tried to replicate the issue on it. We've not been able to replicate on a non prod equivalent though.

The process is so simple though, that there must have been something else going on, I'm really looking for any ideas of what could have stopped that transaction for completing the commit

It wasn't part of a larger transaction. It was run as explained above. What I didn't mention is that there's merge replication on the table. So there are triggers. The exclusive lock taken out on Table1 and MSMerge_genhistory tables that caused issues for us.

There were no other blocking threads.

Cheers
Alex

Sue_H SSC Guru Points: 90860 More actions · Answer 4

alex.palmer - Friday, July 13, 2018 6:39 AM
Thanks for the reply
This is a production server so I'd be taken out and shot if I tried to replicate the issue on it. We've not been able to replicate on a non prod equivalent though.
The process is so simple though, that there must have been something else going on, I'm really looking for any ideas of what could have stopped that transaction for completing the commit
It wasn't part of a larger transaction. It was run as explained above. What I didn't mention is that there's merge replication on the table. So there are triggers. The exclusive lock taken out on Table1 and MSMerge_genhistory tables that caused issues for us.
There were no other blocking threads.
Cheers
Alex

Having merge replication in there changes a lot. I don't know if you will be able to find out what all was going on after the fact but I'd wonder about the merge cleanup and if that didn't contribute to the issues. Make sure the cleanup is working and check the size of the MSMerge_genhistory table. Make sure the retention settings are reasonable for the environment. 14 days can often be too much.

Sue