Introduction to concurrency control and transaction management

by K. Yue

1. Concepts of Concurrency Control

• Modern databases are multi-user, multi-tasking systems: many users access the system concurrently with many tasks.
• 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 concurrency control,
  1. Read-write anomaly and write-write anomaly can occur.
  2. Database may become inconsistent.
• Task schedules need to maintain data and transaction integrity.

Example:

Use case: transfer $200 from account 1000 to account 2000.

-- Task t1
-- Assumption: account 1000: $1,000, account 2000: $500
-- Initial consistent state: total of two accounts: $1,500

-- Step [1]:
UPDATE Account SET amount=amount-200 WHERE account_number=1000;

-- Between step [1] and step [2]:
-- Inconsistent state at this point; total of two accounts: $1,300

-- Step [2]:
UPDATE Account SET amount=amount+200 WHERE account_number=2000; -- step [2]

-- After completion of step [2]: consistent state again; total of two accounts: $1,500

Intended sequence #1 for task #1:

(1) Task t1 step [1]
(2) Task t1 step [2]

Sequence #2: read-write anomaly.

(1) Task t1: step [1]
(2) Task t2 reads the inconsistent state to produce an account report: account 1000: $800, account 2000: $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 and account amounts, calculate interest and update accounts. Interests will be calculated based on account 1000: $800, account 2000: $500.
(3) Task t1: step [2]

1.1 ACID Properties

• Thus, to avoid accessing 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 physically consistent state to another. This is known as physical consistency.
  3. Isolation: A transaction should not make its updates visible to other tasks and transactions until it is committed.
  • In the example above, isolation disallows task t2 to access the inconsistent states of accounts 1000 and 2000 within task t1.
  • 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 support 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 END TRANSACTION (or similar) are 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).

Example:

Use case: transfer $200 from account 1 to account 2000.

START TRANSACTION;
  UPDATE Account SET amount=amount-200 WHERE account_number=1000;
  UPDATE Account SET amount=amount+200 WHERE account_number=2000;

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

1.2 Application programmer's 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 logical consistency: that the logic of the code is consistent with the problem requirements.

Example: The following transaction can be atomic but inconsistent.

START TRANSACTION;
  UPDATE Account SET amount=amount-200 WHERE account_number=1000;
  UPDATE Account SET amount=amount+400 WHERE account_number=2000;

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 on top of the Student table in toyu. The column numCourses is the number of courses a student has enrolled in. It is a derived column obtained by counted the number of classId the student in the enroll 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;

-- Populating activeStudent initially.
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 row (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; -- pass on the error with no change.
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;