[MSSQL] Procedure [procedure_name], Line 1415 [Batch Start Line 0]The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

1. Microsoft Official Document

https://techcommunity.microsoft.com/t5/azure-database-support-blog/msg-3930-level-16-state-1-line-13-the-current-transaction-cannot/ba-p/3766378

Msg 3930, Level 16, State 1, Line 13 The current transaction cannot be committed

 

2. 오류 내용

Msg 3930, Level 16, State 1, Procedure up_IF_MA_EMP_C01, Line 1415 [Batch Start Line 0]
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

 

3. 솔루션

이 오류는 T-SQL 배치가 이미 포기된 트랜잭션을 커밋하려고 할 때 발생합니다.

 

<해당 에러 발생 조건>

    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.

 

<오류 재현 코드>

--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

이러한 시나리오를 처리하려면 커밋하기 전에 XACT_STATE() 값을 평가해야 한다.

-1로 표시되면 트랜잭션을 커밋할 수 없으며 대신 트랜잭션을 롤백해야 함을 의미