Transactions and Concurrency in DBMS

Posted on:July 9, 2023 at 06:23 PM

transactions-and-concurrency-in-dbms Photo by NEOM Unsplash

Table of Contents

Open Table of Contents

Transactions

Transactions are Unit of Work executed in the database.

Transactions may be used when actions that need to be rolled back in the case of possible failure.

In writing to multiple different tables, deleting, and operating on them transactions are helpful. (For example, we are writing to the table-1 then writing to the table-2 but while we are writing to the table-2 an error is occurring, if this is a single unit of work we would like to undo/rollback our writes to table-1)

We want atomicity. Statements in the transaction should succeed or fail as a whole.

Commit makes the transaction permanent, and Rollback cancels the transaction.

Example transaction: ‘A’ sends money to ‘B’

START TRANSACTION
 - Withdraw 100$ from A
 - Deposit 100$ to B
COMMIT

Should I use transactions for a single query?

It depends if we want to roll back the query. But in most scenarios, we don’t need to use transactions for a single query.

Transactions should be ACID.

Atomicity

Consistency

Isolation

Durability

Transactions in SQL

In the SQL, We can use the statements below to control the transaction:

Basic Example

START TRANSACTION;
    INSERT INTO users (name, email) VALUES ('John Doe', 'johndoe@example.com');
    UPDATE accounts SET balance = SUM(balance) WHERE name = 'John Doe';
COMMIT;

Example with Try-Catch

START TRANSACTION;
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
        START TRANSACTION
            ROLLBACK;
            RESIGNAL;
        END;
    UPDATE accounts SET balance = 5000 WHERE user_id = 1;
    UPDATE accounts SET balance = 1000 WHERE user_id = 2;
    IF (SELECT balance FROM accounts WHERE user_id = 1) < 0 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Insufficient balance';
    END IF;
COMMIT;

With SAVEPOINT

BEGIN;
    INSERT INTO table1 VALUES (1);
    SAVEPOINT my_savepoint;
    INSERT INTO table1 VALUES (2);
    ROLLBACK TO SAVEPOINT my_savepoint;
    INSERT INTO table1 VALUES (3);
COMMIT;

Concurrency Control in DMBS

concurrency-control-in-dbms Photo by Liam Briese Unsplash

Executing transactions in the database sequentially (serial schedule) is not efficient and execution of the next transaction may be delayed. But executing sequentially ensures data consistency and in the sequential execution there are no concurrency issues.

To increase performance, Transactions are executed concurrently(concurrent schedule). But in the concurrent execution, we may encounter concurrency problems. For preventing concurrency problems, databases provide Concurrency Control Protocols.

Concurrency Control Protocols

There are two types of concurrency control protocols:

  1. Lock Based Protocols
  2. Timestamp Based Protocols

Lock Based Protocols

In the lock-based protocols, transactions should acquire the lock for changing or reading data. In the DBMS, two types of locks exist:

1. Shared Lock

Simultaneous reading of the data by multiple transactions. If the transaction is acquired the shared lock, it can’t change the data it can only read the data.

2. Exclusive Lock

It is a lock that is acquired for changing the data. At any given time only one transaction can hold an exclusive lock. If one transaction holds an exclusive lock, other transactions can’t acquire a shared or exclusive lock for the same data.

There are two types of lock-based protocols:

1. Two Phase Locking

It is a widely used technique. It provides a strict ordering for acquiring and releasing the lock. It has two phases:

2.Strict Two Phase Locking

It is similar to two-phase locking. In the Two-Phase Locking transaction can release the lock before it commits but in the strict two-phase locking, it can’t.

Timestamp Based Protocols

There is a timestamp of the transaction when the transaction enters the system. Coordination is handled with timestamps. Transactions are executed in the correct order with the help of timestamps.

In the DBMS, several isolation levels exist and they use concurrency control protocols for preventing data loss.

Isolation Levels

Multiple users can access the same data at the same time with lower isolation levels but lower isolation levels can cause certain concurrency problems. (e.g. Dirty Read) Conversely, at the higher isolation level, fewer users can access the same at the same time but it ensures data consistency. Higher isolation levels may increase the block time of the system and other transactions. It requires more system resources.

Read Uncommitted

Read Committed

Repeatable Read

Serializable

A serializable execution is defined to be an execution of the operations of concurrently executing SQL-transactions that produces the same effect as some serial execution of those same SQL-transactions.— Second Informal Review Draft ISO/IEC 9075:1992, Database Language SQL- July 30, 1992

In the MySQL default global transaction level can be specified with: --transaction-isolation=level

Which Isolation level should I use?

In most situations, the default isolation level read committed is sufficient. The lowest isolation level increases performance and the highest isolation level increases data consistency so it depends according to the system requirements.

Concurrency Problems in DBMS

While executing transactions concurrently several concurrency problems can occur regarding isolation level.

Dirty Reads

It occurs if the transaction takes a row and the other transaction updates the same row without committing.

In the example below:

dirty-read Image from Wikipedia

Non-repeatable Reads

It occurs when the transaction queries a row twice and between these queries another transaction updates a row and commits.

In the example below:

non-repeatable-read Image from Wikipedia

Phantom Reads

If the transaction takes single or multiple rows twice and between these another transaction adds new rows or deletes existing ones and commits. For prevention, range locks are required.

In the example below:

phantom-read Image from Wikipedia

Comparison Table

Thanks for reading…

Summary

References