Introduction to concurrency control and transaction management

by K. Yue

1. Concepts of Concurrency Control

• Modern databases are multi-user systems: many users access the system concurrently.
• The database may be in a system with
  • Multiple CPU
  • Multiple GPU
  • Multiple computers
• A task may not be completed in one continuous execution. It may be divided into many execution steps.
• There are many concurrent tasks.
• There are no guarantees of the relative orders of concurrent tasks in an execution schedule.
• Without proper control,
  1. Read-write anomaly and write-write anomaly can occur.
  2. Database may become inconsistent.

Example:

Transfer $200 from account 6000 to account 8000

-- Task t1
-- Assume: account 6000: $1,000, account 8000: $500
-- consistent state; total of two accounts: $1,500
UPDATE Account SET amount=amount-200 WHERE account_number=6000; -- step [1]
-- inconsistent state at this point; total of two accounts: $1,300
UPDATE Account SET amount=amount+200 WHERE account_number=8000; -- step [2]
-- consistent state again; total of two accounts: $1,500

Intended sequence #1:

(1) Task t1 step (1)
(2) Task t1 step (2)

Sequence #2: read-write anomaly

(1) Task t1 step (1)
(2) Task t2 account reports: account 6000: $800, account 8000: $500
(3) Task t1 step (2)

Sequence #3: System crash and recovery

(1) Task t1 step (1)
(2) System crashes; task t1 aborts after step (1)

Sequence #4: write-write anomaly

(1) Task t1 step (1)
(2) Task t2 reads account amounts, calculate interest and update accounts. Interests will be calculated based on account 6000: $800, account 8000: $500
(3) Task t1 step (2)

1.1 ACID Properties

• Thus, to avoid inconsistent states, concurrency control is necessary.
• Concurrency control is mainly done by transaction management.
• A transaction is a logical unit of database processing that is atomic: either the entire transaction is performed, or none of the transaction action is performed. This is the 'all or nothing' property.
• This refers to the famous ACID properties in DBMS: e.g. http://en.wikipedia.org/wiki/ACID
• ACID properties:
  1. Atomicity: A transaction is an atomic unit of processing. It is either performed in its entirety or not performed at all.
  2. Consistency preservation: A correct execution of a transaction must take the database from one consistent state to another.
  3. Isolation: A transaction should not make its updates visible to other tasks and transactions until it is committed.
  • This property, when enforced strictly, solves the temporary update problem and makes cascading rollbacks of transactions unnecessary.
  4. Durability or permanency: Once a transaction changes the database and the changes are committed, these changes must never be lost because of subsequent failure.
• Implementing ACID can bring performance degradation. Thus, for example, some NoSQL DB provide only 'eventual consistency'.
• Many NoSQL databases support the BASE model instead: Basic Availability, Soft-State, Eventual Consistency.
• Many DBMS provides ACID by means of locking or multi-versioning.
• Basically, in locking, a transaction may have the exclusive access to selected data until the transaction is terminated.
• SQL support of transaction management depends on the vendor. It usually includes:
  1. The execution of a single SQL statement is atomic.
  2. The commands START TRANSACTION (or similar) and SAVE TRANSACTION (or similar) may be available to specify the boundary of transactions.
  3. COMMIT makes all data changes in the transaction to become permanent.
  4. ROLLBACK undoes all data changes in the transaction (or since the last COMMIT or ROLLBACK).

START TRANSACTION;
  UPDATE Account SET amount=amount-200 WHERE account_number=6000;
  UPDATE Account SET amount=amount+200 WHERE account_number=8000;

IF ERRORS=0 COMMIT;
IF ERRORS<>0 ROLLBACK;

1.2 Application programmer' responsibility in terms of ACID

1. Atomicity: the transaction is either fully committed, or fully rollback. The DB developer needs to define the scope and action of the transaction.
2. Consistency: the execution of transaction should keep data consistent. It is the programmer's responsibility to ensure the logic for consistency.

Example: The following transaction can be atomic but inconsistent.

START TRANSACTION;
  UPDATE Account SET amount=amount-200 WHERE account_number=6000;
  UPDATE Account SET amount=amount+400 WHERE account_number=8000;

IF ERRORS=0 COMMIT;
IF ERRORS<>0 ROLLBACK;

1. Isolation: As a result, concurrent access will leave the database consistent. Usually not a concern for the application programmers.
2. Durability: Once committed, the transaction is finalized. Usually no concern for the application programmers.

2. MySQL Transaction Management

• MySQL Manual on TM statements: https://dev.mysql.com/doc/refman/8.1/en/sql-transactional-statements.html
• Autocommit mode: after the execution of a SQL statement, the result is automatically committed.
• START TRANSACTION disables the autocommit mode.
• MySQL supports COMMIT and ROLLBACK.
• It also supports LOCK TABLES and UNLOCK TABLES.

Example:

This is not a realistic example but it shows you an example of transaction management.

Suppose we have an ActiveStudent table:

CREATE TABLE IF NOT EXISTS activeStudent(
    stuId        INT NOT NULL,
    fname        VARCHAR(30) NOT NULL,
    lname        VARCHAR(30) NOT NULL,
    numCourses   INTEGER(4) DEFAULT 0
);

SELECT * FROM activeStudent;

INSERT INTO activeStudent(stuId, fName, lName, numCourses)
SELECT s.stuId, s.fName, s.lName, COUNT(e.classId) as numCourses
FROM Student AS s LEFT JOIN Enroll AS e ON (s.stuId = e.stuId)
GROUP BY s.stuId, s.fName, s.lName;

SELECT * FROM activeStudent;

When we add the enrollment (100000, 10006, NULL, 0), we need to perform two tasks:

1. insert the rwo (100000, 10006, NULL, 0) into enroll.
2. increment numCourses for student 100000 by 1 in activeStudent

We can write a procedure to do so:

DROP PROCEDURE IF EXISTS enroll;

DELIMITER //

CREATE PROCEDURE enroll
   (IN stuid VARCHAR(6),
    IN classId VARCHAR(8),
    IN grade VARCHAR(2),
    IN n_alerts INT)
BEGIN

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
   ROLLBACK;
   RESIGNAL;
END;

START TRANSACTION;

INSERT INTO enroll
VALUES (stuid, classId, grade, n_alerts);

UPDATE activestudent AS a
SET a.numCourses = a.numCourses + 1
WHERE a.stuid = stuid;

COMMIT;

END //

DELIMITER ;

SELECT * FROM Enroll;
SELECT * FROM ActiveStudent;

CALL enroll(100000, 10006, NULL, 0);
CALL enroll(100009, 10006, NULL, 0);

SELECT * FROM Enroll;
SELECT * FROM ActiveStudent;

DROP TABLE ActiveStudent;
DROP PROCEDURE enroll;

DELETE FROM enroll WHERE stuId = 100000 AND classId = 10006;
DELETE FROM enroll WHERE stuId = 100009 AND classId = 10006;

SELECT * FROM Enroll;