SQL – on which context do SELECT…FOR UPDATE and UPDATE work?

I would like to have a question about data contexts on which do the transaction operate with different commands.

Are there any differences in SELECT…FOR UPDATE and UPDATE in terms of contexts of data?

What I mean by this: if I run the following transaction (pseudo-code):

begin transaction;
select user for update where id=1;
update user set name="TOM" where id=1;
commit;

What this actually does is that it locks the user in exclusive mode and so other transactions can’t access it in any way, right?

When the transaction commits, the data is written and the lock is released.

But then what about these transactions:

begin transaction;
select user for update where id=1;
update user set name="TOM" where id=1;
select user where id=1; //BREAKPOINT
commit;

and T2:

begin transaction;
update user set name="TOM" where id=1;
select user where id=1; //BREAKPOINT
commit;

I believe that SELECTs at the BREAKPOINTs will return the updated user. If that’s so, why?

The UPDATE and SELECT…FOR UPDATE both lock the user in exclusive mode in the “real” table (table with the data committed from other transactions), while the actual content of the update (name=”TOM”) is visible only to the transaction manipulating it until the transaction is committed?

Does this mean that every transaction works in its own “context” which holds the changes made by its subsequent commands?

Answer

In this case transactions cannot run simultaneously – first transaction, which would be run, will lock record with id = 1.

They do not have two their own states – only one transaction will work and it should see own changes

Advertisement

Answer