Concurrency Control

When dealing with many processes interacting with a database, it is common to encounter scenarios where process A is writing to some record x while process B is reading from that same record x. In such occurances, what should be correct or expected behaviour?

Pessimistic locking is one approach. This will effectively allow process A OR process B exclusive access to record x. During this time the other process which does not have access to X must wait until it is available.

Optimistic concurrency control. With this method, processes are free to read record x as they please - once ready to perform an action however, they will check if the value located at the memory region accessed during the initial read was changed. If is was, the process can then discard or rollback its current action and try again. This could be implemented using compare and swap instruction

Multi-version concurrency control - this is a form of optimistic locking. A process takes the current version of the record x, performs some action and then compares the version it acquired during initial read with the current version.