Isolation levels and concurrency

UPDATE employee SET salary = 31650WHERE empno = '000090'

SELECT * FROM employee

SELECT * FROM employeeWHERE empno = '000090' 

UPDATE employee SET salary = 30100WHERE empno = '000090'

SELECT * FROM employee WHERE salary > 30000 

INSERT INTO employee(empno, firstnme, midinit,lastname, job,salary) VALUES ('000350', 'NICK','A','GREEN','LEGAL COUNSEL',35000)

Isolation levels and concurrency

Derby provides four transaction isolation levels. Setting the transaction isolation level for a connection allows a user to specify how severely the user’s transaction should be isolated from other transactions.

For example, it allows you to specify whether transaction A is allowed to make changes to data that have been viewed by transaction B before transaction B has committed.

A connection determines its own isolation level, so JDBC provides an application with a way to specify a level of transaction isolation. It specifies four levels of transaction isolation. The higher the transaction isolation, the more care is taken to avoid conflicts; avoiding conflicts sometimes means locking out transactions. Lower isolation levels thus allow greater concurrency.

Inserts, updates, and deletes always behave the same no matter what the isolation level is. Only the behavior of select statements varies.

To set isolation levels you can use the JDBC Connection.setIsolationLevel method or the SQL SET ISOLATION statement. The names of the isolation levels are different, depending on whether you use a JDBC method or SQL statement. Mapping of JDBC transaction isolation levels to Derby isolation levels shows the equivalent names for isolation levels whether they are set through the JDBC method or an SQL statement.
Table 1. Mapping of JDBC transaction isolation levels to Derby isolation levels Isolation levels for JDBC Isolation levels for SQL
Connection.TRANSACTION_READ_ UNCOMMITTED (ANSI level 0) UR, DIRTY READ, READ UNCOMMITTED
Connection.TRANSACTION_READ_COMMITTED (ANSI level 1) CS, CURSOR STABILITY, READ COMMITTED
Connection.TRANSACTION_REPEATABLE_READ (ANSI level 2) RS
Connection.TRANSACTION_SERIALIZABLE (ANSI level 3) RR, REPEATABLE READ, SERIALIZABLE

These levels allow you to avoid particular kinds of transaction anomalies, which are described in Transaction Anomalies.
Table 2. Transaction Anomalies Anomaly Example
Dirty Reads

A dirty read happens when a transaction reads data that is being modified by another transaction that has not yet committed.
Transaction A begins.

UPDATE employee SET salary = 31650
WHERE empno = ‘000090′

Transaction B begins.

SELECT * FROM employee

(Transaction B sees data updated by transaction A. Those updates have not yet been committed.)
Non-Repeatable Reads

Non-repeatable reads happen when a query returns data that would be different if the query were repeated within the same transaction. Non-repeatable reads can occur when other transactions are modifying data that a transaction is reading.
Transaction A begins.

SELECT * FROM employee
WHERE empno = ‘000090′

Transaction B begins.

UPDATE employee SET salary = 30100
WHERE empno = ‘000090′

(Transaction B updates rows viewed by transaction A before transaction A commits.) If Transaction A issues the same SELECT statement, the results will be different.
Phantom Reads

Records that appear in a set being read by another transaction. Phantom reads can occur when other transactions insert rows that would satisfy the WHERE clause of another transaction’s statement.
Transaction A begins.

SELECT * FROM employee
WHERE salary > 30000

Transaction B begins.

INSERT INTO employee
(empno, firstnme, midinit,
lastname, job,
salary) VALUES (’000350′, ‘NICK’,
‘A’,'GREEN’,'LEGAL COUNSEL’,35000)

Transaction B inserts a row that would satisfy the query in Transaction A if it were issued again.

The transaction isolation level is a way of specifying whether these transaction anomalies are allowed. The transaction isolation level thus affects the quantity of data locked by a particular transaction. In addition, a DBMS’s locking schema might also affect whether these anomalies are allowed. A DBMS can lock either the entire table or only specific rows in order to prevent transaction anomalies.

When Transaction Anomalies Are Possible shows which anomalies are possible under the various locking schemas and isolation levels.
Table 3. When Transaction Anomalies Are Possible Isolation Level Table-Level Locking Row-Level Locking
TRANSACTION_READ_UNCOMMITTED Dirty reads, nonrepeatable reads, and phantom reads possible Dirty reads, nonrepeatable reads, and phantom reads possible
TRANSACTION_READ_COMMITTED Nonrepeatable reads and phantom reads possible Nonrepeatable reads and phantom reads possible
TRANSACTION_REPEATABLE_READ Phantom reads not possible because entire table is locked Phantom reads possible
TRANSACTION_SERIALIZABLE None None

The following java.sql.Connection isolation levels are supported:

* TRANSACTION_SERIALIZABLE

RR, SERIALIZABLE, or REPEATABLE READ from SQL.

TRANSACTION_SERIALIZABLE means that Derby treats the transactions as if they occurred serially (one after the other) instead of concurrently. Derby issues locks to prevent all the transaction anomalies listed in Transaction Anomalies from occurring. The type of lock it issues is sometimes called a range lock.
* TRANSACTION_REPEATABLE_READ

RS from SQL.

TRANSACTION_REPEATABLE_READ means that Derby issues locks to prevent only dirty reads and non-repeatable reads, but not phantoms. It does not issue range locks for selects.
* TRANSACTION_READ_COMMITTED

CS or CURSOR STABILITY from SQL.

TRANSACTION_READ_COMMITTED means that Derby issues locks to prevent only dirty reads, not all the transaction anomalies listed in Transaction Anomalies.

TRANSACTION_READ_COMMITTED is the default isolation level for transactions.
* TRANSACTION_READ_UNCOMMITTED

UR, DIRTY READ, or READ UNCOMMITTED from SQL.

For a SELECT INTO, FETCH with a read-only cursor, full select used in an INSERT, full select/subquery in an UPDATE/DELETE, or scalar full select (wherever used), READ UNCOMMITTED allows:
o Any row that is read during the unit of work to be changed by other application processes.
o Any row that was changed by another application process to be read even if the change has not been committed by the application process.

For other operations, the rules that apply to READ COMMITTED also apply to READ UNCOMMITTED.

在很早期的Internet邮件列表 已经存在, 而且当时给我们提代不少的Q&A ，问与答的帮助。
可后来，由于网络广告的肆意深入“流传”。 让原本纯洁的MAILLIST不再这么可爱。更有些攻击性的邮件的存丰，更是一度不再出现。 加上简单，漂亮的QA页面的出现。 更是几乎淡出。

最近这几天在研究一个APACHE FOUNDATION下的DERBY数据库。
也就是刚并入JAVA没多久的JAVADB。 却碰到一大堆无法预想得到问题和麻烦。
找中文资料，几乎为0。 找英文的。 从DB2，DERBY，APACHEDB，到JAVADB。是少之又少。看DOC， 有些问题真的不容易找到。 还好加了一个邮件列表。地球另一半的同志者还真热心，还能一一回答了我的问题。虽然最终还需要去研究一些细节。 但在国内，目前还真没碰到这么多热心人。现在的国人也都向“钱”看齐了。呵呵。

好，这就是最近让人怀念的邮件列表，又继续发挥其余热了。