CONCURRENCY CONTROL

• Databases


  A collection of data that is organized and stored in a way that allows for efficient retrieval, manipulation, and management of information
• Concurrency


  When multiple users are trying to access a database at the same time
• Concurrency Control


  The structure and rules to resolve read-write and write-write conflicts, and to enforce isolation (through mutual exclusion) among conflicting transactions
• Transaction


  A group of statements that must be performed as a single logical unit, that must either completely occur or fail as a whole
• Transactions


  • All statements must be successfully completed
  • If one of the statements failed to execute, the entire transaction will fail and need to rollback (undo) with no changes saved to the database
• ACID


  Atomicity, Consistency, Isolation, Durability - important for database integrity, reliability, performance, and correctness in environments with concurrent data interactions
• Atomicity


  Ensures transactions are all-or-nothing, rolling back if any part fails, keeping the database state unchanged
• Consistency


  Guarantees transactions move the database from one valid state to another, adhering to all data integrity rules
• Isolation


  Keeps transactions separate to prevent interference, using mechanisms like locking to avoid issues like dirty reads
• Durability


  Once a transaction commits, it becomes permanent, even after system failures, ensured by logging changes before application
• Starting and Committing a Transaction

  1. START TRANSACTION

  2. COMMIT
• Locking


  The most widely used form of concurrency control to enforce isolation (through mutual exclusion) among conflicting transactions
• Locking


  • Secures permission to Read and permission to Write a data item for a transaction
  • Locks are granted and released by lock manager (database administrator)
• Read Lock


  Also known as S lock or Shared lock - database is always readable by other users, even when locked by a first user, but no write operations allowed until lock is released
• Write Lock


  Also known as X-lock or Exclusive lock - the session holding the write lock can read and write, but the database becomes inaccessible to all other users until the first transaction is completed
• Implicit and Explicit Locks


  InnoDB storage engine automatically applies row-level locking for InnoDB tables, allowing concurrent multiple transactions of READ/WRITE operations on the same table but not on the locked rows, without making each other wait
• LOCK Syntax


  1. LOCK TABLES table_name1 READ | WRITE

  2. LOCK TABLES table_name1 READ | WRITE, table_name2 READ | WRITE, ...

  3. UNLOCK TABLES
• Read Lock


  • LOCK TABLES info_table READ
• Write Lock


  • LOCK TABLES info_table WRITE
• Write Lock


  Allows WRITE operations
• Write Lock


  Prevents other transactions from accessing the locked table until the first transaction is completed
• Problem using lock

  Modern database systems are designed to permit multi-users accessing the database concurrently
• Problem using lock

  Care must be taken during the design phase as multi-user access can cause issues for data integrity