does repeatable read prevent lost update with pessimistic locking?

[Tapio Niemela]

hello,

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?