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
-
Use (NOLOCK) on all SELECT...
select item, quantity from invset (NOLOCK) where sku = @.sku
Adamus
|||
Is this the trigger code? It doesn't seem right. For one, you are looking at rows in the base table and not just the rows affected by the DML operation in the trigger. And you don't need a cursor logic which hurts performance and creates more locking issues since the trigger is in a transaction. Also, the query for the cursor doesn't have any order by so you could process the same rows in different order from different transactions and end up deadlocked. Can you post a simple schema for the base table and the additional table that you need to maintain including the columns? This will help to suggest a simpler trigger code.
|||Using NOLOCK will not prevent all deadlocks. You have to first determine the cause of the deadlock and the resource in contention. With the trigger logic, it will in fact result in wrong results because you will be considering rows from transactions that can be potentially rolled back. It is easy to abuse NOLOCK hint without realizing the impact.|||
hi alex,
try removing that cursor inside the trigger replace it
with multiple update/delete from inserted and deleted tables
regards
|||
Thank to your help
there are trigger purpose and table structure
Alex
** purpose task
customer buys goods (parent) at shop
a parent goods may be two child goods or a set of child goods
and i need to update inventory balance at the realtime
** details task
table [itmhist] as transaction record installed trigger.
trigger use parent goods to find child goods at table [invset]
update the inventory balance of child goods at table [invbal]
/* trigger was installed at this table */
/* sku mean goods id */
CREATE TABLE [ITMHIST] (
[SHOP] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[TXDATE] [smalldatetime] NOT NULL ,
[REG] [smallint] NOT NULL ,
[TX] [smallint] NOT NULL ,
[SEQ] [smallint] NOT NULL ,
[SKU] [varchar] (9) ,
[QTY] [int] NULL ,
CONSTRAINT [PK_ITMHIST] PRIMARY KEY CLUSTERED
(
[SHOP],
[TXDATE],
[REG],
[TX],
[SEQ]
) ON [PRIMARY]
) ON [PRIMARY]
GO
/* cursor and while loop table
purpose - use parent goods to find the child goods
sku is parent goods
item is child goods
*/
CREATE TABLE [INVSET] (
[SKU] [varchar] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
[ITEM] [varchar] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
[QUANTITY] [int] NULL ,
CONSTRAINT [PK_INVSET] PRIMARY KEY CLUSTERED
(
[SKU],
[ITEM]
) ON [PRIMARY]
) ON [PRIMARY]
GO
/* update table
purpose - update realtime inventory balance
slsqtyyes was yesterday sold goods
slsqtynow was today sold goods
slsqtynxt was tommorrow sold goods
*/
CREATE TABLE [INVBAL] (
[SHOP] [varchar] (7) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
[SKU] [varchar] (9) COLLATE Chinese_Taiwan_Stroke_CI_AS NOT NULL ,
[OPENDATE] [datetime] NULL ,
[SLSQTYYES] [numeric](10, 0) NULL CONSTRAINT [DF_INVBAL_SLSQTYYES] DEFAULT (0),
[SLSQTYNOW] [numeric](10, 0) NULL CONSTRAINT [DF_INVBAL_SLSQTYNOW] DEFAULT (0),
[SLSQTYNXT] [numeric](10, 0) NULL CONSTRAINT [DF_INVBAL_SLSQTY] DEFAULT (0),
CONSTRAINT [PK_INVBAL] PRIMARY KEY CLUSTERED
(
[SHOP],
[SKU]
) ON [PRIMARY]
) ON [PRIMARY]
GO
|||
 Umachandar Jayachandran - MS wrote: |
