5 ms·
InnoDB uses MVCC, so the only operations that respect the SELECT .. FOR UPDATE queries are updates. As long as you use transactions, I wouldn't say this is a c
by morgo 13y ago
InnoDB uses MVCC, so the only operations that respect the SELECT .. FOR UPDATE queries are updates. As long as you use transactions, I wouldn't say this is a common application pattern.
It was good for the article to mention gap locking/next key locking - a lot of users are surprised by it. The only comment I would make, is that it not required when using Row-based-replication:
http://www.tocker.ca/2013/09/04/row-based-replication.html http://www.tocker.ca/2013/09/04/row-based-replication.html
I also have a 35min video as well which describes locking:
http://www.tocker.ca/2013/09/19/locking-and-concurrency-control.html http://www.tocker.ca/2013/09/19/locking-and-concurrency-cont...
- Roboprog 13y agoSince MySQL + InnoDB now uses MultiVersion Concurrency Control, why does it need these locks in the article??? (MVCC should allow "speculative", isolated, changes which fail and get rolled back in case of a conflict)
- mjb 13y agoAs I replied to your comment further down, you (and the article) are confusing the implementation's use of locks to ensure atomicity and isolation with the applications use of locks to ensure consistency. These things are different.
- kozlovsky 13y agoWithout locks lost updates are possible because default isolation level is not SERIALIZABLE
- morgo 13y agoWhat @kozlovsky said :) Only advice I would add, is SELECT .. FOR UPDATE doesn't (and shouldn't) use MVCC, so it guarantees you the most recent version of the row. In my comment I said the use case for this feature is not typical, but I can list the case: If you needed to read the most recent value, apply logic (external to the DB) and then write back a new value you will likely want serializable to prevent lost-updates. A hypothetical (but poor example) might be a stats counter where you read the value, add one, then save it back.