If you're getting a deadlock error, chances are that it has to do with the
order that your queries are going. If you have one page with a transaction
that starts with an insert to Table A and then updates all the rows in Table
B while you have another page with a transaction that inserts into Table B
and then updates all the rows in Table A you can have a deadlock.
Example:
Page 1
BEGIN TRANSACTION
INSERT INTO TableA (field1,field2) VALUES ('value1', 'value2')
UPDATE TableB SET field1 = 5
COMMIT TRANSACTION
Page 2
BEGIN TRANSACTION
INSERT INTO TableB (field1,field2) VALUES ('value1', 'value2')
UPDATE TableA SET field1 = 5
COMMIT TRANSACTION
If both pages run at the same time, page 1 will lock TableA and page 2 will
lock TableB. Neither can let go of the lock until the transaction is
complete, but they can't get to other table they need until the other page
releases its lock.
The idea is to put all your tables into the same order so if page 1 works on
TableA and then TableB, then page 2 should work on TableA and then TableB as
well -- not in reverse order.
Does that help?
Ben Johnson
Hostworks, Inc.
______________________________________________________________________
Structure your ColdFusion code with Fusebox. Get the official book at
http://www.fusionauthority.com/bkinfo.cfm
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
