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