BUG: Insert Trigger that Issues a Rollback May Cause Error 1203 (245672)
The information in this article 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.
| Modification Type: | Major | Last Reviewed: | 10/3/2003 |
|---|
| Keywords: | kbbug kbpending KB245672 |
|---|
|