Solved

Would you avoid using database table triggers?

Posted on 2007-11-19
21
823 Views
Last Modified: 2012-05-11
In order to improve performance of my web application, I took some bulky database processing and moved it into database level procedures.  I also added an insert/update trigger on a few tables to automatically populate child tables when a record was added or modified.   Everything worked great and much much faster.

Now, however, I am in deadlock hell.  Tables are deadlocking right and left.   I don't understand why tables deadlock on insert statements (of tables with no triggers).

I'm investigating several paths, including having just upgraded to Coldfusion 8.    My question for this post is, are using table insert/update triggers and procedures more prone to deadlock?  Should I rip them out because they are tripping over each other.    The procedures have many insert and update statements have between two and six insert/update statements in them.
0
Comment
Question by:gdemaria
  • 5
  • 3
  • 3
  • +6
21 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 100 total points
ID: 20314964
> are using table insert/update triggers and procedures more prone to deadlock?
yes, if the triggers are not written "correctly".

especially if you have inefficient code, or multiple triggers, or triggers firing recursively etc...
also, indexes will be very important, because if there is any trigger running another insert/update/delete with full-table scans, well ...
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 20314971
Make certain there are no loops in your triggers. For example, inserting to table A inserts to B which inserts to C which updates A.

Also make sure that a trigger doesn't update the table on which it sits.  You can get some really goofy things happening when you do that.

Beyond that, I wouldn't suspect the triggers.
0
 
LVL 29

Assisted Solution

by:Göran Andersson
Göran Andersson earned 100 total points
ID: 20315089
Your deadlocks is most likely due to transactions that are locking the tables. If you don't use any transactions, these transactions comes from using the triggers.

If you don't specifically start a transaction, each query is a transaction in itself. However, when you are using a trigger to insert data, that trigger will run inside the transaction of the query that caused the trigger to run.

In efffect, the parent table is locked (entirelly or partially) while you insert the data in the child tables. If another query is accessing the child table first, then the parent table, they will each hold a lock to the table that the other is waiting for, and a deadlock occurs.

If you don't use triggers to populate the child tables, the transactions will only span a single query, and the deadlocks doesn't occur.

You might be able to avoid the deadlocks and still keep the transactions, but then you have to be very careful on how your triggers and procedures locks the tables, and make sure that the tables are not locked in such an order that a deadlock occurs.
0
 
LVL 6

Expert Comment

by:Spot_The_Cat
ID: 20315298
Are you saying that the procedures didn't always cause deadlocks and that this has recently started happening?

Because it's possible that the queries are now causing lock escalation.
0
 
LVL 39

Author Comment

by:gdemaria
ID: 20315308
Thanks all for your responses.

All my triggers do only two things:  update a few unused columns of the same record being inserted/updated and/or insert records into a child table.  

For example, if a new "product" is added the trigger will add a few warehouse records in the productLocations table.  It will also update the products table with fields such as lastSaleDate and Manufacturer's name.  

> If you don't specifically start a transaction, each query is a transaction in itself.
GreenGhost, how do you "specifically start a transaction" ?  

>  If another query is accessing the child table first, then the parent table, they will each hold a lock to the table that the other is waiting for, and a deadlock occurs.
Since the trigger is adding child records, I didn't think that someone else accessing this table would create a lock.  No one can be accessing these specific records in the child table because they don't yet exist.

My tables are very user-specific.  Think of a shopping cart where that cart's record and the items in that cart are only used by that one person.   Although this is not a shopping cart its a similar concept.    So none of my records should be fetched or modified by anyone else.  

I have seen a "hang"  (not sure if it was actually deadlocked or not) on a query as simple as this..  

select * from myProducts where cart_id = 123

(where cart_id is a foreign key to a parent table).

Thanks for any additional thoughts !   I'm really stuck !
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20315415
>All my triggers do only two things:  update a few unused columns of the same record being inserted/updated and/or insert records into a child table.  

please show the code of one such trigger.
0
 
LVL 29

Expert Comment

by:Göran Andersson
ID: 20315424
> how do you "specifically start a transaction" ?

For example:

