Hack 64. Determine Your Isolation Level

Your isolation level dictates how locks are obtained in a transaction. A higher isolation level results in fewer concurrency problems at the expense of performance.

If two transactions are competing for the same resource, the database system needs rules to tell it how to resolve the competition. The rules depend on the transaction isolation level. You need to understand these rules, as they can affect system behavior.

9.1.1. Autocommit

To be able to investigate transactions and concurrency, you need to issue the transaction control commands, COMMIT and ROLLBACK, explicitly. To do this you need to ensure that autocommit mode is off.

In autocommit mode, the database performs an implicit COMMIT after every SQL statement. Each statement is a single transaction while you are in autocommit mode. Although this is adequate in many situations, sometimes you need to execute several SQL statements as a single transaction.

In Oracle, at the beginning of an SQL*PLUS session, you can do:

SET AUTOCOMMIT OFF

In MySQL, isolation is not enforced on tables using the MyISAM engine. You can specify engine=innoDB on the CREATE TABLE statement to ensure that transactions are respected for a given table.

In MySQL, you can do:

START TRANSACTION;

In PostgreSQL, autocommit is also known as chained mode. It is on by default, and you need to start each transaction with the following code to switch it off:

BEGIN TRANSACTION;

The same statement works for SQL Server:

BEGIN TRANSACTION
GO

 

9.1.2. Concurrency Issues

Transactions can affect one another in three basic ways: with phantom reads, nonrepeatable reads, and dirty reads.

9.1.2.1. Phantom reads

A phantom read occurs when two identical queries are executed within a single transaction, but the set of rows returned from the second query is different from the first. Consider these two transactions:

Transaction A Transaction B
mysql> START TRANSACTION;
Query OK, 0 rows affected

mysql> SELECT * FROM t;
+------+------+
| x | y |
+------+------+
| 1 | 1 |
| 2 | 1 |
+------+------+
2 rows in set

 mysql> START TRANSACTION;
 Query OK, 0 rows affected

 mysql> INSERT INTO t VALUES (3,1);
 Query OK, 1 row affected

 mysql> COMMIT;
 Query OK, 0 rows affected

mysql> SELECT * FROM t;
+------+------+
| x | y |
+------+------+
| 1 | 1 |
| 2 | 1 |
| 3 | 1 |
+------+------+
3 rows in set 

mysql> COMMIT;
Query OK, 0 rows affected 

So, if the first SELECT in transaction A returned two rows, and then the second SELECT returned three rows, you are a victim of a phantom read. Setting the transaction isolation level to SERIALIZABLE will prevent phantom reads.

9.1.2.2. Nonrepeatable reads

A nonrepeatable read occurs when a transaction reads data from the database in one query, and then later in the same transaction a second query reads the same data but finds it has been modified by another committed transaction:

Transaction A Transaction B
mysql> START TRANSACTION;
Query OK, 0 rows affected
mysql> SELECT * FROM t;
+------+------+
| x | y |
+------+------+
| 1 | 1 |
| 2 | 1 |
+------+------+
2 rows in set

 mysql> UPDATE t SET y = 2
 -> WHERE x = 1;
 Query OK, 1 row affected
 Rows matched: 1 Changed: 1 
 mysql> COMMIT;
 Query OK, 0 rows affected

mysql>
mysql> SELECT * FROM t;
+------+------+
| x | y |
+------+------+
| 1 | 2 |
| 2 | 1 |
+------+------+
2 rows in set

mysql> COMMIT; 

So, if the first SELECT in transaction A returned 1 for y, and then the second SELECT showed that y was 2, transaction A has suffered from a nonrepeatable read. If you set the isolation level to REPEATABLE READ or higher, a nonrepeatable read cannot occur.

9.1.2.3. Dirty reads

A dirty read is almost identical to a nonrepeatable read, except that it may arise even without the COMMIT in transaction B. If you set the isolation level to READ COMMITTED or higher a dirty read cannot occur.

9.1.3. Isolation Level

There are four standard types of isolation level, as shown in Table 9-1.

Table 9-1. Standard isolation levels

Isolation level	Phantom read	Nonrepeatable read	Dirty read
SERIALIZABLE(highest level of isolation)	Not possible	Not possible	Not possible
REPEATABLE READ	Possible	Not possible	Not possible
READ COMMITTED	Possible	Possible	Not possible
READ UNCOMMITTED(lowest level of isolation)	Possible	Possible	Possible

With the following command, you can set the isolation level you want:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

Oracle and SQL Server have a default isolation level of READ COMMITTED. DB2 and MySQL have a default of REPEATABLE READ. Not every database system can support all four standard isolation types.

READ UNCOMMITTED effectively switches off all locking in the database, and it can be very hard to design good queries for it. READ COMMITTED or better is usually more than acceptable for everyday queries, but you still should be cautious when writing queries, to make sure they are safe with the degree of query concurrency you intend to use. Note that as the isolation level becomes increasingly strict the overhead in managing each transaction increases. SERIALIZABLE is often considered to have too much of a negative impact on concurrent transaction throughput to be used in general implementations.

9.1.4. Enforcing Isolation

If you attempt a query that might compromise the rules of your current isolation level, the database can respond in one of three ways:

 

Abort

One of the transactions aborts. All work in the victim transaction is rolled back.

 

Hang

One transaction is suspended, as shown in Figure 9-1. From the user's point of view, the system hangs and will not respond until some other transaction finishes and releases the resource required.

 

Copy

The system may return an old copy of the data.

Figure 9-1. Transaction A hangs

 

9.1.5. Querying the Isolation Level

You will see in "Use Pessimistic Locking" [Hack #65], "Use Optimistic Locking" [Hack #66], and "Lock Implicitly Within Transactions" [Hack #67] how to handle concurrency issues without resorting to SERIALIZABLE. But how do you know what isolation level you are in?

It might be useful to detect in which isolation level the current transaction is running. There are commands to do this in MySQL, SQL Server, Oracle, and PostgreSQL.

In MySQL the command is:

mysql> SELECT @@tx_isolation;
+-----------------+
| @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+

In PostgreSQL the command is:

scott=> show transaction isolation level;
 transaction_isolation
-----------------------
 read committed

In SQL Server the command is:

1> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
2> GO
1> BEGIN TRANSACTION
2> GO
1> dbcc useroptions
2> GO
 Set Option Value
 ----------------------- -------------
 textsize 2147483647
 language us_english
 ...
 isolation level serializable

In Oracle you can work out the isolation level using a combination of v$session and v$transaction. These are SYS views, so you may have to ask your SYSDBA user to grant you SELECT permissions on these views. Note also that the query will tell you only the current transaction's isolation level, so you must be in a transaction already for the query to work:

SQL> select sid,serial#,flag,
 2 CASE WHEN bitand(FLAG,268435456) = 0 THEN 'SERIALIZABLE'
 3 ELSE 'READ COMMITTED'
 4 END AS ISOLATIONLEVEL
 5 from V$transaction t,v$session s
 6 where t.addr=s.taddr
 7 AND audsid = USERENV('SESSIONID');

 SID SERIAL# FLAG ISOLATIONLEVEL
---------- ---------- ---------- -----------------
 242 5828 268443139 READ COMMITTED