Hola,
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.
Peace
Karen.
On Jan 3, 2012, at 9:40 PM, KK Everest wrote:
Hi all,
Can anyone help me with the problem explained here please:
http://www.reddit.com/r/mysql/comments/o256m/a_transactional_problem/
Thanks in advance.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql
