SQLTeam.com | Weblogs | Forums

Executing Query ends with "transaction lock" error


#1

Hello, everyone. I have no idea what's going on here.

Update user_int
set value_56 = mf.amount --SELECT *
from manual.dbo.MFimport mf
join student s on mf.ssn = s.student_ssn
join stu_award_year say on s.student_token = say.student_token
join user_int ui on say.stu_award_year_token = ui.stu_award_year_token
where say.award_year_token = '2016'
and mf.trans = '1210'
and mf.document = ''
and mf.SSN=s.student_ssn

When I execute the query above, it takes about 15 minutes to get a "Query terminated, transaction lock" error message. However, the table does update correctly. Interestingly enough, if I change the "where say.award_year_token = '2016'" to be '2015' instead, the query executes in about 2 seconds.

Additionally, if I run the commented SELECT statement, the SELECT query executes in about 1 second.

Anyone have any thoughts on this?


#2

Update ui
set ui.value_56 = mf.amount --SELECT *
from manual.dbo.MFimport mf
join student s on mf.ssn = s.student_ssn
join stu_award_year say on s.student_token = say.student_token
join user_int ui on say.stu_award_year_token = ui.stu_award_year_token
where say.award_year_token = '2016'
and mf.trans = '1210'
and mf.document = ''
and mf.SSN=s.student_ssn


#3

Thanks, yosiasz! I tried your suggestion, but it didn't work. :confused:


#4

Are you running the query from SSMS, or is it from a client application? In either case, while running the long running query, in another SSMS query window run the command sp_who2. The BlkBy column will show you what if anything is blocking what else. Also, sp_whoisactive is a good (and free) tool to see what is going on


#5

Thanks! I'll take a look at this. Hopefully it'll provide some insight as to what in the world is going on!