Database Reference
In-Depth Information
Concurrency
Concurrency is the term used to describe how a database system such as SQL Server
2014 deals with multiple concurrent users. It is needed to ensure that when one
person makes changes to the data, those changes do not adversely affect those of
another user. This is sometime called concurrency control.
There are two types of concurrency control:
Pessimistic concurrency control : A system-implemented locking system
prevents users from changing data that affects other users. When a user
performs an action, it causes a lock to be applied. The other users cannot
perform any action that would conlict with that lock until that lock is released.
Optimistic concurrency control : When reading data, users do not put
locks on data. When a user makes changes to the data, the system checks if
another user changed the data after it was read. If the data was changed, an
error is raised.
• SQL Server supports a range of concurrency control. Users specify the type of
concurrency control by selecting transaction isolation levels for connections
or concurrency options for cursors.
Some concurrency terminologies
Some concurrency terminologies are listed as follows:
Dirty read : Dirty reads can occur when a second transaction retrieves a
row that is already being updated by a transaction. The second transaction
reads data that has not been committed and may possibly be changed by
the transaction updating the row.
Non-repeatable read : Non-repeatable reads occur when a second transaction
accesses the same row several times and reads different data each time.
Phantom read : Phantom reads occur when a row insertion or row deletion
action is performed against a row that belongs to a range of rows being read
by a transaction.
SQL Server 2008 R2 and higher support the following transaction isolation levels:
Read uncommitted : This allows dirty reads, non-repeatable reads, and
phantom reads.
Read committed : This doesn't allow dirty reads but does allow non-
repeatable and phantom reads.
 
Search WWH ::




Custom Search