Purpose:
Explain the following T-SQL error:
Msg 3930, Level 16, State 1, Line 13
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Solution:
This error happens when T-SQL batch is trying to commit a transaction that was already abandoned.
When could this happen?
We need specific condition for that to happen
a. we need to be in try..catch block
b. the transaction need face an error (such as primary key constraint violation/ foreign key violation etc.)
c. we are trying to commit the transaction.
to make it easier to understand here is a short code snippet to repro the same error
--preperation.
drop table if exists #tab1
create table #tab1(i int primary key)
insert into #tab1 values(1)
-- set xact abort to ON so the transaction will be invalidated if error happen
set XACT_ABORT ON
-- use try and catch block
begin try
begin transaction
insert into #tab1 values(1)
commit -- this commit will never happen, as we are violating the primary key constratint.
end try
begin catch
print XACT_STATE() -- xact_state = (-1) indictae that the transaction is uncommitable.
commit -- this will happen as part of the catch block, this will cause the exception of error 3930 to happen
end catch
To handle such scenario you should evaluate the XACT_STATE() value before committing.
if it is shows -1 this means that you cannot attempt to commit the transaction and you should rollback the transaction instead.
More information:
XACT_STATE (Transact-SQL)
