begin tran ... commit tran

while @@trancount > 0
begin
 commit tran
end

set transaction isolation level read committed

Process ID 61 attempted to unlock a resource it does not own: DATABASE: 7 Retry the transaction, because this error may be caused by a timing condition. If the problem persists, contact the database administrator.

ALZDBA (12/19/2008)

---

Scenarion 1 : add

begin tran ... commit tran

Just encapsulating the trigger with its own Begin tran ... commit tran, off course didn't do the trick.
Because: that tran is just an augmentation of the @@trancount which - because of the "set implicit transactions on" >0 at that time.

When I added a while loop

while @@trancount > 0
begin
 commit tran
end

resulted in a failure of the biztalk apps.

Commit Tran
IF @@trancount = 1 
 Begin
    Commit Tran
    Begin Tran
 End

jeffrey yao (12/16/2008)

---

Informative article.
But I am still trying to understand why the transaction isolation level plays a role here. Before a connection is established, there is no isolation level to that "would-be" connection. The only thing I can think of is that the user table used in the trigger have a table-scope lock (by either another existing connection or some other applications) that prevents the table being accessed when the trigger trying to update the user table when a new connection is being established. So this may not have anything to do with the isolation level, but because of something else, like lock escalation, or simply a table-wide lock on the hot table.