Begin transaction
insert into Product (ProductName) values (@ProductName)
insert into ProductLocation (ProductId, Location) values (scope_identity(), 'dunno')
commit transaction

> Since the trigger is adding child records, I didn't think that someone else accessing
> this table would create a lock.  No one can be accessing these specific records in
> the child table because they don't yet exist.

They don't have to access the exact same records. A lock can affect a range of records or even an entire table.

> I have seen a "hang"  (not sure if it was actually deadlocked or not) on a query
> as simple as this..  
> select * from myProducts where cart_id = 123

Then the actual deadlock was probably caused by two other queries locking each other. If the table is locked you might not be able to read from it at all, even if the session trying to read the table isn't involved in the deadlock itself.
0
 
LVL 39

Author Comment

by:gdemaria
ID: 20315632
> Are you saying that the procedures didn't always cause deadlocks and that this has recently started happening?   Because it's possible that the queries are now causing lock escalation

Yes, this is new.   Its the test system, and there are only two changes.  (1) one other person has been reviewing occassionally (2) I upgraded to coldfusion 8

I've posted this separately on Coldfusion forums to see if anything may be up with things from that aspect.

Can you speak more about lock escalation ?


0
 
LVL 32

Assisted Solution

by:Daniel Wilson
Daniel Wilson earned 100 total points
ID: 20315645
>All my triggers do only two things:  update a few unused columns of the same record being inserted/updated and/or insert records into a child table.

You'll need to use the UPDATE(<ColumnName>) function or the COLUMNS_UPDATED() function to be sure you don't kick off recursion with that.

I have had enough bad experiences, including deadlocks, with triggers that update the same record that I avoid them.
0
 
LVL 39

Author Comment

by:gdemaria
ID: 20315676
GGhost,

 Is Begin Transaction Different from just "Begin" ?    Will it help reduce deadlock by keeping the tables open until the commit action perhaps?

 how you suggest I use the Begin transaction clause?
 For example, I could place it inside my triggers or I could place it in the code that updates my table (which in turn calls my triggers).   Same thing with the stored procedures..

 Thanks !!
0
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
LVL 6

Assisted Solution

by:Spot_The_Cat
Spot_The_Cat earned 100 total points
ID: 20315809
Lock escalation occurs when the estimated number of rows affected reaches a pre-determined amount of memory.

 It can be caused by locking more rows than originally expected.

 e.g. if you select a row with criteria that is not indexed then you will lock  all rows that need to be scanned.

 Select * from table where c1=22

If  c1  is not indexed you'll lock every row in the table if there's a lot of rows in the table then the lock mode could be escalated to table.

Consequently you need to check your indexing and your selects and updates/inserts to be sure how escalation could be affecting your procedures.
0
 
LVL 39

Author Comment

by:gdemaria
ID: 20315836
> You'll need to use the UPDATE(<ColumnName>) function or the COLUMNS_UPDATED() function to be sure you don't kick off recursion with that.

Danial, yes, I do use UPdate(colum)   as I only run the trigger if one or two columns have changed.  I'm sorry to have left out that information.

> I have had enough bad experiences, including deadlocks, with triggers that update the same record that I avoid them.

Perhaps I should lookup the data and make it part of the insert statement.  I just hate to do all that work when this used to work so smoothly !  

0
 
LVL 1

Expert Comment

by:ReconIT
ID: 20315898
Is is possible that he could be helped by using the "WITH (NOLOCK)" hint on his read queries to prevent the locking? I'm asking more so than making a recomendation. There are things to consider like if your data changes while the query is issued you might get bad data but if his read data is static it might be useful.
0
 
LVL 6

Expert Comment

by:Spot_The_Cat
ID: 20315973
ROWLOCK as a lock hint may also be useful - as yet we can't be sure that it's lock escalation at all but that may not help if the queries go through in-advertant scans. It would be useful to see the schema for the tables affected and a copy of the triggers
0
 
LVL 69

Assisted Solution

by:ScottPletcher
ScottPletcher earned 50 total points
ID: 20316240
I agree we need to see the specific code of the trigger(s) to really provide proper feedback.

