BUG: Insert Trigger that Issues a Rollback May Cause Error 1203

• Microsoft SQL Server version 6.5 Service Pack 5a

SYMPTOMS

An INSERT trigger may cause a 1203 error when all of the following conditions are met:

• The trigger issues a rollback.



• There are TSQL statements after the rollback statement.



• The table the trigger is defined on has a foreign key constraint defined.



• The trigger is fired as a result of running an INSERT SELECT.



• The client that issues the INSERT SELECT statement sets implicit_transactions on.

Error : 1203, Severity: 20, State: 2
Caller of lock manager is incorrectly trying to unlock an unlocked object. spid=18 locktype=4 dbid=6 lockid=644197345.

WORKAROUND

To work around this problem, use any one of the following:

• Have the client not issue set implicit_transactions on.



• Use an INSERT statement instead of an INSERT SELECT statement to populate the table on which the trigger is defined.



• Modify the trigger to perform an explicit transaction using begin tran/commit tran.

STATUS

Microsoft has confirmed this to be a problem in SQL Server version 6.5 Service Pack 5a.

Additional query words: trigger; 1203; rollback; implicit_transaction

Keywords : kbSQLServ650sp5
Version : winnt:6.5 Service Pack 5a
Platform : winnt
Issue type : kbbug