COMSCI 2200: Concurrency Control

Transaction

A set of several operations constituting a logical task unit, which converts a consistent state of the database to another consistent state.

Transaction

A unit of performing a series of read and write operations in a database, which should be

performed at the same time.

Atomicity

All data should be successfully entered or failed in one transaction.

Consistency

The transaction result should always be the same expected value.

A transaction should be controlled using Lock, Timestamp, Validation, etc. to prevent other transactions from intervening in the middle of processing the transaction.

Isolation

Another transaction should not intervene and process the data until

transaction commit or rollback isissued.

Durability

The persistence of a successful transaction should be guaranteed. In this case, the data should be restored using the forward recovery algorithm.

Transaction commit operation:

An operation that completes a transaction after permanently reflecting the results of all SQL statements in the transaction to the database.

Transaction rollback operation:

An operation that restores all data values that have been modified by a transaction so far to the state prior to executing the transaction (that is, aborting the transaction), when the transaction cannot continue due to a fatal error in the middle of the transaction or because the user issues a rollback command.

Implementation of transaction concurrency:

Simultaneous execution of transactions can increase concurrency by increasing the transaction throughput and system utilization ratio and by reducing latency.

Executing a transaction as quickly as possible:

If a transaction takes too long, there is a high possibility that conflicts and deadlocks will occur due to the long locking time. Locking should be maintained long enough to ensure transaction serialization, but should be executed quickly enough so as not to deteriorate performance at the same time.

Serializable Schedule

Means that even if each transaction is executed at the

same time, the result is the same as the sequential execution of the

transaction.

Concurrency Control

A function that enables the successful execution of several transactions at the same time in a database system that supports a multi-user environment.

Concurrency Control

A database system that supports a multi-user environment must have this function, which is also called concurrency control.

To create a serializable schedule or ensure the possibility of transaction serialization.

Purposes of Concurrency Control (1)

To guarantee the maximum sharing level, minimum response time, and maximum system activity.

Purposes of Concurrency Control (2)

To ensure data integrity and consistency.

Purposes of Concurrency Control (3)

Lost Update

• Occurs when transactions update the same data simultaneously.

• Occurs when the later transaction overwrites the update value before the previous transaction has completed the transaction after updating the data.

Dirty Read

• Other transactions refer to the intermediate execution result of a transaction.

Inconsistency

• A database remains inconsistent when two transactions are executed at the same time.

Cascading Rollback

• When a specific transaction cancels processing if data is shared by multiple transactions, the part processed by other transactions cannot be cancelled.

Unrepeatable Read

• When a transaction executes the same query twice, the query results differ because other transactions have modified or deleted the value in the middle.

1.Lock-based technique

2.Timestamp-based technique

3.Multi-version technique

4.Verification (optimistic) based technique

Concurrency Control Technique

Lock-Based Technique

Lock Based Protocols in DBMS is a mechanism in which a transaction

cannot Read or Write the data until it acquires an appropriate lock.

Lock based protocols help to eliminate the concurrency problem in DBMS for simultaneous transactions by locking or isolating a particular transaction to a single user.

Lock-Based Technique

A lock is a data variable which is associated with a data item. This lock

signifies that operations that can be performed on the data item. Locks in DBMS help synchronize access to the database items by concurrent transactions.

Lock-Based technique

All lock requests are made to the concurrency-control manager. Transactions proceed only once the lock request is granted.

Timestamp-Based Technique

An algorithm which uses the System Time or Logical Counter as a timestamp to serialize the execution of concurrent transactions. The Timestamp-based protocol ensures that every conflicting read and write operations are executed in a timestamp order.

Timestamp-Based Technique

The older transaction is always given priority in this method. It uses system time to determine the time stamp of the transaction. This is the most commonly used concurrency protocol.

Timestamp-Based Technique

Lock-based protocols help you to manage the order between the conflicting transactions when they will execute. Manage conflicts as soon as an operation is created.

Multi-Version technique

A database optimization technique that creates duplicate copies of records so that data can be safely read and updated at the same time.

Multi-Version Technique

When implemented properly by a DBMS, provides the following benefits:

 diminished need for database locks;

 fewer database access contention issues;

 improved read access performance;

 continued record isolation for write operations; and

 reduced number of database deadlocks.

Multi-Version Technique

DBMS reads and writes don’t block each other.

1. Every database record has a version number.

2. Concurrent reads happen against the record with the highest version number.

3. Write operations operate on copy of the record, not the record itself.

4. Users continue to read the older version while the copy is updated.

5. After the write operation is successful, the version id is incremented.

6. Subsequent concurrent reads use the updated version.

7. When a new update occurs, a new version is again created, continuing the cycle.

HOW DOES AN MVCC DATABASE WORK?

• Unlike traditional a DMBS, multiversion concurrency control does not lock a record when a write operation is about to occur. Instead, a new version of the record, with an incremented version number, is created.

• Users can continue to read the old version of the record while the new record is transitionally edited and updated. This eliminates the need for locks, as well as contention and deadlock issues.

• When the new version of the record is committed to the database, all future read operations work on the updated version. New write operations again create a new version, and the cycle continues.

Difference between MVCC vs. Locking

• While MVCC offers many benefits, there are two key drawbacks to multiversion concurrency control:

• Concurrent update control methods are difficult to implement.

• The database grows in size and becomes bloated by multiple versions of DBMS records.

• For the user, or even the developer, the complexity involved to implement MVCC concurrency control methods is completely hidden, as the functionality is provided by the database vendor.

• The developer can write SQL, and the end user can consume applications as they normally would. The fact that the DBMS uses multiversion concurrency control behind the scenes is completely transparent to them.

WHAT ARE THE DRAWBACKS TO MULTIVERSION CONCURRENCY

CONTROL (MVCC)?

Optimistic Concurrency Control or Optimistic Locking

is a concurrency control method applied to transactional systems such as relational database management systems and software transactional memory. OCC assumes that multiple transactions can frequently complete without interfering with each other. While running, transactions use data resources without acquiring locks on those resources.

Begin

Modify

Validate

Commit Rollback

Optimistic concurrency control transactions involve these phases

Modify

Read database values, and tentatively write changes.

Validate

Check whether other transactions have modified data that this transaction has used (read or written). This includes transactions that completed after this transaction's start time, and optionally, transactions that are still active at validation time.

Begin

Record a Timestamp marking involve these phases

Commit/Rollback

If there is no conflict, make all changes take effect. If there is a conflict, resolve it, typically by aborting the transaction, although other resolution schemes are possible. Care must be taken to avoid a time-of-check to time-of-use bug, particularly if this phase and the previous one are not performed as a single atomic operation.

2-Phase Locking Technique

controls concurrency by guaranteeing serialization and follows the 2PL protocol, which performs jobs by dividing ‘lock’ and ‘unlock’ operations into the expansion and contraction phases.

Expansion Phase

A phase in which a transaction can perform locking only, and cannot perform unlocking.

Contraction Phase

A phase in which a transaction can perform unlocking only, and cannot perform locking.