Deadlock at trigger

12 Aug 2006 4:28 AM

alex121

Deadlock at trigger

a trigger need to insert or update record at the other table in high traffic
environments
however, the deadlock happens; ie, a trigger running twice at the same time
want to need to insert record at the table.

i trid to change isolation as SERIALIZABLE or REPEATABLE , but i cannot
solve the problem. i guess the "while loop" affects the result because i try
to cancel the loop and execute smoothly.

? How to solve the deadlock???
Thx

-----------------------------
i used the following commands to change isolation level:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
begin transaction
commit transaction

-----------------------------
the code are as follows:
                    declare setskuCursor cursor
                    local
                    static
                    for select item, quantity from invset where sku = @sku
                    open setskuCursor
                    fetch next from setskuCursor into @invsetitem, @invsetqty
                    while @@fetch_status = 0
                        begin

                            select @balsku = sku, @opendate = opendate from invbal where sku =
@invsetitem
                            if (@balsku is not null)
                                begin
                                    if @txdate <= @opendate
                                        update invbal set slsqtynow = slsqtynow + @itmtxqty * @invsetqty
where sku = @invsetitem
                                    if @txdate > @opendate
                                        update invbal set slsqtynxt = slsqtynxt + @itmtxqty * @invsetqty
where sku = @invsetitem
                                end
                            else
                                begin

                                    INSERT INTO INVBAL (SHOP, SKU, OPENDATE, SLSQTYNOW) VALUES (@shop,
@invsetitem, @bizdate, @itmtxqty * @invsetqty)

                                end
                            fetch next from setskuCursor into @invsetitem, @invsetqty
                        end
                    close setskuCursor
                    deallocate setskuCursor
-------------------------------------------------------------------

12 Aug 2006 7:32 AM

David Portas

alex121 wrote:
Show quote

> Deadlock at trigger
>
> a trigger need to insert or update record at the other table in high traffic
> environments
> however, the deadlock happens; ie, a trigger running twice at the same time
> want to need to insert record at the table.
>
> i trid to change isolation as SERIALIZABLE or REPEATABLE , but i cannot
> solve the problem. i guess the "while loop" affects the result because i try
> to cancel the loop and execute smoothly.
>
> ? How to solve the deadlock???
> Thx
>

Avoid cursors in triggers. The cursor is unecessary and inefficient.
Your trigger code also blocks because you haven't referenced the
Inserted and Deleted tables. Those are virtual tables that tell you
what information was changed by the update operation.

Have you considered using views for this? To do it in a trigger looks
highly redundant to me.

If you need more help, please post a better description of what you are
doing. DDL and sample data also usually helps.

--
David Portas, SQL Server MVP

Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.

SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--