Concurrency Control Single User Versus Multi-user System:

1. Concurrency Control Single User Versus Multi-user System: • Database system can be classified on the basis of the number of users who can use the system concurrently. • A DBMS is single user if at most one user at a time can use the system. • It is multi-user if many users can use the system and hence access the database concurrently. • E.g. Airline Reservation System.

2. Multiprogramming: • Allows computer to execute multiple programs or processes at the same time. • If only single CPU exists it can actually execute at most one process at a time. • Multiprogramming OS executes some commands from one process, then suspend that process and execute some commands from next process and so on. • A process is resumed at the point where it was suspended, whenever it gets its turn to use the CPU again.

3. Parallel Processing: • If the computer has multiple hardware processors parallel processing of multiple processes is possible. • All the database access operations are specified in the explicit BEGIN & END statements form one transaction.

4. Basic Database Access Operations: 1. read_item(x): • Reads a database item named x into a program variable. • Executing a read_item(x) command includes the following steps: • Finds the address of the disk block that contains item x. • Copy that disk block into a buffer in main memory. • Copy item x from the buffer to the program variable named x.

5. 2. write_item(x): • Writes the value of a program variable x into the database item named x. • Executing a write_item(x) command includes the following steps: • Find the address of the disk block that contains item x. • Copy that disk block into a buffer in main memory. • Copy item x from the program variable named x into its correct location in the buffer. • Store location in the buffer. • Step 4 is one that actually updates the database on disk.

6. E.g. Of Transactions: T1 T2 Read_item(x); Read_item(x); X:= x-n; x:= x+m; Write_item(x); write_item(x); Read_item(y); Y:= y+n; Write_item(y);

7. The read-set of a transaction is the set of all items that the transaction reads. • The write-set of a transaction is the set of all the items that the transaction writes. • E.g. In the above case, read set of T1 is {x,y} and its write- set is also {x,y}. • read set of T2 is {x} and its write- set is also {x}.

8. Begin Transaction End Transaction State Transition Diagram: Active Partially Committed Commit Committed Abort Abort Failed Terminated

9. Properties of Transaction: A – Atomicity: • Atomicity property of a transaction states that the operations in a transaction are either executed completely or not at all. C – Consistency: • The consistency property sates that the operation sequence of a transaction takes the database from any consistent state to another consistent state (correct, integrity).

10. I – Isolation: • Isolation property states that the intermediate states of transactions are not visible to other transactions. D – Durability: • Durability states that the effects of completed transactions are not lost due to hardware or software failures

12. Transactions – Isolation: • Isolation means that: • Multiple transactions running at the same time not impact each other’s execution. • Each user has the impression that he/she has exclusive access for the entire transaction. • All other transactions that happen at the same time should appear either as before or after it.

13. Schedules: • When transactions are executing concurrently in an interleaved fashion, then the order of execution of operations from various transactions is known as a schedule (or history).

14. Examples of Schedule: T1 T2 read_item(x) x:= x-n; read_item(x); x:= x+m; write_item(x); read_item(y); write_item(x); y:= y+n; Write_item(y); Schedule: r1(x); r2(x); w1(x); r1(y); w2(x); w1(y);

15. T1 T2 read_item(x) x:= x-n; Write_item(x) read_item(x) x:= x+m; write_item(x) Write_item(y) Schedule: r1(x); w1(x); r2(x); w2(x); r1(y);

16. Conflicting Operations: • Two operations in a schedule are said to conflict, if they satisfy all three of the following conditions: 1. They belong to different transactions. 2. They access the same item x. 3. At least one of the operation is a write_item(x).

17. Serializability of Schedules: • Serializability of schedules is used to identify which schedules are correct when transaction executions have interleaving of their operations in the schedules. • A schedule can be: • Serial Schedule: • Non-serial Schedule:

18. Serial Schedules: T1 T2 Read_item(x); X:= x-n; Write_item(x); Read_item(y); Y:= y+n; Write_item(y); read_item(x); x:= x + m; write_item(y);

19. Non - Serial Schedules: T1 T2 Read_item(x); X:=x-n; read_item(x); X:=x+m; Write_item(x); Read_item(y); write_item(x); Y:=y+n; Write_item(y);

