Transaction and Select For Update help

Hello,

Can someone tell me the differences between the following, besides the fact that with the transaction you can rollback if something bad happens? Does the transaction prevent another client from reading/writing the count until its done?

1. Transaction way:
start transaction;
select count from mutable where id = 2;
// calculate new value of count, which ends up being 100
update mytable set count = 100 where id = 2 limit 1;
commit;


2. Select for Update way:
select count from mutable where id = 2 for update;
// calculate new value of count, which ends up being 100
update mytable set count = 100 where id = 2 limit 1;

Thanks!

Actually the second option you have written is flawed. A SELECT ... FOR UPDATE doesn't work without a transaction. So basically you have written two transactional ways where you in one doesn't lock the rows in the SELECT and in the second you do.

So your two options should have looked like:

And the difference in the default transaction mode REPEATABLE READ is that the second way will lock the record earlier (already in the select and not in the update).

But logically it doesn't matter if they both are trying to update the same value. Since in both ways the second thread will end up waiting for the first thread to commit and then the second transaction will fail and roll back.

But if you change transaction mode some of this behavior might change.