I am trying to understand how shared and exclusive locks work, and their effect on repeatable read isolation level.
Basically, what I am trying to do is this:
I start transaction 1:
BEGIN ISOLATION LEVEL REPEATABLE READ;
Then I start the second transaction 2:
BEGIN ISOLATION LEVEL REPEATABLE READ;
Then, in the first transaction, I run
SELECT * FROM students WHERE id = 1;
And then in second transaction, I run
update students set cnt = 1 where id = 1; <-- I expect it to be locked and wait
I expect that the second transaction goes into pending mode waiting until the shared lock acquired by the first transaction will be released when the first transaction commits, but in reality, the second transaction successfully updates the data and get committed. Is there something I am missing ?