DeadLock - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

DeadLock

Author

Topic

egemen_ates
Yak Posting Veteran

76 Posts

Posted - 2012-06-22 : 05:59:00

I create trigger my trigger show belown as ,some time update statement given deadlock for orders table.how can i solve this case

CREATE TRIGGER [dbo].[add_orders] ON [dbo].[ORDERS] INSTEAD OF INSERT,UPDATE,DELETE
AS
BEGIN
SET NOCOUNT ON

SET NOCOUNT ON;
IF EXISTS(SELECT TOP 1 ORDER_ID FROM INSERTED)
BEGIN
IF EXISTS(SELECT TOP 1 ORDER_ID FROM DELETED)--UPDATE
BEGIN
DELETE FROM workcube_cf_2012_1.dbo.ORDERS_VIRTUAL WHERE ORDER_ID = (SELECT ORDER_ID FROM INSERTED)
INSERT INTO
dbo.ORDERS_VIRTUAL
(
ORDER_ID,
ORDER_STATUS,
RESERVED,
DELIVER_DEPT_ID,
LOCATION_ID
)
SELECT
ORDER_ID,
ORDER_STATUS,
RESERVED,
DELIVER_DEPT_ID,
LOCATION_ID
FROM
INSERTED
END
ELSE--INSERT
BEGIN
INSERT INTO
dbo.ORDERS_VIRTUAL
(
ORDER_ID,
ORDER_STATUS,
RESERVED,
DELIVER_DEPT_ID,
LOCATION_ID
)
SELECT
ORDER_ID,
ORDER_STATUS,
RESERVED,
DELIVER_DEPT_ID,
LOCATION_ID
FROM
INSERTED
END
END
ELSE
BEGIN
IF EXISTS(SELECT TOP 1 * FROM DELETED)--DELETE
BEGIN
DELETE FROM dbo.ORDERS_VIRTUAL WHERE ORDER_ID = (SELECT ORDER_ID FROM DELETED)
END
END
SET NOCOUNT OFF
END;

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2012-06-22 : 16:28:21

Fixing deadlock is time consumming, and we need more info than that

Could you post the trace file of that deadlock?

Also, have a quick question.

For Update, you delete rows from table ORDERS_VIRTUAL WHERE ORDER_ID = (SELECT ORDER_ID FROM INSERTED). Then you insert into the same table with data, and the same ORDER_ID ?

If so, why don't you just update the table. Do I miss something.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources