At the first look, it looks like one of the following will work:
1. Use MySQL's MERGE statement (that is, INSERT with ON DUPLICATE KEY). What really
happens, when the two transactions execute SELECT followed by an INSERT, there is no way
to hold off SELECT. The natural instinct is to make both operations (selecting followed
by inserting if it does not exist and locking if it does) the same thing. The solution
would then work like this:
- if the record does not exist, the first transaction to execute would insert it,
hopefully another transaction will wait;
- if the record does exist, the first transaction to execute would update it, thus locking
the record, the other transaction would then hopefully wait;
- the lock placed by the first transaction to execute will block the second transaction at
the very beginning, and it will then proceed after the first one commits or rolls back.
2. Put the transaction in a MySQL procedure and call it. Inside a procedure, write code
to handle unique constraint violation. The procedure would just start with attempting to
insert a row without bothering to check if it exists. If it succeeds, it will lock the
other transactions out. If it fails with unique constraint violation, do something
creative like sleeping, hanging on an update, or whatever, followed by starting over.
I did not mean to provide a solution but just to suggest some ideas about how this could
be resolved. Assuming it is on InnoDB tables without autocommit, the locking behavior
might be tricky, so any solution should be well tested.
On Jan 3, 2012, at 9:40 PM, KK Everest wrote:
> Hi all,
> Can anyone help me with the problem explained here please:
> Thanks in advance.