I have procedure for handling transaction detail there is aprox 60 user access my application for saving data at once(i.e.) this procedure will be hit by all user at once. My proc like this
Create Proc InsertUpdateData
As
Begin
Set tran Isolation Level Serializable
Begin Tran
Begin Try
Declare @SeqNo BigInt
select @SeqNo=TblVal+1 from GetSeq Where Year=2014 AND ColName='Tbl1'
update GetSeq set TblVal=@SeqNo Where Year=2014 AND ColName='Tbl1'
--- Some Insertion and updation in different different table
--------------------------
----- End of insertion and updation
Commit tran
End Try
Begin Catch
return -1
Rollback
Insert into ErrorLog Select ERROR_LINE(),ERROR_MESSAGE()
End catch
End
But In a day i got deadlock problem in line select @SeqNo=TblVal+1 from GetSeq Where Year=2014 AND ColName='Tbl1'
update GetSeq set TblVal=@SeqNo Where Year=2014 AND ColName='Tbl1'
while i use sql heartbeat for detecting deadlock.
I have use Transaction level Serializable for avoiding phantom,dirty and repeatable read.
This GetSeq table has
TableName column
TblVal(this will increase when i use update query to Getseq)
Can Any one help me how can i avoid deadlock with using Serializable isolation level for this
2 Replies
Know the answer? Post it — somebody with the same question will find it here.
Sign in to answer this question
It is the same account you read, post and publish with — and you will come straight back to this page.
ShehzadPosted Jun 3, 2014, 5:19 AM
Try this & check whether it works for you.
select @SeqNo=TblVal+1 from GetSeq with (UPDlock) Where Year=2014 AND ColName='Tbl1'
Regards
Shehzad
Anupam SinghPosted May 24, 2014, 8:27 AM
Locking in sql is handled by itself and it handles in best way.
although if you want to read records during transaction you can try with(nolock)
which is just like reading un-commited records.
hope it helps.