20. Concurrency control: • Takes care that transactions access database items (database, table, page, row, index key) such that the meaningful results are produced. • Produces a schedule of database operations from transactions running concurrently so the order of operations for each particular transaction is preserved.

21. Need for Concurrency Control : • If DBMS provides concurrency control support for transactions, users/programmers do not need to worry that there are other transactions running at the same time or not. • Problems can occur when concurrent transactions execute in an uncontrolled manner. • Isolation (+ Consistency) => Concurrency Control. • Multiple transactions may want to access and modify the same resources. • Whenever multiple processes share resources there is need to schedule the access.

22. Transaction Schedule – By Example: • Assume that Transaction T1 has operations O1 O2 O3 • Assume that Transaction T2 has operations P1 P2 P3 • O1 O2 P1 O3 P2 P3 is a schedule • O1 P1 O3 P2 P3 O2 is nota schedule • order is not preserved operation O3 must be executed after O2 within T2

23. Serial Schedule: • Schedule is serial if all operations from one transaction are completed prior to beginning of another transaction. • Each serial schedule is considered correct since one transaction is independent of the other transactions • There is no overlapping of transactions

24. Serial Schedule – Examples and Main Problem: • In the example on the right: • Transactions T1 and T2 update totally different items of the database (X,Y) • Hence, T1 and T2 could have been executed concurrently (“in parallel”) • Serial schedules are always correct but do not use computer resources on optimal way (for concurrency and performance)

26. How To Improve Efficiency? Non-serial schedules : • Allow transactions to occur at the same time (concurrently). • Operations of one transaction can be executed before another transaction is committed. • Schedules where transactions occur concurrently are called non-serial schedules or concurrent schedules.

27. Non-Serial Schedule – Example and New Problems : • If operations are not “meaningfully” ordered, we can get unexpected results. • Typical problems with schedules: • Dirty read • Non-repeatable read • Phantom read

29. Dirty Read : • A DirtyRead occurs because transaction T2 sees the uncommitted results of transaction T1. • Transaction T1 reads an item and updates it • Transaction T2 reads updated item • Transaction T1 might abort in the future (and its update would be annulled). • In meantime, transaction T2 proceeds with the item that now has incorrect / uncommitted value. • Expected (good) behavior if the transactions were serialized: Once T1 is aborted, T2 will still use the old (valid, non-updated) value of the item.

31. Non-Repeatable Read : • A No repeatableRead occurs if transaction T1 retrieves a different result from the each read. • Transaction T1 reads an item. • Transaction T2 reads and updates the same item. • Transaction T1 reads the same item again, but now it has a new, modified value. • Expected (good) behavior if the transactions were serialized: If a transaction only reads (and does not modify) the item, each time the item is read, the same value will be obtained.

33. Phantom Read : • A PhantomRead occurs if transaction T1 obtains a different result from each Select for the same criteria. • Transaction T1 executes search on certain criteria and retrieve m items from a table. • Transaction T2 inserts another item that would match the search criteria. • Transaction T1 again executes search and now retrieves m+1 items from the table. • Expected (good) behavior if the transactions were serialized: The first and the second search within the same transaction will give the same result

35. Locking Technique for Concurrency Control: • Locking is very important in a multi-user DBMS. • Locking allows one user to work with a data item without another user changing the data item's value. • Locking is necessary for maintaining data integrity while concurrent users access database information.

38. Locks: • Lock is implemented as a variable associated to a data item. • Locks can be placed explicitly by the program, or implicitly by the DBMS. • Lock describes status of an item with respect to operations that can be performed on the item. • Generally there is one lock for each data item in the database. • Locks are used as a means of synchronizing the access by concurrent transactions to the database items.

39. Types of Locks: 1. Binary Locks: • A binary lock can have two states i.e. locked (1)and unlocked(0). • A distinct lock is associated with each data item X. • If the value of the lock on X is 1. item X cannot be accessed by a database operation that requests the item. • If the value of the lock on X is ), the item can be accessed when requested. • The current value (state) of the lock associated with item X as LOCK(X).

