The purpose of concurrency control is to prevent two different users (or two different connections by the same user) from trying to update the same data at the same time. Concurrency control can also prevent one user from seeing out-of-date data while another user is updating the same data.
The following examples explain why concurrency control is needed. For both examples, suppose that your bank account contains $1,000. During the day you deposit $300 and spend $200 from that account. At the end of the day your account should have $1,100.
Example 1: No concurrency control
1 At 11:00 AM, bank teller #1 looks up your account and sees that you have $1,000. The teller subtracts the $200 check, but is not able to save the updated account balance ($800) immediately.
2 At 11:01 AM, another teller #2 looks up your account and still sees the $1,000 balance. Bank teller #2 then adds your $300 deposit and saves your new account balance as $1,300.
3 At 11:09 AM, bank teller #1 returns to the terminal, finishes entering and saving the updated value that is calculated to be $800. That $800 value writes over the $1300.
4 At the end of the day, your account has $800 when it should have had $1,100 ($1000 + 300 - 200).
Example 2: Concurrency control
1 When teller #1 starts working on your account, a lock is placed on the account.
2 When teller #2 tries to read or update your account while teller #1 is updating your account, teller #2 will not be given access and gets an error message.
3 After teller #1 has finished the update, teller #2 can proceed.
4 At the end of the day, your account has $1,100 ($1000 - 200 + 300).
In Example 1, the account updates are done simultaneously rather than in sequence and one update write overwrites another update. In Example 2, to prevent two users from updating the data simultaneously (and potentially writing over updates from each other), the system uses a concurrency control mechanism.
solidDB offers two different concurrency control mechanisms, pessimistic concurrency control and optimistic concurrency control.
The pessimistic concurrency control mechanism is based on locking. A lock is a mechanism for limiting other user access to a piece of data. When one user has a lock on a record, the lock prevents other users from changing (and in some cases reading) that record. Optimistic concurrency control mechanism does not place locks but prevents the overwriting of data by using timestamps.