After scouring the internet for information about Transactional Isolation levels, I understood that Row Locking in MySQL can be achieved by using either SELECT...FOR UPDATE or LOCK IN SHARE MODE and also that, a row locking query must execute after starting the transaction. So I came across some example illustrating the implementation of READ COMMITTED isolation level for two sessions like this:
--session 1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
BEGIN TRANSACTION;
SELECT FirstName FROM EmployeeInfo
WHERE EmpID = 1;
WAITFOR DELAY '00:00:05'
SELECT FirstName FROM EmployeeInfo
WHERE EmpID = 1;
ROLLBACK TRANSACTION;
The code for session 2 is like so:
--session 2
UPDATE EmployeeInfo
SET FirstName = 'Frank'
WHERE EmpID = 1;
In the code sample for session1, the transaction Isolation level and the beginning of a transaction are explicit. In a certain blog I read that
- UPDATE statements implicitly create READ locks on affected rows in a transaction.
So does this means that I can safely omit
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;from session 1 and still get the same results.In other words how true is the statement UPDATE statements implicitly create READ locks on affected rows in a transaction.
