MySQL transactions question

Question

I have been reading about transactions in MySQL, but I could not understand one issue. Transactions are a way to ensure that in a block of statements, either all are executed or none of them. However, Does a transaction ensures the "locking" of rows that are part of it? That is, say I have the following statements (pseudo):

1) START TRANSACTION
2) SELECT row1 FROM table
3) UPDATE table SET row1='new value'
4) COMMIT

Now, say a user tried to access row1 at the time where the system was at line 3 above.. Will that user have access to the row (and then he can see the old value of row1), or will he have to wait until the transaction is finished, and only then the row will be fetched with the new value.

Thanks! Joel

Answer 1

It depends. See the MySQL SELECT statement syntax, specifically FOR UPDATE and LOCK IN SHARE MODE.

"If you use FOR UPDATE with a storage engine that uses page or row locks, rows examined by the query are write-locked until the end of the current transaction. Using LOCK IN SHARE MODE sets a shared lock that permits other transactions to read the examined rows but not to update or delete them."

Answer 2

No, transactions and isolation are two separate concepts.

Five levels of isolation:

1. None
2. Read committed means that dirty reads are prevented; non-repeatable reads and phantom reads can occur.
3. Read uncommitted means that dirty reads, non-repeatable reads and phantom reads can occur.
4. Repeatable read means that dirty reads and non-repeatable reads are prevented; phantom reads can occur.
5. Serializable means that dirty reads, non-repeatable reads and phantom reads are prevented.

Answer 3

when you start a transaction the changes you made become visible only after the transaction has been committed. It is more like an isolaton than a lock.

HTH