Hello!

I have a table with products(id, name, count) and another table with
sold_products(id, name, count, invoiceno.. etc). sold_products has a
trigger that fires on table insertions. It updates the count of products
in products table. Trigger looks like this:
Update products p set p.count = p.count - sold_products.count where p.id
= sold_products.id;

This works until two different transactions try to insert to table1.
Then a deadlock occurs.

(When this first occurred i had a foreign key sold_products on name
pointing to products name. The error message (before i dropped the fk)
was that there was a foreign key violation [fk name] on insert. This was
nonsense so id dropped the key and the message changed to 'deadlock'.)

Currently i solved this by committing the transaction immidiately, but i
do not like this (there was supposed to be other tables involved in the
same transaction too).

Can't the p.count be involved in transactions? I can vaguely understand
what the problem is.. maybe a new function like pascals inc(value,
count) would solve this?

Or another approach to maintaining product amounts maybe?


BR,
Lauri