• BEGIN TRAN name_change
• UPDATE member
• SET lastname = 'Smith' where member_no = 1
• SAVE TRAN savepoint_name
• DELETE adult where member_no = 1
• ROLLBACK TRAN savepoint_name
• " other SQL statements "
• COMMIT TRAN

Transaction Log

CONCURRENCY CONTROL

Coordination of simultaneous execution of multiple transactions

• the 3 concurrency problems:
  1. The Lost Update Problem -- e.g. the following with time t4>t3>t2>t1

      
  Transaction A        Time     Transaction B
    Fetch R             t1          --
      --                t2        Fetch R
    Update R            t3          --
      --                t4        Update R
  

  • After Transaction A updates R at time t3,
  • Transaction B updates R based on R at earlier time t2
  • The effect of A's update is wiped out by B

  ---

  2. The Uncommitted Dependency Problem

     Transaction A        Time     Transaction B
         --                t1        Update R
       Fetch R             t2          --
         --                t3        RollBack
     

     • A is allowed to retrieve (or worse, update) a row that has been updated by another transaction B which has not been committed
     • When B rolls back, and if the transaction B never commits, then A has seen some data that now no longer exists ( and in a sense "never existed)

     ---

  3. The Inconsistent Analysis Problem

         
     Transaction A        Time     Transaction B
       Fetch Acct1 (40)    t1          --
         sum = 40          t2          --
       Fetch Acct2 (50)    t3          --
         sum = 90          t4          --    
           --              t5      Fetch Acct3 (30) 
           --              t6      Update Acct3 (30 to 20)
           --              t7      Fetch Acct1 (40)
           --              t8      Update Acct1 (40 to 50)
           --              t9      COMMIT
       Fetch Acct3 (20)    t10         --
         sum = 110  [NOT 120!]
     
     

  4. The result produced by A is incorrect, and if written back to the database, will leave the database in an inconsistent state
  5. A is dependent on an uncommitted change since B commits all its updates before A sees Acct3

Concurrency Control Based on Locking

• A transaction ensures an object it is interested in (e.g. a database row, and/or an index) will not change in some unpredicatable manner, it acquires a lock on that object
• The effect of the lock is to guarantee exclusive use and therefore block other transactions out of the object and thereby prevent them from changing it

Lock Granularity

• One can guarantee exclusive use by locking the whole database and results in a single user data so that no one else can use it during that time
• Cannot share the data among multiple users even if they use different tables
• Locking can be applied at different levels:
  1. Database level --
     • good for batch processing, total backup
  2. Table level
     • No one else can access the locked table
     • Others can access other tables.
     • Application is still single-user as another user of the same application cannot access other rows of the same table
  3. Page level
     • A disk-page which is equivalent to a disk-block
     • A page has a fixed size such as 2K, 4K,... (SQL Server uses 2K)
     • A table can span several pages
     • A page can span several rows of one or more tables
     • A page can form part of a large row
     • It is the most commonly used level
  4. Field level
     • Object size down to an attribute
     • Others can access the same row except different attributes
     • High overhead, less efficient

  Lock Types

  1. Binary Locks
     • It has only two states, an object is either locked or unlocked
     • When a transaction needs an object, the object must not be locked
     • It locks the object before using it
     • It unlocks the object after use
     • It eliminates the lost update problem
     • Performance is not so good as too restrictive, two read operations cannot access the same object when obvious none of the operatins will cause any problems
  2. Shared/Exclusive Locks
     • To enable read only operations to share a resource, shared lock is introduced
     • when a transaction wants to read an object: issue a shared-lock if no exclusive-lock is on
     • when a transaction wants to update an object: issue an exclusive-lock when no lock is applied
     • therefore a lock has 3 states: unlocked, shared, and exclusive
     • two transactions conflict only when at least one of them is an update (write) transaction
     • more overhead for the scheduler as:
       1. type of lock must be known
       2. 3 lock operations
       3. enhanced to have lock upgrade and lock downgrade

In SQL-SERVER, locks are automatically applied, you can specify under (optimizer_hints):

• NOLOCK -- Allows "dirty reads," which means that no shared locks are issued and no exclusive locks are honored. This can result in higher concurrency, but at the cost of lower consistency. If this option is specified, it is possible to read an uncommitted transaction or to read a set of pages that are rolled back in the middle of the read, so errors might result
• HOLDLOCK -- Makes a shared lock more restrictive by holding it until the completion of a transaction (instead of releasing the shared lock as soon as the required table or data page is no longer needed, whether or not the transaction has been completed). The HOLDLOCK option applies only to the table or view for which it is specified and only for the duration of the transaction defined by the statement in which it is used
• UPDLOCK -- Takes update locks instead of shared locks while reading the table and holds them until the end-of-command or end-of-transaction.
• TABLOCK -- Takes a shared lock on the table that is held until the end-of-command. If HOLDLOCK is also specified, the shared lock is held until the end of the transaction.
• PAGLOCK -- Takes shared page locks where a single shared table lock would normally be taken.
• TABLOCKX -- Takes an exclusive lock on the table that is held until the end-of-command or end-of-transaction.

Two-Phase Locking to Ensure Serializability

• Locks prevent serious data inconsistencies, it still has two major problems, one of which is serializability
• Two-phase locking protocol defines how transactions acquire and relinquish locks
  1. A Growing phase when a transaction aquires all the required locks without unlocking any data. At this locked point, the transaction can execute
  2. A Shrinking phase when a transaction releases all locks and cannot obtain any new locks
• Two transactions cannot have conflicting locks
• No unlock operation can precede a lock operation in the same transaction
• No data are affected until all locks are obtained

Deadlock Problem

• Deadlock exists when two transactions T1 and T2 are in the following situation:
  1. T1 has a lock on resource A and wishes to obtain B exclusively
  2. T2 has a lock on resource B and wishes to obtain A exclusively
• Each transaction will wait for the other to unlock and end up in a deadly embrace situation
• More than two transactions can be involved in a dead lock (the dependency graph has/is a circle)

3 Techniques to Control Deadlocks

1. Deadlock Prevention -- A transaction requesting a new lock is aborted if there is the possibility that a deadlock can occur (more rollbacks and redoes)
2. Deadlock Detection -- DBMS periodically checks for deadlock, if found, aborts one the transactions (the victim)
3. Deadlock Avoidance -- Transaction must obtain all the locks it needs before it can be executed (Increases response time)

Concurrency Control with Time Stamping

• A global unique time stamp is assigned to each transaction which is the order transactions are submitted to the DBMS
• Unique time stamps means no dupliate time stamp can exist
• Monotonicity of time stamps means the time stamps values are always increasing
• All READ and WRITE for the same transaction use the same time stamp
• The DBMS executes the conflicting transactions in time stamp order to ensure serializability of transactions
• If two transactions are conflicting, (later) one is aborted and rescheduled with a new time stamp
• No locking is needed, but each value stored in the database must have 2 time stamp fields, one for last-read, another for last-update
• Processing overhead increases with additional time stamp management and aborting and rescheduling of transactions

Concurrency Control with Optimistic Methods

Assumes majority of database operations do not conflict
• No locking or time stamping uses
• Transaction executed without restriction until committment time
• Each transaction goes through two or three phases:
  1. Read Phase -- database read and updates recorded to a private copy not accessible by others
  2. Validation Phase -- transaction is validated to assure of integrity and consistency. If positve, transaction goes to next phase, else transaction is aborted and restarted
  3. Write Phase -- apply the recorded changes on the private copy to the permanent database
• It is acceptable for mostly read or query databse systems that has few update transactions.