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.
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, you can do:
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:
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.
126.96.36.199. 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.
188.8.131.52. 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.
184.108.40.206. 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.
|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:
One of the transactions aborts. All work in the victim transaction is rolled back.
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.
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
Joins, Unions, and Views
Storing Small Amounts of Data
Locking and Performance
Users and Administration