Concurrency Control

• Chapter 9, page 397
• This is not a problem in a single user database
• This is not a problem when only one person has the rights to write in a database
• This is a problem when multiple users have the rights to read/write
• Concurrent Transactions
  • Two or more processes working on the database at the same time.
  • But one cpu, so the instruction stream for the processes may be intermixed.
• The hope is to give the user in a multi-user system the same results they would have received if they were working on a single-user system.
  • There may be changes due to multiple users
  • But this is done in a controlled, or anticipated way.
• There are multiple ways to control concurrency
  • Each has strengths and weaknesses.
  • Speed vs accuracy vs level of complexity
  • Fast, right or cheap, pick two.
• Serializable transactions
  • If the outcome of concurrent transactions is the same as if the transactions occurred in (some) serial manner, then the system is serializable
  • Widom presents the example of reserving a seat on a flight.
    • FLIGHTS(FlightNumber, FlightDate, SeatNumber, SeatStatus)

    • SELECT SeatNumber 
          FROM FLIGHTS 
          WHERE FlightNumber=123 AND FlightDate='2015-05-11'
                AND SeatStatus = 'available';
      
      UPDATE FLIGHTS
          SET SeatStatus = 'occupied' 
          WHERE FlightNumber=123 AND FlightDate='2015-05-11'
                AND SeatNumber='22A';
      	     

    • Assume that two passengers try to book the same seat, at the "same" time.
    • How could these two sets of queries end badly?
  • In some sense, we don't care who wins, just that both don't win.
  • So we want to make sure that the transactions behave as if they are run in serial.
• MySQL has a TRANSACTION ISOLATION LEVEL that controls seralizability
  • Reference
  • INNODB only
  • GLOBAL or SESSION, or none
    • none: next transaction
    • SESSION: All transactions from this point in the current session
    • GLOBAL: all subsequent sessions
  • REPEATABLE READ, READ COMMITTED, READ UNCOMMITTED, SERALIZABLE
    • READ UNCOMMITTED : selects are performed, no locking is used.
      • Results are not consistent
      • This is a "dirty read"
        • Data written but not yet committed is dirty data.
        • A dirty read is a read of dirty data written by another transaction.
        • Problematic in a rollback.
      • Widom provides an example of a dirty read problem
        • Take $100 from account 1 and add it to account 2
          1. Add the money to account 2
          2. Test to see if account 1 has enough money
             1. If it does, remove it from account 1
             2. If it does not, remove the amount from account 2
        • Not a problem for account 1, even with a dirty read
        • But a real problem for account 2, they might have MUCH more money in the account than they actually have.
        • Or even worse
          • A1 has $100, A2 has $200, A3 has $300

            T1 wants to transfer $150 from A1 to A2
            T2 wants to transfer $250 from A2 to A3
            
                                          A1        A2      A3
               	                     100       200     300
            T2 Adds $250 to A3   (1)     100       200     550
            T1 Adds $150 to A2   (1)     100       300     550
            T2 Checks and Allows (2b)    100        50     550
            T1 Checks and Fails  (2a)    100      -200     550
            			  

      • READ COMMITTED
        • Locks the data that is there,
        • But new data can be inserted during the read.
        • A Dirty READ is not possible with READ_COMMITTED
        • But a Nonrepeatable Read is
          • A transaction finds data has changed from a previous read due to another transaction.
      • REPEATABLE READ
        • the default in INNODB
        • SELECT @@GLOBAL.tx_isolation, @@tx_isolation;
        • A snapshot is created at the beginning of the read.
          • Reference
          • A snapshot is a version of the data at a given point in time.
          • No changes after the select begins are incorporated into the database.
        • Multiple selects will return the same values
        • Various levels of locks are used.
        • A Nonrepeatable read is not possible at this level.
        • But a phantom read is
          • A phantom read occurs when a transaction rereads data and finds new rows that were inserted by a committed transaction since previous read.
      • SERIALIZABLE
        • Locks everything down.
  • Atomic Transactions
    • A transaction is also a logical unit of work
    • A series of actions that are either performed successfully as a unit or not at all.
    • All single queries are this way.
    • groups of queries can be forced to become atomic
  • Example:
    • A customer adds an item to an order
      1. Change the customer's row to indicate a new total
      2. Change the sales person's row to indicate a new sales level
      3. Change the order table to hold the new customer's order
      4. Change the item count on the item to reflect the customer's order.
    • How could this fail?
      • The last item is remove by another order after the first three steps have been performed.
      • The customer is dropped for bad credit after step 2
      • The order is canceled after step three
      • The disk becomes full after any step.
    • How would each of these lead to an unexpected inconsistency?
  • SQL calls to handle atomic transactions
    • Reference
    • START TRANSACTION
    • COMMIT
    • ROLLBACK
    • SET AUTOCOMMIT {0,1}
    • AUTOCOMMIT
      • 1: each transaction is committed as soon as it is complete.
      • 0: not committed until a COMMIT.
    • see final/atomic.sql
  • Locks
    • As discussed above sometimes the DBMS will lock tables, such locks are implicit locks
    • If the user locks a table, it is a explicit lock
    • Kroenke says most locking is implicit.
    • The amount of data being locked is the lock granularity
      • A row,
      • Multiple rows
      • Tables
      • ..
    • The more information locked
      • The lower the need for accuracy of what to lock.
      • The safer the transaction?...
      • The slower the database.
    • An exclusive lock means no other transaction can read/write data
    • An shared lock means others can read, but nothing can change data.
  • Locking and Serializable Transactions
    • Go through a growing and shrinking phase.
    • Only acquire locks while growing
    • After a unlock, no new locks can be acquired.
    • Some DBMS do not release locks until after a commit or rollback
  • Deadlock
    • Deadlock occurs when two or more processes are waiting on resources held by another.
    • Dining Philosophers
    • Deadlock detection - has deadlock occurred
    • Deadlock breaking -how do I deal with it.
    • MySQL for Innodb
      • Semi automatic deadlock detection
      • Tries to kill the transaction with minimum number of rows
      • Causes a rollback.