June 6, 2017 at 12:41 pm
I have a stored procedure that has multiple commands: Inserts, deletes, creates temp tables. I put Begin Transactions .... Commit in between most of them. We are getting deadlocks when users call the sproc at times. This is why we put the Begin Trans...commit to see if that would help. The sproc can take less than a minute or 20 minutes to run depending on the criteria. Does the Begin Trans ....Commit help? We are also calling another sproc at the end of this sproc, is that good practice?
Thanks,
Alex
June 6, 2017 at 12:49 pm
Begin Tran ... Commit is about atomic operations and isolation, it's a transaction block. If you need the inserts, deletes and other things to be atomic, you need a transaction block.
As for deadlocks, you need to look at the deadlock graph, see what resources and queries are involved and debug from there.
This may help: https://www.simple-talk.com/sql/performance/sql-server-deadlocks-by-example/
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
June 6, 2017 at 1:11 pm
Generally speaking, the bigger "begin tran ... commit tran" block is, the more possible blocking / deadlocking event arise. In your case, I think you need to figure out the blocking/deadlocking hotspot (i.e. which resource causes the blockng/deadlocking), you can use either XEvent or SQL Profiler or other mechanism (3rd party tool) to figure out these hot spots and then you can tune your SP either from business process logic perspective or t-sql optimization perspective (adding index or refactoring code?) to minimize the blocking/deadlocking
June 8, 2017 at 12:25 pm
If in a sproc I have multiple inserts, updates, deletes and user A calls the sproc and it is processing all the commands within the sproc and say there is an update halfway thru... and then user B calls the sproc and he is then wanting to update the table but user A's update is not finished and user B is then at the update command. Does User B start the update command as soon as user A finishes with the update or ONLY after the entire sproc is finished for User A ?
June 8, 2017 at 1:57 pm
User B starts the update immediately, but will probably be blocked until User A's session releases locks, which will be when the transaction commits.
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
June 14, 2017 at 11:37 am
GilaMonster - Thursday, June 8, 2017 1:57 PMUser B starts the update immediately, but will probably be blocked until User A's session releases locks, which will be when the transaction commits.
On a related question ... occasionally when a simple SQL is applied with a begin and a commit ... and the Query Messages results window shows the results as having been committed,
then the user closes the query or ends the SQL server session and the message is saying to the effect of " you have uncommitted transactions, commit these before you exit SQL server ?" why in general would these show as being committed in the results but not actually committed until the exit ? This seems to happen most often on a delete of data when the application has the same data displayed in a window....
June 14, 2017 at 12:03 pm
That's not a deadlock at all. That's an uncommitted transaction and SSMS warning you that you should have committed the transaction before closing the window.
To get that, the COMMIT TRANSACTION wasn't run for some reason
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
June 14, 2017 at 1:31 pm
GilaMonster - Thursday, June 8, 2017 1:57 PMUser B starts the update immediately, but will probably be blocked until User A's session releases locks, which will be when the transaction commits.
O Thanks Gail !
We use a
BEGIN TRANSACTION
delete something
IF @@ERROR <> 0
BEGIN
ROLLBACK
PRINT 'ERROR OCCURED - ROLLBACK'
END
ELSE
BEGIN
COMMIT
PRINT 'SQL SUCCESSFUL'
END I'll try using the DBCC Opentran after the SQL prints the 'SQL SUCCESSFUL' and see if it has any trans open ...
We are thinking we should change the default on the blocked process threshold to allow blocked process reports to be produced...
Do you think changing this default would produce any use full info? Thanks John
June 14, 2017 at 1:42 pm
Use TRY ... CATCH blocks, not the old SQL 2000-style error handling.
If that's the only code that you have and you only ever run it once, there will not be an open transaction after the PRINT 'SQL SUCCESSFUL'. There cannot be, it was started, it was committed.
Now, if someone stops the query while it's deleting, you'll have a transaction left open as neither the commit nor the rollback were reached
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
June 15, 2017 at 6:37 am
GilaMonster - Wednesday, June 14, 2017 1:42 PMUse TRY ... CATCH blocks, not the old SQL 2000-style error handling.If that's the only code that you have and you only ever run it once, there will not be an open transaction after the PRINT 'SQL SUCCESSFUL'. There cannot be, it was started, it was committed.
Now, if someone stops the query while it's deleting, you'll have a transaction left open as neither the commit nor the rollback were reached
Would SET XACT_ABORT ON help with dangling commits?
From what I've read that seems to be the purpose, to roll back when an error occurs and kills the SP. Does stopping the SP prematurely count as an error for XACT_ABORT?
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply