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: interleaved processing.
  • Single computer multiple CPU: parallel processing.
  • Multiple computers: parallel processing.
• 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 at this point.
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
(3) Task t1 step (2)

Sequence #3: System crashes and recovery

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

Sequence #4: write-write anomaly

(1) Task t1 step (1)
(2) Task t2 read account amounts, calculate interest and update accounts. Interest will be calculated based on account 1234 = $800, account 2345: $500
(3) Task t1 step (2)

ACID

• 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 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).

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;

Programmer' responsibility In terms of ACID:

• Atomicity: the transaction is either fully committed, or fully rollback. The programmer needs to define the scope and action of the transaction.
• 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. Usually not a 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:

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      varchar(6),
   lastName   varchar(20)  NOT NULL,
   firstName    varchar(20)  NOT NULL,
   numCourses  integer(4) DEFAULT 0
);

select * from activeStudent;

insert into activeStudent(stuId, lastName, firstName, numCourses)
select s.stuId, s.lastName, s.firstName, count(e.classNumber) as numCourses
from Student s natural join Enroll e
group by s.stuId, s.lastName, s.firstName;

select * from activeStudent;

When we add the enrollment ('S1010', 'HST2015A', null), we need to perform two tasks:

1. insert ('S1010', 'HST2015A', null) into enroll.
2. increment numCourses for 'S1010' by 1 in activeStudent

When we add the enrollment ('S1013', 'HST2015A', null), we need to perform two tasks:

1. insert ('S1013', 'HST2015A', null) into enroll.
2. insert a new row for 'S1013' into activeStudent since it is not yet there.

We can write a procedure to do so:

drop procedure if exists enroll;

delimiter //

create procedure enroll
   (in stuid varchar(6),
    in classNumber varchar(8),
    in grade varchar(2))
begin

declare already_in int default 0;
declare exit handler for sqlexception rollback;

start transaction;
insert into enroll
values (stuid, classNumber, grade);

select count(*) into already_in
from activestudent a
where a.stuid = stuid;

if (already_in > 0) then
   update activestudent a
   set a.numCourses = a.numCourses + 1
   where a.stuid = stuid;
else
   insert into activestudent
   select s.stuid, s.lastName, s.firstName, count(e.classNumber)
   from student s, enroll e
   where s.stuid = e.stuid
   and s.stuid = stuid;
end if;

commit;

end //

delimiter ;

call enroll('S1010', 'HST205A', null);
call enroll('S1013', 'HST205A', null);

-- clean up
delete from enroll
where stuid = 'S1010'
and classNumber = 'HST205A';

delete from enroll
where stuid = 'S1013'
and classNumber = 'HST205A';