MySQL Forums :: Transactions :: mysql innodb transaction concurrency

Hi Peter,

Sorry to bother you. I am confused :(

I have read this page.
http://dev.mysql.com/doc/refman/5.5/en/innodb-locking-reads.html
summary: "If you query data and then insert or update related data within the same transaction, the regular SELECT statement does not give enough protection. Other transactions can update or delete the same rows you just queried."

Does it mean below code wont work?
$user_purchased = SELECT count(*) FROM purchases where user_id = 1; (This might return many records)
if (condition) {
UPDATE table SET column = "value" WHERE (...)
... other logic (including INSERT some data) ...
}

so I have to use
$user_purchased = SELECT count(*) FROM purchases where user_id = 1 for update; (This might return many records)
if (condition) {
UPDATE table SET column = "value" WHERE (...)
... other logic (including INSERT some data) ...
}

But still, I have a doubt, will it stop allowing users to insert the records? or just locks whatever I just queried in select ...for update. If so, Please could shed some light on this?

Could you please clarify this? I struck in the middle. Don't know which to follow.

Thanks for your valuable time.



Edited 2 time(s). Last edit at 08/11/2011 12:38AM by venu gopal.