学步园

The ACID properties are one of the cornerstones of database theory. ACID
defines four properties that must be present if a database is considered
reliable: Atomicity, Consistency, Isolation, and Durability. While all four
properties are important, isolation in particular is interpreted with the most
flexibility. Most databases provide a number of isolation levels to choose
from, and many libraries today add additional layers which create even more fine-grained
degrees of isolation. The main reason for this wide range of isolation levels
is that relaxing isolation can often result in scalability and performance
increases of several orders of magnitude.

Improving Application
Performance and Scalability with Terracotta Ehcache

Adobe® Flash® Platform Overview
PDF

Intel® SOA Expressway
Performance Comparison to IBM® DataPower XI50

Free $40 SOA Demystified Book
Offer

Business Benefits of Open
Source SOA

Serializable consistency is one of the oldest and highest isolation levels
that is generally available, and many choose it due to the simple programming
model it provides - only one transaction can execute at a time against a given
resource, and many potential sources of problems are removed. However, most
applications (particularly web applications) cannot assume this very high level
of isolation because it is impractical from the end user perspective - any
application with a non-trivial number of users would quickly experience delays
of several minutes accessing shared resources, which would rapidly reduce the
number of users of that application back to a trivial number. Weak and eventual
consistency are common in large distributed data sources such as the Web, and
several very large and successful web-based applications (e.g. eBay and Amazon)
have shown that optimistic weak consistency is much more scalable than
traditional pessimistic mechanisms. This article takes a look at eight
different isolation levels that you can use to potentially gain more
performance and scalability in your applications by learning to relax data
consistency constraints.

The main goal of concurrency control is to ensure transactions are
isolated and do not interfere with one another. Higher degrees of isolation are
achieved at the expense of potential performance gains. Concurrency control is
implemented by a pessimistic or optimistic mechanism. Most relational
databases, which are write-optimised, use a pessimistic mechanism. Pessimistic
mechanisms use locks and may block operations or use some form of conflict
detection. Pessimistic blocking is done when a table, page, or row has been
modified, preventing other transactions from accessing potentially modified
resources. However, optimistic mechanisms do not use any locks and rely solely
on conflict detection to maintain transaction isolation. Conflict detection, as
used by optimistic mechanisms, permits all read operations and verifies
consistency at the end of the transaction. If a conflict is detected then the
transaction is rolled back or repeated. Most web servers are read-optimised and
thus use an optimistic mechanism. By permitting all read operations, optimistic
mechanisms can achieve a higher read and write throughput while still
preserving data consistency when resources are not continually changing.

The isolation levels listed below are here to help Web developers better
understand the constraints placed on their programming models, and to engage
system architects and developers in discussions to choose the most efficient
isolation levels while maintaining necessary data consistency. They are listed
from the least isolated (Read Uncommitted) to the most isolated
(Serializability).

1 Read
Uncommitted

Read uncommitted isolation level requires little isolation between
transactions. Every read operation may see pending write operations from any
transaction (dirty reads). However, committed write operations must have a
serial order to prevent dirty writes. A pessimistic mechanism will block
conflicting write operations until others are committed or rolled back. An
optimistic mechanism will not lock and will allow everything to go through. If
a connection is rolled back, all other connections that made subsequent
modifications to the same data will also be rolled back. Shared caches are
permitted in this level without validation. This isolation level is best used
when transactions are not needed (such as a read-only dataset) or are modified
with exclusive access to the database.

Example: An archive database that is only
updated while offline, or an audit/logging table that is not used within a
transaction

2 Read Committed

Read committed may read any committed state of the system and may be
cached without validation (mixed states) as long as changes in the current
connection are reflected in the results. Pessimistic mechanisms implement this
as a Monotonic View. Optimistic transactions store all changes in isolation,
making them only available to itself until committed. Read committed is
implemented with an overly optimistic mechanism that delays writing all changes
until the transaction is committed. This form of optimistic isolation permits
complicated write operations without blocking read operations and has no
validation schema. Shared caches are permitted only for committed states. This
isolation level is best used when older values are permitted in results and
transactions are only use for write operations.

Example: An online forum, when the absolute
latest postings may not necessarily be shown and posts don't conflict with each
other

3 Monotonic View

Monotonic view is an extension to read committed where
transactions observes a monotonically increasing state of the database as it
executes. In this level, a pessimistic transaction may be blocked during read
operations if there is an outstanding write transaction. Optimistic
transactions behave like read committed, keeping their changes in isolation,
but validate their cache to ensure it is still valid. Periodically synchronized
database clones are permitted in this level. This isolation level is best used
when transactions are not needed or transactions only contain write operations.

Example: A user preference tables that are
modified only by one person

