Understanding the Locking System
Concurrency and Consistency
In any database management system with multiple users, there is a trade-off between concurrency and consistency. Users need to be able to access data any time (concurrency), but changes to the database must be done in an orderly sequence that maintains the underlying structure of the data (consistency).
The task of the locking system is to manage access to resources shared by user databases, tables, pages and rows to guarantee the consistency of the shared data. Various types of locks are used to ensure that the database does not become inconsistent through concurrent update.
Concurrency control in database management systems (DBMS) ensures that database transactions are performed concurrently without violating the integrity of the database. A transaction is one or more statements processed as a single database action.
Each transaction must have certain properties called ACID properties:
• Atomicity - The effects of either all or none of its operations remain when a transaction is completed--the parts of the transaction are indivisible (atomic).
• Consistency - Every transaction must leave the database in a consistent state.
• Isolation - One transaction cannot interfere with another. Providing isolation is the main goal of concurrency control.
• Durability - Successful transactions must persist through crashes.