Week 07 Notes 03 – Transaction properties

As discussed in the last notes, transactions:

· are a mechanism provided by the DBMS,

· cause the DBMS to behave as if each transaction has exclusive access to the dbms from beginning to end (i.e., transactions are serializable).

Transactions are a trade-off between protection and throughput, and the DBMS provides several choices on how you make the trade-off.

ACID

Transactions have the following properties:

Atomic

All actions that are part of the transaction happen, or none of them do.

Durable

Once a transaction commits, its updates survive in the database, even if there is a subsequent system crash.

Consistent and Isolated

Consistency and Isolation are intertwined in a way that is not easy to understand. 

Consistency

Statement-level consistency

Once you start an sql statement, you are guaranteed no other transaction will be able to make changes to data you need in the statement.  Book uses the example:

update customer set AreaCode=’425’ where ZipCode=’98050’

Must do a read to find all records where zipcode=’98050’.  Statement-level consistency means no one is allowed to change a zipcode in the customer table once your update statement starts.

mysql supports statement-level consistency with explicit locking statements. 

http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html

Shared lock == SELECT ... LOCK IN SHARE MODE # blocks if some other session has an exclusive lock

Exclusive lock == SELECT ... FOR UPDATE            # blocks if some other session has any lock

· Both kinds of locks last until your transaction commits or rolls back.

· Select for update should be followed by an update statement for the same records that appeared in the select for update statement.

Transaction level consistency

All rows used by any statement in transaction A are guaranteed to be unchanged by any other transaction while A is executing.

See warning in the book regarding how a transaction might not see its own changes.

Mysql doesn’t seem to provide any setting that enforces transaction level consistency. 

From http://dev.mysql.com/doc/refman/5.0/en/commit.html:

START TRANSACTION WITH CONSISTENT SNAPSHOT;

The WITH CONSISTENT SNAPSHOT option starts a consistent read for storage engines that are capable of it. This applies only to InnoDB. The effect is the same as issuing a START TRANSACTION followed by aSELECT from any InnoDB table.

Isolation levels

SQL92 defines 4 Isolation levels.  See Fig 6-13 in the text.

Mysql innodb engine seems to support all of these levels – see:

http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html and

http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html

Read uncommitted

show all changes that have happened in any other transaction

Read committed

Show changes that have been committed by another transaction

Repeatable Read

“All consistent reads within the same transaction read the snapshot established by the first read.  This convention means that if you issue several plain (nonlocking) SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other.”  (Default level for innodb)

Serializable

InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE

Data anomalies that can occur depending on the DBMS Isolation level setting:

Dirty read

Reading an uncommitted change made by a different transaction.

Nonrepeatable read

Doing the same read two times in the same transaction where the second read shows updates or deletions committed by a different transaction.

Phantom read

Doing the same read two times where the second read shows new (inserted) records committed by a different transaction.

Figure 6-13 from the text shows which problems can occur with which isolation levels

Examples:

Read Uncommitted Isolation level -- Dirty read possible

S1:

# initialize the table

drop table if exists Conc;

create table Conc (

ID int primary key,

NAME varchar(45)) engine=InnoDB;

insert Conc values (1, 'jb'),(2, 'jb');

#start example

set autocommit=0;

set session transaction isolation level read uncommitted;

begin;

S2:

set autocommit=0;

set session transaction isolation level read uncommitted;

begin;

mysql> select * from Conc;

+----+------+

| ID | NAME |

+----+------+

|  1 | jb   |

|  2 | jb   |

+----+------+

2 rows in set (0.00 sec)

S1:

mysql> update Conc set id=5 where id=1;

Query OK, 1 row affected (0.02 sec)

Rows matched: 1  Changed: 1  Warnings: 0

S2:

mysql> select * from Conc;

+----+------+

| ID | NAME |

+----+------+

|  2 | jb   |

|  5 | jb   |

+----+------+

2 rows in set (0.00 sec)

S1:

mysql> rollback;

Query OK, 0 rows affected (0.03 sec)

S2:

mysql> select * from Conc;

+----+------+

| ID | NAME |

+----+------+

|  1 | jb   |

|  2 | jb   |

+----+------+

2 rows in set (0.00 sec)

