MySQL Forums
Forum List  »  InnoDB

does repeatable read prevent lost update with pessimistic locking?
Posted by: Tapio Niemela
Date: November 24, 2005 01:26PM


Could someone help me, please. I've read from somewhere that transaction isolation level REPEATABLE READ and also SERIALIZABLE can prevent LOST UPDATE from happening when using pessimistic locking, like SQL Server does. Is this true? If so how is this done? Is solution following: Does pessimistic locking prevent a transaction reading same row as another transaction? I would be thankful for help.

Other funny thing I discovered is that when using FOR UPDATE in every SELECT-query one can prevent dirty reads even if selected isolation level is READ UNCOMMITTED. At least with MySQL (with InnoDB-engine). If first transaction SELECTS a row FOR UPDATE then another transaction can't SELECT a row FOR UPDATE before first transaction is completed (commit/rollback). Second transaction doesn't return any values with SELECT-query before first is completed. It just waits or is canceled if enough time has passed by ("time-outed"). Does anyone know what kind of problems can follow if dirty read is prevented with READ UNCOMMITTED and SELECT FOR UPDATE in every SELECT-query?

Options: ReplyQuote

Written By
does repeatable read prevent lost update with pessimistic locking?
November 24, 2005 01:26PM

Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.