search

minus5 / gofreetds

Go Sql Server database driver.
MIT License
113 stars 48 forks source link

Recovering from SQL Server deadlocks #36

Closed MarkSonghurst closed 8 years ago

MarkSonghurst commented 8 years ago

On occasion I encounter a deadlock error whilst calling a stored procedure on my SQL Server:

Msg 20018, Level 13
General SQL Server error: Check messages from the SQL Server

Msg 20018, Level 16
General SQL Server error: Check messages from the SQL Server

Msg 50000, Level 13, State 51
Server 'MUSTANG', Line 506
    Msg 1205,Line 449: **>> ERROR(UP_IDAlpha_BetaCreate):Transaction (Process ID 69) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Msg 266, Level 16, State 2
Server 'MUSTANG', 
    Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.

Following this error all consequent proc calls (to other procs, or this same one) fail with error messages such as:

Msg 20019, Level 7
Attempt to initiate a new Adaptive Server operation with results pending
dbrpcinit failed

As the initial deadlock error is the cause of all consequent failures, is there a way to recover from it? Maybe we should mark the connection as isDead within conn_sp.go ? Or is this a non-recoverable situation that would require a complete draining of the connection pool and then reconnecting to the database?

My DBA is working on a proc which will deliberately deadlock so I can develop against it, but if anyone knows how to handle this issue I would be grateful for their advice.

I'm only using stored proc calls, so I don't think using the gofreetds transaction functions (Begin, Rollback) would help me - or would they?

Thanks in advance for any help.

ianic commented 8 years ago

Is there a possibility that you have begin transaction without matching rollback. If that is the case you should call rollback after deadlock. Or set XACT_ABORT to let sever do that. If you are starting transaction from code use DoInTrasaction on gofreetds connection. It handles cleanup on errors.

MarkSonghurst commented 8 years ago

Thanks @ianic I will work on a pull request which modifies the current capturing of errors from SQL Server in a way which we can see the error codes quickly - I specifically need to trap 1204 and 1205 errors so I can retry the deadlocked proc call.

I'll investigate replacing Conn.Error and Conn.Message with Go maps.