Microsoft KB Archive/245672

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

Article ID: 245672

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Service Pack 5a

-



This article was previously published under Q245672



BUG #: 18917 (SQLBUG_65)



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.

Following is the error message:

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.

This error should not affect other connections.



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 6.5 Service Pack 5a.

Additional query words: trigger; 1203; rollback; implicit_transaction

Keywords: kbbug kbpending KB245672

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.