Pessimistic and optimistic concurrency controls

▪ Pessimistic concurrency control (or pessimistic locking) is called "pessimistic" because the system assumes the worst — it assumes that two or more users will want to update the same record at the same time, and then prevents that possibility by locking the record, no matter how unlikely conflicts actually are.

The locks are placed as soon as any piece of the row is accessed, making it impossible for two or more users to update the row at the same time. Depending on the lock mode (shared, exclusive, or update), other users might be able to read the data even though a lock has been placed. For more details on the lock modes, see Lock modes: shared, exclusive, and update.

▪ Optimistic concurrency control (or optimistic locking) assumes that although conflicts are possible, they will be very rare. Instead of locking every record every time that it is used, the system merely looks for indications that two users actually did try to update the same record at the same time. If that evidence is found, then the updates of one user are discarded and the user is informed.

For example, if User1 updates a record and User2 only wants to read it, then User2 simply reads whatever data is on the disk and then proceeds, without checking whether the data is locked. User2 might see slightly out-of-date information if User1 has read the data and updated it, but has not yet committed the transaction.

a Each time that the server reads a record to try to update it, the server makes a copy of the version number of the record and stores that copy for later reference.

b When it is time to commit the transaction, the server compares the original version number that it read against the version number of the currently committed data.

– If the originally read value and the current value on the disk are not the same, then someone has changed the data since it was read, and the current operation is probably out-of-date. Thus the system discards the version of the data, aborts the transaction, and returns an error message.

– The step of checking the version numbers is called validation. The validation can be performed at the commit time (normal validation) or at the time of writing each statement (early validation). In solidDB, early validation is the default method but you can change when validation occurs by using the General.TransactionEarlyValidate parameter, see General section.

solidDB can store multiple versions of each data row temporarily, rather than giving each user the version of data that is on the disk at the moment it is read. Each transaction sees the database as it was at the time that the transaction started. This way the data that each user sees is consistent throughout the transaction, and users are able to concurrently access the database.

Even though the optimistic concurrency control mechanism is sometimes called optimistic locking, it is not a true locking scheme—the system does not place any locks when optimistic concurrency control is used. The term locking is used because optimistic concurrency control serves the same purpose as pessimistic locking by preventing overlapping updates.

When you use optimistic locking, you do not find out that there is a conflict until just before you write the updated data. In pessimistic locking, you find out there is a conflict as soon as you try to read the data.

With pessimistic locking, the first user to request a lock, gets it. When you have the lock, no other user or connection can override your lock. In solidDB, the lock lasts until the end of the transaction or in the case of long table locks, the lock lasts until you explicitly release the lock.

The default concurrency control mechanism depends on the table type:

You can override optimistic locking on D-tables and specify pessimistic locking instead. You can do this at the level of individual tables. One D-table might follow the rules of optimistic locking while another table follows the rules of pessimistic locking. Both tables can be used within the same transaction and even the same statement; solidDB handles this internally.

Optimistic locking allows fast performance and high concurrency (access by multiple users), at the cost of occasionally refusing to write data that was initially accepted but was found at the last second to conflict with the changes of another user.

Pessimistic locking requires overhead for every operation, whether or not two or more users are actually trying to access the same record. The overhead is small but adds up because every row that is updated requires a lock. Furthermore, every time that a user tries to access a row, the system must also check whether the requested row or rows are already locked by another user or connection.

For example, if two bank tellers are accessing the same record around the same time and bank teller #1 gets a lock, teller #2 must check for that lock, no matter how unlikely it is that teller #2 will work on the same record exactly at the same time as teller #1. Checking every record that is used takes time. Furthermore, it is important that during the checking, no other teller tries to run the same check as teller #2 (otherwise they might both see at 10:59:59 that record X is not in use, and then they might both try to lock it at 11:00:00). Thus even checking a lock can itself require another lock to prevent two users from changing the locks at the time.

In most scenarios, optimistic concurrency control is more efficient and offers higher performance. When choosing between pessimistic and optimistic locking, consider the following points:

▪ Pessimistic locking is useful if there are a lot of updates and relatively high chances of users trying to update data at the same time.

For example, if each operation can update a large number of records at a time (the bank might add interest earnings to every account at the end of each month), and if two applications are running such operations at the same time, they will have conflicts.

Pessimistic concurrency control is also more appropriate in applications that contain small tables that are frequently updated. In this case, conflicts are so probable that optimistic concurrency control wastes effort in rolling back conflicting transactions.

If you are migrating an application from another DBMS that uses pessimistic locking, you should use the pessimistic mode with solidDB too. Using pessimistic mode in solidDB means that you do not have to make changes to the application.

▪ Optimistic locking is useful if the possibility for conflicts is very low – there are many records but relatively few users, or very few updates and mostly read-type operations.