Transaction Management and Concurrency Control

1. Concurrency Control

1.1. Techniques

1.1.1. Locking

1.1.1.1. Denies other transactions access until the current transaction is complete to prevent incorrect updates

1.1.2. Time-Based Concurrency Control

1.1.2.1. It is optimistic as when conflict is discovered, transactions are rolled back and restarted

1.1.3. Timestamping

1.1.3.1. Orders transactions in such a way that older transactions and those with smaller timestamps get priority in the event of a conflict ehich is resolved by atomicity and restarting the transaction

1.1.4. Recovery Control

1.1.4.1. Restores the database to correct state in the event of a failure by either undoing or redoing what has happened.

1.1.4.1.1. Types of Failure

1.1.4.1.2. Recovery Facilities

1.2. Problems

1.2.1. Lost Update Problem

1.2.1.1. Successfully executed update is overriden by another user

1.2.2. Uncommitted dependacy Problem(Dirty Read)

1.2.2.1. Occurs when one transaction can see intermediate results of another transaction before it has been committed

1.2.3. Inconsistent Analysis Problem

1.2.3.1. Occurs when transaction reads several values but second transaction updates some of them during execution at first

2. Database Transactions

2.1. ACID

2.1.1. Atomicity

2.1.1.1. Complete transaction exceution or no exceution at all

2.1.2. Consistency

2.1.2.1. Each transaction should maintain the database consistency and not intefere with the predefined rules

2.1.3. Isolation

2.1.3.1. Concurrent execution of a set of transactions has the same effect as the serial execution of that set

2.1.4. Durability

2.1.4.1. Effects of committed transactions are permanently recorded in the database and the database should have back ups in case of any mishap