40. Two operations, lock-item and unlock – item are used with binary locking. • A transaction requests access to an item X by first issuing a lock_item(x) operation. • If Lock(X) = 1, the transaction is forced to wait. • If Lock(X) = 0, it is set to 1 (the transaction locks the item), and the transaction is allowed to access item(X). • When the transaction is finished using the item, it issues an unlock_item(X) operation, which sets LOCK(X) to 0 so that X may be accessed by other transactions. • Each lock is a record with 3 fields:- • <data item name, LOCK, locking transaction> plus a queue for transactions that are waiting to access the item.

41. In a simple Binary Locking scheme, every transaction must obey the following rules:- • A transaction T must issue the operation lock_item(X) before any read_item(X) or Write_item(X) operations are performed in T. • A transaction T must issue the operation Unlock_item(X) after all read_item(X) and write_item(X) operations are completed in T. • A transaction T will not issue a lock_item a lock_item(X) operation if it already holds the lock on item X. • A transaction T will not issue an unlock_item(X) operation unless it already holds the lock on item X.

42. 2. Shared/ Exclusive (Read/Write) Locks: • Binary locking scheme is too restrictive for database items, because at most one transaction can hold a lock on the given item. • Several transactions can access the same item X if they all access X for reading purpose only. • However if a transaction is to write an item X, it must have exclusive access to X. • For this purpose, a different type of lock called a multiple mode lock is used.

43. In shared/Exclusive or Read/Write locking scheme, there are three locking operations: • Read_lock(X), • Write_lock(X), • Unlock(X). • A lock associated with an item X, Lock(X), has 3 possible states: “read_locked”, “write_locked” or “unlocked”. • A read locked item is called Shared_locked, because other transactions are allowed to read the item, where as a write_locked item is called exclusive_locked, because a single transaction exclusively holds the lock on the item.

44. Each record in the lock table will have four fields: • <data item name, lock, no. of records, locking transactions>. • To save space , the system maintains lock records only for locked items in the lock table. • The value (state) of lock is either read_lockes or write_locked.

45. In shared / Exclusive locking scheme, a transaction must obey the following rules:- • A transaction T must issue the operation read_lock(X) or Write_lock(X) before any read_item(X) is performed in T. • A transaction T must issue the operation Write_lock(X) before any write_item(X) operation is performed in T. • A transaction T must issue the operation Unlock(X) after all read_item(X) and write_item(X) operations are completed in T. • A transaction T will not issue a read_lock(X) operation if it already holds a read (shared) lock or a write (exclusive) lock on item X. • A transaction T will not issue a write_lock(X) operatio9n if it already holds a read (shared) lock or write (exclusive) lock on an item X. • A transaction T will not issue an unlock(X) operation. Unless it already holds a read (shared) lock or a write (exclusive) lock on item X.

46. Conversion of Locks: • A transaction that already holds a lock on item X is allowed under certain conditions to convert the lock from one locked state to another. • This is called as lock conversion. • E.g. It is possible for a transaction T to issue a read_lock(X) and then later upgrade the lock by issuing a write_lock(X) operation. • If T is the only transaction holding a read lock in X at the time it issues the write_lock(X) operation, the lock can be upgraded; otherwise the transaction must wait.

47. It is also possible for a transaction T to issue a write_lock(X) and then later on to downgrade the lock by issuing a read_lock(X) operation.

48. Two Phase Locking: • Binary Locks/ & Read/Write locks in transactions, does not guarantee serializability of schedules on it own. • To guarantee serializability, an additional protocol, concerning the positioning of locking and unlocking operations in every transactions. • Such a transaction can be divided into 2 phases: • Expanding /Growing Phase. • Shrinking Phase.

49. Expanding Phase: • In this phase, new locks on items can be acquired but none can be released. Shrinking Phase: • In this phase existing locks can be released but no new locks can be acquired. • If lock conversion is allowed, then upgrading of locks (i.e. from read_locked to write_locked) must be done during the expanding phase, and downgrading of locks (from write_locked to read_locked) must be done in shrinking phase. • A transaction that follows two phase locking guarantees serializability.

50. Two phase locking limits the amount of concurrency that can occur in a schedule. • This is because transaction T may not be able to release an item X after it is finished using it. • If T wants to lock an additional item Y later on, T must lock it before it needs. • Other transaction wanting to lock Y cannot lock if it because T has already locked it even though it is not using Y. • Hence the other transaction is forced to wait until T goes into shrinking phase.