There is a final technique for increasing concurrency: Multi-Version Concurrency Control (MVCC). Often referred to simply as versioning, MVCC is used by Oracle, by PostgreSQL, and by MySQL’s InnoDB storage engine. MVCC can be thought of as a new twist on row-level locking. It has the added benefit of allowing nonlocking reads while still locking the necessary records only during write operations. Some of MVCC’s other properties will be of particular interest when we look at transactions in the next section. So how does this scheme work? Conceptually, any query against a table will actually see a snapshot of the data as it existed at the time the query began—no matter how long it takes to execute. If you’ve never experienced this before, it may sound a little crazy. But give it a chance. In a versioning system, each row has two additional, hidden values associated with it. These values represent when the row was created and when it was expired (or deleted). Rather than storing the actual time at which these events occur, the database stores the version number at the time each event occurred. The database version (or system version) is a number that increments each time a query* begins. We’ll call these two values the creation id and the deletion id. * That’s not quite true. As you’ll see when we start talking about transactions later, the version number is incremented for each transaction rather than each query. Under MVCC, a final duty of the database server is to keep track of all the running queries (with their associated version numbers). Let’s see how this applies to particular operations: SELECT When records are selected from a table, the server must examine each row to ensure that it meets several criteria:
Rows that pass all of these tests may be returned as the result of the query. INSERT When a row is added to a table, the database server records the current version number along with the new row, using it as the row’s creation id. DELETE To delete a row, the database server records the current version number as the row’s deletion id. UPDATE When a row is modified, the database server writes a new copy of the row, using the version number as the new row’s creation id. It also writes the version number as the old row’s deletion id. The result of all this extra record keeping is that read queries never lock tables, pages, or rows. They simply read data as fast as they can, making sure to select only rows that meet the criteria laid out earlier. The drawbacks are that the server has to store a bit more data with each row and do a bit more work when examining rows. Table 2-1 summarizes the various locking models and concurrency in MySQL.
Table 2-1. Locking models and concurrency in MySQL
blog comments powered by Disqus |