Concurrency Control

The real issue to ensure isolation, coherency and atomicity is the write operations on the same data (add, update and delete):

• if all transactions are only reading data, they can work at the same time without modifying the behavior of another transaction.
• if (at least) one of the transactions is modifying a data read by other transactions, the database needs to find a way to hide this modification from the other transactions. Moreover, it also needs to ensure that this modification won’t be erased by another transaction that didn’t see the modified data.

This problem is a called concurrency control.

The easiest way to solve this problem is to run each transaction one by one (i.e. sequentially). But that’s not scalable at all and only one core is working on the multi-processor/core server, not very efficient…

The ideal way to solve this problem is, every time a transaction is created or cancelled:

• to monitor all the operations of all the transactions
• to check if the parts of 2 (or more) transactions are in conflict because they’re reading/modifying the same data.
• to reorder the operations inside the conflicting transactions to reduce the size of the conflicting parts
• to execute the conflicting parts in a certain order (while the non-conflicting transactions are still running concurrently).
• to take into account that a transaction can be cancelled.

More formally it’s a scheduling problem with conflicting schedules. More concretely, it’s a very difficult and CPU-expensive optimization problem. Enterprise databases can’t afford to wait hours to find the best schedule for each new transaction event. Therefore, they use less ideal approaches that lead to more time wasted between conflicting transactions.