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 of:
  • Single computer single CPU (increasingly rare): interleaved processing.
  • Single computer multiple CPU (core): parallel processing.
  • Multiple computers: parallel processing.
  • Cloud computing: massive parallelism.
• A task may not be executed in one shot. It may be divided into many execution sequences.
• There are no guarantee of the relative orders of concurrent tasks in an execution schedule.
• Without proper control,
  • Read-write anomaly and write-write anomaly can occur.
  • Database may become inconsistent.

Example:

Transfer $200 from account 1234 to 2345:

http://en.wikipedia.org/wiki/SQL#Transaction_controls

Task t1:
-- Assume: account 1234 = $1,000, account 2345: $500
UPDATE Account SET amount=amount-200 WHERE account_number=1234; -- (1) inconsistent state.
UPDATE Account SET amount=amount+200 WHERE account_number=2345; -- (2)

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 1234: $800, account 2345: $500 (Problem: access data from an inconsistent state)
(3) Task t1 step (2)

Sequence #3: crash and recovery.

(1) Task t1 step (1)
(2) System crashes; task t1 aborted after step (1) (Problem: system ending up in an inconsistent state)

Sequence #4: write-write anomaly

(1) Task t1 step (1)
(2) Task t2 read account amounts, calculate bonus and update accounts. Bonus will be calculated based on account 1234 = $800, account 2345: $500 (Problem: data update in an inconsistent state)
(3) Task t1 step (2)

• Thus, to avoid accessing an inconsistent state, 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.
  1. Before transaction: consistent
  2. Inside transaction: possibly inconsistent, but isolated from concurrent access
  3. After transaction: consistent state; action permanent.
• This refers to the famous ACID properties in DBMS: e.g. http://en.wikipedia.org/wiki/ACID
• ACID properties (from Elmarsi)
  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 the transaction must take the database from one consistent state to another.
  3. Isolation: A transaction should not make its updates visible to other 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 provides only 'eventual consistency'.
• Many DBMS provides ACID by means of locking or multi-versioning.
• Basically, in locking, a transaction may have 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 undo all data changes in the transaction (or since the last COMMIT or ROLLBACK).

Example: from Wikipedia

http://en.wikipedia.org/wiki/SQL#Transaction_controls

START TRANSACTION;
  UPDATE Account SET amount=amount-200 WHERE account_number=1234;
  UPDATE Account SET amount=amount+200 WHERE account_number=2345;

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

Application Programmer' responsibility in terms of ACID:

1. Atomicity: the transaction is either fully committed, or fully rollback. The programmer 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. The following transaction can be atomic but inconsistent.

START TRANSACTION;
  UPDATE Account SET amount=amount-200 WHERE account_number=1234;
  UPDATE Account SET amount=amount+400 WHERE account_number=2345;

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

• Isolation: Concurrent accesses will leave the data base consistent. Consistent states within a transaction is isolated from concurrent access. Usually no concern for the application programmers.
• Durability: Once committed, the transaction is finalized. Usually no concern for the application programmers.

2. MySQL Transaction Management

• MySQL Manual on TM statements: http://dev.mysql.com/doc/refman/5.6/en/sql-syntax-transactions.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: using Sakila

If a customer rents a film (of inventory id 10, say), Sakila needs to perform the following in an atomic transaction:

1. Insert a new row into the rental table with inventory_id 10.
2. Insert a new row into the payment table with the rental_id automatically created in (1).

This can be accomplished by the following code:

drop procedure rent_film;

DELIMITER $$
#   A customer rents a video
create procedure rent_film(
   IN customer_id INT,       # cutomer who rents the video
   IN inventory_id INT,       # inventory video rented
   IN staff_id INT,          # staff id
   IN rental_fees DECIMAL(5,2)) # FEES PAID TO RENT THE VIDEO INITIALLY
LANGUAGE SQL
NOT DETERMINISTIC
READS SQL DATA
SQL SECURITY DEFINER

BEGIN

DECLARE rental_id INT;

START TRANSACTION;
INSERT INTO rental(rental_date, inventory_id, customer_id, return_date, staff_id)
VALUES (CURRENT_TIMESTAMP(), inventory_id, customer_id, NULL, staff_id);

SET rental_id := LAST_INSERT_ID();

INSERT INTO payment(customer_id, staff_id, rental_id, amount, payment_date)
VALUES(customer_id, staff_id, rental_id, rental_fees, CURRENT_TIMESTAMP());

IF (@@error_count =0) THEN
   COMMIT;
ELSE
   ROLLBACK;
END IF;

END $$

DELIMITER ;

Testing:

CALL rent_film(1,10,1,1.99);

select * from rental
order by rental_id desc
limit 1;

select * from payment
order by payment_id desc
limit 1;
 

Note:

• the use of LAST_INSERT_ID() and CURRENT_TIMESTAMP().
• The stored procedure is basic and does not include error handling code.