|
| Using NOLOCK will not prevent all deadlocks. You have to first determine the cause of the deadlock and the resource in contention. With the trigger logic, it will in fact result in wrong results because you will be considering rows from transactions that can be potentially rolled back. It is easy to abuse NOLOCK hint without realizing the impact. |
Well considering I focused on (NOLOCKS) with SELECT's only, I'm confused on how you incorporated transactions into this. Do you use transactions on SELECTs?
Also, the trigger logic is fired after the events. Protocol suggest rollbacks have already been completed prior to the trigger or exist inside the trigger. In either event, the results will be accurate.
It is easy to be lead astray on tangeants when you're uncertain of the desired resultset.
Adamus
|||
| Umachandar Jayachandran - MS wrote: |
|
| Is this the trigger code? It doesn't seem right. For one, you are looking at rows in the base table and not just the rows affected by the DML operation in the trigger. And you don't need a cursor logic which hurts performance and creates more locking issues since the trigger is in a transaction. |
Could you explain more on how a cursor causes locking issues any more than an alternative approach?
| Umachandar Jayachandran - MS wrote: |
|
| Also, the query for the cursor doesn't have any order by so you could process the same rows in different order from different transactions and end up deadlocked. |
Also, can you explain more on how ordering/sorting the records would cause records to be read twice in the cursor?
| Umachandar Jayachandran - MS wrote: |
|
| Can you post a simple schema for the base table and the additional table that you need to maintain including the columns? This will help to suggest a simpler trigger code. |
How much simpler could it be?
Adamus
|||
Please read the trigger topic in SQL Server Books Online to start with. Trigger are always in an implicit transaction so whatever code you write executes within a transaction. And also read about read uncommitted transaction isolation level in Books Online. Read uncommitted means you can look at currently executing transactions in the system (you are not isolated from changes happening in the system).
See below pseudo-code showing interleaved operations between 2 connections:
begin tran connection#1
insert into table values(1) connection#1
declare cursor ... select * from table (nolock) connection #2 inside trigger will get row above
fetch .... -- connection #2, now you have read row from connection #1
rollback connection #1
... some other logic in connection #2, now you are doing something with a row that doesn't exist!
>> Also, the trigger logic is fired after the events. Protocol suggest rollbacks have already been completed
>> prior to the trigger or exist inside the trigger. In either event, the results will be accurate
You need to read about how triggers work and what each transaction isolation level means (including NOLOCK hint). See topics below:
-- trigger related topics:
http://msdn2.microsoft.com/en-us/library/ms189799.aspx
http://msdn2.microsoft.com/en-us/library/ms178110.aspx
http://msdn2.microsoft.com/en-us/library/ms189261.aspx
-- isolation level related topics:
http://msdn2.microsoft.com/en-us/library/ms173763.aspx
http://msdn2.microsoft.com/en-us/library/ms173763.aspx
http://msdn2.microsoft.com/en-us/library/ms189122.aspx
http://msdn2.microsoft.com/en-us/library/ms190805.aspx
|||
>> Could you explain more on how a cursor causes locking issues any more than an alternative approach?
Depending on the type of cursor, the execution strategy is different. An insensitive cursor makes a copy of the rows in temporary table so it is not an atomic operation - the results are persisted upon OPEN cursor. Keyset cursor uses keys persisted in temporary table but every fetch incurs SELECT overhead. Dynamic cursor is susceptible to other changes happening in the system and depending on isolation level you may end up reading rows that are rolled back. See the topics in my other post to understand how isolation level works. If you use a SELECT statement then it is easy to understand the locking issues if you know the isolation level for starters.
>> Also, can you explain more on how ordering/sorting the records would cause records to be read twice in the
>> cursor?
I didn't say records. I said rows so there is a difference. And I didn't say that rows are read twice. I said that rows could be processed in different order leading to deadlock. The user's code had a SELECT against the base table so if two concurrent transactions fired the trigger both will be looking at the base table. And depending on the execution plan the order of the rows might differ (see how SELECT statement works in Books Online and the ordering guarantees). So connection #1 may process rows in different order than connection #2.
>> How much simpler could it be?
As I can see from your responses it has not really helped the user in any way. It is best to look at sample DDL and data for newsgroup problems to give the correct solution. It is easy to talk vaguely or point in multiple directions.
|||
Thanks for posting the sample schema. Some more explanation on the rules would have been helpful. Anyway, I created some sample trigger code based on what I could understand from your comments in the script above.
-- Insert sample inventory data set:
INSERT INTO [INVSET] ([SKU], [ITEM], [QUANTITY]) VALUES( 'p1', 'c1', 10 )
INSERT INTO [INVSET] ([SKU], [ITEM], [QUANTITY]) VALUES( 'p1', 'c2', 5 )
INSERT INTO [INVSET] ([SKU], [ITEM], [QUANTITY]) VALUES( 'p2', 'c2', 5 )
INSERT INTO [INVSET] ([SKU], [ITEM], [QUANTITY]) VALUES( 'p2', 'c3', 20 )
go
-- Trigger to maintain inventory balance upon insert:
create trigger insert_ITMHIST on ITMHIST
after insert
as
begin
set transaction isolation level serializable
-- Update existing inventory items first:
update [INVBAL]
set [SLSQTYNOW] = [SLSQTYNOW] + CASE WHEN i3.[TXDATE] <= [INVBAL].[OPENDATE] THEN i3.[QUANTITY] * i3.[QTY] ELSE 0 END
, [SLSQTYNXT] = [SLSQTYNXT] + CASE WHEN i3.[TXDATE] > [INVBAL].[OPENDATE] THEN i3.[QUANTITY] * i3.[QTY] ELSE 0 END
from (
select i.[SHOP], i.[TXDATE], i2.[ITEM], i.[QTY], i2.[QUANTITY]
from inserted as i
join [INVSET] as i2
on i2.[SKU] = i.[SKU]
) as i3
where i3.[SHOP] = [INVBAL].[SHOP] and i3.[ITEM] = [INVBAL].[SKU]
-- add new inventorty items next:
insert into [INVBAL] ([SHOP], [SKU], [OPENDATE], [SLSQTYNOW], [SLSQTYYES], [SLSQTYNXT])
select i.[SHOP], i2.[ITEM], i.[TXDATE], i.[QTY] * i2.[QUANTITY], 0, 0
from inserted as i
join [INVSET] as i2
on i2.[SKU] = i.[SKU]
where not exists(select * from [INVBAL] as i3
where i3.[SHOP] = i.[SHOP] and i3.[SKU] = i2.[ITEM])
end
go
-- view approach:
create view INVBAL_RT
as
select ih.[SHOP], i.[ITEM] as SKU, SUM( i.[QUANTITY] * ih.[QTY] ) as QTY
from dbo.[ITMHIST] as ih
join dbo.[INVSET] as i
on i.[SKU] = ih.[SKU]
group by ih.[SHOP], i.[ITEM]
go
begin tran
-- Try some test inserts:
insert into ITMHIST values('a', '20060814', 1, 1, 1, 'p1', 2)
select * from INVBAL
select * from INVBAL_RT
insert into ITMHIST values('a', '20060814', 1, 2, 1, 'p1', 2)
select * from INVBAL
select * from INVBAL_RT
insert into ITMHIST values('a', '20060815', 1, 1, 1, 'p2', 1)
select * from INVBAL
select * from INVBAL_RT
rollback
go
-- cleanup tables:
drop table INVSET, INVBAL, ITMHIST
drop view INVBAL_RT
go
The trigger code above should give you an idea of how to simplify your existing logic. I also used serializable isolation level inside trigger code. Alternatively, you can just create a view to show the real-time inventory data instead of persisting in the table. You could index the view for performance although it has some overhead in terms of maintainenence during DML operations on the base tables. But you could compare it with the trigger logic.
Btw, if you want to retain your existing trigger logic assuming everything works except for the deadlock you can use the link below to troubleshoot the deadlock issue. SQL Server 2005 has new trace flag that provides richer output for deadlock detection including the conflicting statements.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/trblsql/tr_servdatabse_5xrn.asp
|||
hi all,
I collect your suggestions and modify my program.
it seems ok, but i am not sure.
there is my final product. please give me some advise if you find out risk in my code.
my main modification is stroke out "local static" at cursor loop.
add "no lock" at each select, add serializable in trigger, "set nocount on" at the beginning and "set nocount off" at the end.
thank a lot
alex
-
alter TRIGGER tr_SKYTSP_INVBAL
ON dbo.ITMHIST
FOR INSERT
AS
SET NOCOUNT ON
set transaction isolation level serializable
DECLARE @.shop varchar(7), @.txdate smalldatetime, @.sku varchar(9),
@.setsku varchar(1), @.invsetitem varchar(9), @.invsetqty smallint,
@.opendate smalldatetime, @.itmtxqty smallint, @.balsku varchar(9), @.bizdate smalldatetime
SELECT @.shop = SHOP, @.sku = SKU, @.txdate = TXDATE, @.itmtxqty = qty From Inserted
SELECT @.setsku = setsku from INVMST (NOLOCK) where SKU = @.sku
select @.bizDate = CONVERT(char(10), BUSSINDATE,101) FROM chain.dbo.CNTRL (NOLOCK)
if (@.sku is not null)
begin
if (@.setsku = 1)
begin
declare setskuCursor cursor
for select item, quantity from invset (NOLOCK) where sku = @.sku
open setskuCursor
fetch next from setskuCursor into @.invsetitem, @.invsetqty
while @.@.fetch_status = 0
begin
set @.balsku = '000000'
select @.balsku = sku, @.opendate = opendate from invbal (NOLOCK) where sku = @.invsetitem
if (@.balsku <> '000000')
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
INSERT INTO INVBAL (SHOP, SKU, OPENDATE, SLSQTYNOW) VALUES (@.shop, @.invsetitem, @.bizdate, @.itmtxqty * @.invsetqty)
fetch next from setskuCursor into @.invsetitem, @.invsetqty
end
close setskuCursor
deallocate setskuCursor
end
else
begin
set @.balsku = '000000'
select @.balsku = sku, @.opendate = opendate from invbal (NOLOCK) where sku = @.sku
if (@.balsku <> '000000')
begin
if @.txdate <= @.opendate
update invbal set slsqtynow = slsqtynow + @.itmtxqty where sku = @.sku
if @.txdate > @.opendate
update invbal set slsqtynxt = slsqtynxt + @.itmtxqty where sku = @.sku
end
else
INSERT INTO INVBAL (SHOP, SKU, OPENDATE, SLSQTYNOW) VALUES (@.shop, @.sku, @.bizdate, @.itmtxqty)
end
end
SET NOCOUNT off
Go
|||
I am not sure why you added NOLOCK in your SELECT statements. As I pointed out multiple times in this thread, it will produce wrong results based on your concurrency and will lead to other problems. There are even query execution plans where you will get duplicates with NOLOCK i.e., you will end up reading the same row multiple times within the same query. Reading same row multiple times can happen when a split happens on the table and the row gets moved to a different location & the query ends up reading it again. This is perfectly fine for semantics of a dirty read which is what NOLOCK implies. So your real-time inventory balance will be incorrect. NOLOCK means no transactional consistency in the data that you read. If it is fine for you to have insufficient or wrong real-time inventory balance then you can use NOLOCK everywhere. Note that this may still not solve the deadlock issue because that could be due to parallelism in one of your queries for example. You may have to troubleshoot that next.
Your logic still doesn't take care of multiple rows inserted into the table. If that happens the behavior of trigger will be unpredictable i.e., you will not know which one of the inserted rows will be updated in the balance. Please take a look at the code that I posted. Try to modify and use that. Starting with the simple inventory balance update scenario of say inserting multiple SKUs will help understand the code & then you can incorporate more logic. It handles multiple rows being inserted/updated. It uses set-based operations and it is less resource intensive especially when executed within a trigger code which is in a transaction always.
|||
| Umachandar Jayachandran - MS wrote: |
|
| Please read the trigger topic in SQL Server Books Online to start with. Trigger are always in an implicit transaction so whatever code you write executes within a transaction. And also read about read uncommitted transaction isolation level in Books Online. Read uncommitted means you can look at currently executing transactions in the system (you are not isolated from changes happening in the system). See below pseudo-code showing interleaved operations between 2 connections: begin tran connection#1 insert into table values(1) connection#1 declare cursor ... select * from table (nolock) connection #2 inside trigger will get row above fetch .... -- connection #2, now you have read row from connection #1 rollback connection #1 ... some other logic in connection #2, now you are doing something with a row that doesn't exist! >> Also, the trigger logic is fired after the events. Protocol suggest rollbacks have already been completed >> prior to the trigger or exist inside the trigger. In either event, the results will be accurate You need to read about how triggers work and what each transaction isolation level means (including NOLOCK hint). See topics below: -- trigger related topics: http://msdn2.microsoft.com/en-us/library/ms189799.aspx http://msdn2.microsoft.com/en-us/library/ms178110.aspx http://msdn2.microsoft.com/en-us/library/ms189261.aspx -- isolation level related topics: http://msdn2.microsoft.com/en-us/library/ms173763.aspx http://msdn2.microsoft.com/en-us/library/ms173763.aspx http://msdn2.microsoft.com/en-us/library/ms189122.aspx http://msdn2.microsoft.com/en-us/library/ms190805.aspx |
Um "Instead of" triggers? Ever heard of them?|||
So how will instead of trigger solve this particular problem? How does it relate to your previous statement? Instead of trigger is also in a transaction though it fires in lieu of the DML action. Check for @.@.TRANCOUNT value inside instead of trigger code and see. You will have to anyway issue the same or similar set of DML actions against the base tables. Instead of triggers do not magically solve concurrency or locking issues. The main purpose of INSTEAD OF triggers or motivation behind adding it in the ANSI SQL standards was to allow DML operations on non-updateable views.
As I said before, you need to read about triggers (after and instead of) and how they work transactionally especially when different isolation levels are in effect. Isolation levels by themselves are a different beast and the topics I posted will illuminate you on those areas. Jim Gray's whitepaper on transactions is also a good start for this one.
0x50871568)