In general, the format of the trigger should be roughly like below.  That is, you should check for no rows first and you should join to the inserted table to limit the rows to those that were affected by the INSERT/UPDATE that fired the trigger:


CREATE TRIGGER mainTable_Trigger1
ON mainTable
AFTER INSERT, UPDATE
AS
IF @@ROWCOUNT = 0
    RETURN
IF UPDATE(columnName1) OR UPDATE(columnName2)
BEGIN
    UPDATE mainTable
    SET ...
    FROM mainTable mt
    INNER JON inserted i ON mt.keyCol = i.keyCol
END --IF
0
 
LVL 29

Expert Comment

by:Göran Andersson
ID: 20316513
> Is Begin Transaction Different from just "Begin" ?

Yes. Just "begin" only determines the start of a code block, like in:

if (@@rowcount = 0) begin
  ...
end

> Will it help reduce deadlock by keeping the tables open until the commit action perhaps?

The problem is that you _are_ keepng the table locked during the transaction.

> how you suggest I use the Begin transaction clause?

I don't. You don't need to specify a transaction. You already have a transaction, and that's the problem.

> Perhaps I should lookup the data and make it part of the insert statement.

Yes, try that.
0
 
LVL 40

Assisted Solution

by:Vadim Rapp
Vadim Rapp earned 50 total points
ID: 20317098
Perhaps this will help you:

1. consider INSTEAD OF triggers. You said that one thing you do with your triggers is populate some other columns in the same row. Tjis can be done with INSTEAD OF trigger, so you will have only one update instead of two you have now.

For example, the user is

INSERT INTO TABLE1 (COL1) VALUES(1)

and you also want to populate COL2, for example with value 2. Currently, you probably do something like this:

UPDATE TABLE1 SET COL2=2
FROM TABLE1 join inserted on inserted.col1=table1.col1

with INSTEAD OF trigger it will be

INSERT INTO TABLE1 (col1,col2) SELECT COL1, 2 FROM INSERTED

so you basically substitute your own "improved" INSERT instead of the original one - rather than issue additional operation after the original INSERT has completed.

The same can be used for inserting rows into child tables:

insert into ChildTable(col1) select col1 from inserted
insert into ParentTable(col1) select col1 from inserted


2. Yoi can use Debugger in Query Analyzer and see step by step how your INSERT is working, and how each trigger is called one after another. For that, create a trivial stored procedure

create procedure test as insert into table1 (col1) select 3

and then right-click the procedure in query analyzer, in the left pane (object browser), and select "debug". Very soon you will see most likely some sort of loop - what others have described - when updating table a fires updating table b which in turn fires updating table a. Or actually, not even updating - even trying to read the table already being updated will result in deadlock, because you can't reliably read what has pending update, and the update is pending until the whole chain of triggers has completed.


0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 20317120
...to illustrate how this works:

for example, you are inserting into table A:

INSERT INTO TABLEA(COL1) SELECT 1000

On table A you have a trigger that fires another trigger that fires another trigger, and somewhere down the process you have

INSERT INTO someothertable SELECT MAX(COL1) from TableA

That's what will cause the deadlock: since the first INSERT is not complete, it's impossible to know what is maximum of COL1 - maybe this very INSERT is about to add the biggest value 1000. But INSERT is not complete yet, so calculation of MAX will be waiting when it's possible to calculate it, i.e. when INSERT is complete with all the triggers it has fires, i.e. it will be waiting for itself to complete- which is the deadlock.
0
 
LVL 6

Expert Comment

by:Rajesh_mj
ID: 20318052
Hi,

Just check some tips to avoid dead locks:

Deadlocks can occur when two connections update tables in opposite order. For example, one connection inserts into table "example1" first and then into "example2," while another connection inserts into table "example2" first and then into "example1" within a transaction.

Just check:
http://support.microsoft.com/kb/q169960/
http://www.sql-server-performance.com/tips/deadlocks_p1.aspx
0
 
LVL 39

Author Closing Comment

by:gdemaria
ID: 31410011
So many great responses it was hard to assign points - I hope I got everyone fairly.  Greatly appreciated!!!
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Viewers will learn how the fundamental information of how to create a table.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now