Which Transaction Isolation Level to use

[John Noble]

Hi folks,

I have been trying out various transaction levels tryng to find something that will meet my needs.

I am having problems with locks doing the folloing transactions...

Session 1
START TRANSACTION;
UPDATE customers SET balance = balance + 10 WHERE customerID = "ABC";
UPDATE customers SET balance = balance - 10 WHERE customerID = "DEF";
UPDATE customers SET balance = balance + 10 WHERE customerID = "GHI";
UPDATE customers SET balance = balance - 10 WHERE customerID = "JKL";
COMMIT;

Session 2
START TRANSACTION;
UPDATE customers SET balance = balance + 10 WHERE customerID = "JKL";
UPDATE customers SET balance = balance - 10 WHERE customerID = "GHI";
UPDATE customers SET balance = balance + 10 WHERE customerID = "DEF";
UPDATE customers SET balance = balance - 10 WHERE customerID = "ABC";
COMMIT;

Supposing the 2 transactions above started at the same time. Session 2 will already have a lock on customerID "GHI" so Session 1 cant access it until Session 2 is committed. But Session 2 cant complete because Session 1 already has a lock on customerID "DEF"

The default isolation type caused timeouts. So I tried setting isolation to SERIALIZABLE but I did not get the expected results.

Is there away round this?

John