This page is part of the book SQL-99 Complete, Really, by Peter Gulutzan & Trudy Pelzer. The authors have graciously allowed us to reproduce the contents of the book here. Because the book is about the SQL-99 standard, the contents of this and other pages in the book may not directly apply to MariaDB. Use the navigation bar to navigate the book.
There are several ways to control concurrency without locking. The most common ones can be grouped together as the "optimistic assumption" ways, and the most common of those ways is timestamping. With timestamping, there are no locks but there are two situations which cause transaction failure:
- If a younger transaction has "read" the row, then an attempt by an older transaction to "change" that row will fail.
- If a younger transaction has "changed" the row, then an attempt by an older transaction to "read" that row will fail.
The general effect of these rules is that concurrency is high, but failure is frequent. Indeed, it is quite possible that a transaction will fail many times. But what the heck, one can put the transaction in a loop and keep retrying until it goes through.
Some DBMSs enhance the concurrency further by actually "reading" a row which has been changed by another transaction, and deciding whether the change is significant. For example, it often happens that the same Column is being updated so that it has the same value for both transactions. In that case, there may be no need to abort.
Most optimistic concurrency mechanisms are not particularly good for detecting Non-Repeatable Reads or Phantoms.