Notice S2 saw an uncommitted change made by S1.

Read Committed – non-repeatable read possible

S1:

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

S2:

mysql> set session transaction isolation level read committed;

Query OK, 0 rows affected (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from Conc;

+----+------+

| ID | NAME |

+----+------+

|  1 | jb   |

|  2 | jb   |

+----+------+

2 rows in set (0.00 sec)

S1:

mysql> update Conc set id=5 where id=1;

Query OK, 1 row affected (0.01 sec)

Rows matched: 1  Changed: 1  Warnings: 0

S2:

mysql> select * from Conc;

+----+------+

| ID | NAME |

+----+------+

|  1 | jb   |

|  2 | jb   |

+----+------+

2 rows in set (0.00 sec)

S1:

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

S2:

mysql> select * from Conc;

+----+------+

| ID | NAME |

+----+------+

|  2 | jb   |

|  5 | jb   |

+----+------+

2 rows in set (0.00 sec)

Notice S2 did not see the change until S1 committed.

Repeatable read – phantom read possible

Not quite.  From http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html:

If the transaction isolation level is REPEATABLE READ (the default level), all consistent reads within the same transaction read the snapshot established by the first such read in that transaction. You can get a fresher snapshot for your queries by committing the current transaction and after that issuing new queries.

With READ COMMITTED isolation level, each consistent read within a transaction sets and reads its own fresh snapshot.

So, insert, update, and delete operations in another session are all treated the same by repeatable read isolation level.  Non-repeatable read not possible.

S1:

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

S2:

mysql> set session transaction isolation level repeatable read;

Query OK, 0 rows affected (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from Conc;

+----+------+

| ID | NAME |

+----+------+

|  2 | jb   |

|  5 | jb   |

+----+------+

2 rows in set (0.00 sec)

S1:

mysql> insert Conc values (3, 'jane doe');

Query OK, 1 row affected (0.03 sec)

mysql> commit;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from Conc;

+----+----------+

| ID | NAME     |

+----+----------+

|  2 | jb       |

|  3 | jane doe |

|  5 | jb       |

+----+----------+

3 rows in set (0.00 sec)

S2:

mysql> select * from Conc;

+----+------+

| ID | NAME |

+----+------+

|  2 | jb   |

|  5 | jb   |

+----+------+

2 rows in set (0.00 sec)

Read was still repeatable, even though it was an insert instead of an update.

Serializable – no anomalies possible

S1:

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

S2:

mysql> set session transaction isolation level serializable;

Query OK, 0 rows affected (0.00 sec)

mysql> begin;

Query OK, 0 rows affected (0.00 sec)

mysql> select * from Conc;

+----+----------+

| ID | NAME     |

+----+----------+

|  2 | jb       |

|  3 | jane doe |

|  5 | jb       |

+----+----------+

3 rows in set (0.00 sec)

S1:

mysql> insert Conc values (7, 'mcgee');

(hangs waiting for lock, and eventually:)

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

S1:

mysql> insert Conc values (7, 'mcgee');

(hangs waiting for lock)

S2:

mysql>  insert Conc values (9, 'mcgee');

Query OK, 1 row affected (0.00 sec)

mysql> commit;

Query OK, 0 rows affected (0.01 sec)

S1:

Still hanging and eventually:

Query OK, 1 row affected (17.68 sec)

mysql> select * from Conc;

+----+----------+

| ID | NAME     |

+----+----------+

|  2 | jb       |

|  3 | jane doe |

|  5 | jb       |

|  7 | mcgee    |

|  9 | mcgee    |

+----+----------+

5 rows in set (0.00 sec)

S2:

mysql> select * from Conc;

+----+----------+

| ID | NAME     |

+----+----------+

|  2 | jb       |

|  3 | jane doe |

|  5 | jb       |

|  9 | mcgee    |

+----+----------+

4 rows in set (0.00 sec)

Notice S1 has not yet committed

S1:

commit;

S2:

mysql> select * from Conc;

+----+----------+

| ID | NAME     |

+----+----------+

|  2 | jb       |

|  3 | jane doe |

|  5 | jb       |

|  7 | mcgee    |

|  9 | mcgee    |

+----+----------+

5 rows in set (0.00 sec)