4 Snapshot Reads

Snapshot Reads extends monotonic view and guarantees that query results reflect a consistent snapshot of the
database. A pessimistic mechanism will block other write operations from
affecting the results while they are being read. An optimistic mechanism will
allow other write operations and inform the reading transaction if any of the
results have changed and may roll it back. To implement an optimistic
mechanism, a validation must be performed at the end of the read operation to
detect if any concurrent write operations modified the result, and if so the
result maybe repeated or rolled back. This validation may simply check if write
operations occurred in the same table, or it might check the query results for
the changes. This optimistic isolation level can detect conflicts easily and
favours write operations, while permitting concurrent read operations. This
level permits periodically synchronized database clones so long as they provide
snapshot reads. It is best used when write operations are low or unexpected to
conflict with concurrent read operations and when query results need to be
consistent.

Example: A currency conversion or lookup
table that is queried more often then it is modified and only the newest values
are kept,

5 Cursor
Stability

Cursor Stability isolation extends read
committed and is the default isolation level of many relational databases. In this
isolation level, a pessimistic transaction must indicate which records it will
modify when reading them, if done in a separate statement. This is often done
using 'FOR UPDATE' keywords appended to the end of a 'SELECT' query. In this
case, other conflicting read or write pessimistic transactions will be blocked until
the transaction is finished. An optimistic transaction tracks the version
number of all modified records/entities to be verified when committed. This is
the most popular optimistic isolation level and is provided by all major
object-relational mapping libraries. In the Java Persistence API, this level
can closely be achieved using FLUSH_ON_COMMIT (although queries may not reflect
local changes), and if a conflict is detected an OptimisticLockException is
thrown. This isolation can also be used with the HTTP headers If-Match or
If-Unmodified-Since that compare a previous resource's version or time-stamp
before updating. This level of isolation is best used for entities that are
modified based on external information (not read from the database) and changes
must not overwrite each other.

Example: A shared company directory or a wiki

6 Repeatable Read

Repeatable Read isolation extends cursor stability and
guarantees that any data read within the transaction will not be modified or
removed during the transaction. A pessimistic transaction will acquire read
locks on all records and block other transactions from modifying them. An
optimistic transaction will track all records or entities and verify they have
not been modified when committed. This level of isolation is best used when
entity states can affect other entities and transactions are made up of read
and write operations.

Example:
An order-tracking database, where values are read from one entity and used to
compute values for other entities.

7 Snapshot Isolation

Snapshot isolation extends snapshot reads and repeatable read and guarantees that all read operations made in a transaction will see a
consistent snapshot of the database. Any read operation performed in a
transaction will have the same result regardless of whether it was performed
earlier or later in the transaction. This differs from repeatable read
isolation because it prevents phantom reads (range query results changing).
This level is supported by many relational databases in the form of multi-version
concurrency control (maybe called SERIALIZABLE), which is pessimistically
implemented using a combination of locks and conflict detection. In this level,
transactions must be prepared to be rolled back due to conflicts from either a
pessimistic mechanism or an optimistic mechanism. A pessimistic mechanism will
try to reduce the chances of a conflict by locking resources, but must merge
changes when transactions are committed. An optimistic mechanism may also use a
multi-version concurrency control, but would not block other transactions from
engaging in potentially conflicting operations, instead it would roll back
transactions that were found to conflict. This level of isolation is best used
for transactions that read and modify multiple records.

Example: A workflow system, with rules based
on the state of the system.

8 Serializability

Serializability is an extension of snapshot
isolation that specifies all transactions must occur as if they had executed
serially, one after the other. A pessimistic mechanism acquires range locks for
all evaluated queries, preventing write operations from affecting these
results. An optimistic mechanism tracks all evaluated queries and either uses a
backwards validation scheme or a forwards validation scheme at the end of the
transaction to detect if any concurrent write operations affect concurrent read
operations, and if so, rolls back all but one of the conflicting transactions.
In this isolation level, the apparent state of the system by any committed
transaction will not have changed. This level of isolation is used for
transactions that require complete data consistency.

Example: An accounting system that performs
range queries to compute new values.

Summary

Below is a summary of the isolation levels outlined in this article, to
help you find the level that is most appropriate for your application.

Types of possible collisions between transactions in different isolation
levels:

Optimistic requirements for different isolation levels:

Data consistency is vital in database applications -- it allows developers
to make sense of data within a concurrent environment. Although strong
consistency levels such as serializability provide a simple programming model,
they can cause excess overhead, blocked operations, or transaction rollbacks
and may be unnecessary for many applications. Being aware of other, potentially
more appropriate isolation levels can help ensure that developers and system
architects understand the data consistency needs, while balancing performance
tradeoffs.