This is a small tip that can be used when you want to have separate processes to write to the same records in the database, without deadlocks. Example:
b:=StartTransaction;
UpdateDataInTable (b);
Commit (b);
If two processes try to do this at the same time, the second process will detect a possible deadlock on the update, and will wait until the first process completes its transaction. When the first transaction commits, the second raises a deadlock exception. Fortunately, there is a simple solution:
a:=StartTransaction;
UpdateDummyData (a);
b:=StartTransaction;
UpdateDataInTable (b);
Commit (b);
Rollback (a);
If two processes do this at the same time, the a transaction will make the second process wait until the first one completes. Because a is rolled back, this is purely a wait, and will not throw any exception. This way, the two b transactions will not be active at the same time.
2 comments:
Excuse me, but why this is better than a WAIT transaction? Or just an empty try/except block around the first code?
Please, correct me if I'm wrong. But in the first code:
* With NOWAIT (default): the first operation writes and the second one raises an exception.
* With WAIT: the first operation writes, the second one waits for the first one completion and then overwrites the data written by the first one.
With your solution (second code): the first one writes and the second one is ignored.
Apart discussions of whether exceptions should be swallowed or not, it appears to me that your solution is just a (costly) replacement for a try/except block, just using more resources on server.
The tip is about SQL statements like this:
update table set field=field+1
If you have two concurrent queries like this, one will throw an exception, no matter what. However, this tip can enforce serial handling of these, using WAIT, guaranteeing that both succeed.
The tip only works if you use WAIT transactions. As you correctly write, NOWAIT transaction will fail.
Post a Comment