Locks

• Transactions
  • A Transaction is a series of actions, carried out by a single user or application program, which reads or updates the contents of a database.
  • It is a logical unit of work.
  • It may be an entire program or a single portion of a larger set of actions.
  • Transactions should transform the database from one consistent state to another.
  • Transactions begin with a MySQL command START TRANSACTION or BEGIN
  • Or with BEGIN [WORK] , which is an alias for START TRANSACTION
  • This is not the BEGIN from BEGIN ... END
  • Transactions end with a COMMIT or a ROLLBACK
  • Commit will write the results of your transactions to disk.
  • Rollback causes the transactions to be abandon.
  • If the session autocommit variable is set to 0, then commits are required, otherwise they are done at the end of the command.
  • If you disable autocommit, you must commit work.
  • START TRANSACTION
    • Disables autocommit
    • Causes any pending transactions to be committed.
    • Causes locks to be released.
  • There are issues with tables with different engines,
  • Or engines which do not support transactions.
  • With INNODB, you can specify start transaction WITH CONSISTENT SNAPSHOT
    • This is equivalent to doing a select on the tables to be used in the transaction.
    • It avoids locking and reduces concurrency issues.
    • Depending on the isolation level, behavior is different.
• Some Lock Terms:
  • A lock provides exclusive access to a resource.
  • If the DBMS locks the table, it is implicit locking
  • If the external application asks the application to do it, it is explicit locking
  • The granularity of a lock refers to the scale of data locked.
  • An exclusive lock provides access to only one transaction while a shared lock restricts reading, but only one transaction can write.
• Serializable Transactions
  • If two transactions proceed concurrently, the results should be the same as if they occurred sequentially.
  • This is only a problem when two transactions are working in the same are of the database.
  • Scheduling works to guarantee transactional consistency.
  • A schedule is a sequence of operations by a set of concurrent transactions that preservers the order of the operations in each of the individual transactions.
  • A Serial schedule is a schedule where the operations of each transaction are executed consecutively without any interleaved operations from the other transactions.
  • A nonserial schedule is where the operations are interleaved.
  • Constructing a serial schedule guarantees a set of results which could have occurred from some serial execution, but they my not be unique.
  • Example: Lost update:
    • Assume a customer has $100,000 on deposit in the bank. They wish to withdraw the entire amount (T1) . At the same time, the bank is updating interest (T2).
    • Scenario 1: lost write

      	T1 actions                      T2 actions
      	Read balance                    Read balance
      	                                Interest = balance * ir
      	Balance = balance - 100,000     Balance = balance + interest
      	Write balance                   Write Balance
      	   

    • In one case, the the amount will be withdrawn, without interest.
    • In the second case, the user will get interest and the money, and it will still be in the account!
    • We can serialize these and have two results:
      • T1 occurs first, and the user has a balance of 0, then T2 occurs and there is no interest paid, not quite fair, but not bogus
      • T2 occurs, the user receives interest, then T1 occurs and only the interest remains in the account. Nicer for the user but still not bogus.
      • The intermixing of the two transactions is the problem.
    • A nonserial transaction is correct if it produces the same results as SOME serial transaction.
    • This is a serializable schedule.
  • Order of transactions is important (but we learn this is Architecture too)
  • There is only a problem if two transactions are working on the SAME data item.
    • Read and read - non issue.
    • If a write is involved : order is important.
  • Graph theory can be used to detect if transactions are serializable or not.
  • But this problem is NP-Complete (very hard) so it is not done in practice.
• Recoverability
  • An alternative to serialization.
  • If a transaction fails, we need to be able to undo the effects of the transaction.
  • Once a transaction is committeed, it's effects can not be undone.
  • A recoverable schedule is a schedule where, for each pair of transactions T_i and T_j, if T_j reads data previously writtent by T_i, then the commit operation of T_iproceeds the commit operation of T_j
  • Non-recoverable: T1 reads and writes, T2 reads, T1 rolls back, T2 writes and commits.