Databases Reference
In-Depth Information
issue checkpoints. There are also database server events and conditions that will
cause a checkpoint to be issued, including the following:
The active portion of the log is larger than can be efficiently recovered
after an error.
▲
An ALTER DATABASE statement is executed.
▲
The database is backed up.
▲
The server is stopped or shut down.
▲
This is a partial list, but it gives you a good idea of the types of events that
cause a checkpoint to be issued. Transact-SQL also includes a CHECKPOINT
command that manually issues a checkpoint when run. When the checkpoint is
issued, it forces a write of all committed dirty pages to the hard disk.
10.3.2 Managing Locks, Locking, and Transaction Isolation
Lock management through transaction isolation is not unique to SQL Server, but
it is the primary concurrency control method used by SQL Server. SQL Server
2005 supports the four transaction isolation levels defined by the SQL-99 stan-
dard. These are, from least to most restrictive:
Read uncommitted
▲
Read committed
▲
Repeatable read
▲
Serializable
▲
SQL Server supports one additional level that is not part of the SQL-99 stan-
dard, snapshot isolation. The level is set for a connection using the SET TRANS-
ACTION ISOLATION LEVEL statement.
The
read uncommitted
isolation level is the least restrictive, but also pro-
vides the least protection against possible concurrency errors. When running
under this isolation level, transactions do not acquire a shared lock when read-
ing data. Transactions also ignore exclusive locks, meaning that they can read
data that has been locked for modification. Because of this, your transactions can
be affected by dirty reads, nonrepeatable reads, and phantoms.
The
read committed
transaction isolation level is the default database level.
This level prevents a transaction from reading uncommitted changes made by other
transactions. This means that dirty reads are prevented, but nonrepeatable reads
and phantoms are both still possible. That is because it is possible for another
transaction to make changes to the data and commit the changes between reads.
The
repeatable read
transaction isolation level prevents both dirty reads and
nonrepeatable reads. This is because it prevents the current transaction from
reading uncommitted data and also prevents other transactions from modifying
data being read by the current transaction until after the transaction completes.
