Link to home
Start Free TrialLog in
Avatar of gdemaria
gdemariaFlag for United States of America

asked on

Would you avoid using database table triggers?

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.
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
Avatar of gdemaria

ASKER

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 !
>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.
> 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.
> 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 ?


SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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 !!
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
> 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 !  

Avatar of ReconIT
ReconIT

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.
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
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
> 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.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
...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.
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
So many great responses it was hard to assign points - I hope I got everyone fairly.  Greatly appreciated!!!