Transcript Locks

Locking
Used to manage concurrent access.
Lock denies access to sub-set of database to
a set of transactions.
Can ensure serialisability of transactionsdepending on isolation level
Serializability and
Isolation
Serializability means running a set of
concurrent transactions resulting in a
database state that would be achieved if
the set of transactions were executed
serially in order.
Transactions run in complete isolation of
one another, although there can be more
than one transaction running at any time.
However, many transactions do not
require full isolation.
Isolation level
The level at which a transaction is
prepared to accept inconsistent data is
termed the Isolation level.
The Isolation level required by an
application determines the locking
behavior SQL Server uses.
SQL-92 defines the following Isolation
levels, all of which are supported by SQL
Server:
•Read uncommitted
•Read committed (default).
•Repeatable read.
•Serializable (transactions are completely
Isolated).
Isolation level
Dirty read
Nonrepeatable
read
Phantom
Read
uncommitted
Yes
Yes
Yes
Read committed
No
Yes
Yes
Repeatable read
No
No
Yes
Serializable
No
No
No
Transactions must be run at an isolation
level of ‘repeatable read’ or higher to
prevent lost updates.
If the two transactions update rows using
a single UPDATE statement and do not
base the update on the previously
retrieved values, lost updates cannot
occur at the default Isolation level of read
committed.
Granularity
Lock operate on data items
Data item can be a field to whole database
Lock Type
Shared
A transaction can read but not update
the data item
Exclusive
Transaction can both read and update
data item
A number of transactions could have
shared locks on a data item.
Only one transaction will have an
exclusive lock on a data item
Transaction locks data item before
accessing it.
If item not locked access is granted.
If item locked, then if it is a shared lock and
request is for a shared lock then request
granted.
If item exclusively locked then requesting
transaction waits.
Transaction holds lock until it releases it
(fail or commit).
Only when lock released are any write
operations results visible to other
transactions
SQL Server has several lock modes:
•Shared
•Update
•Exclusive
•Intent
•Schema
•Bulk Update
SQL Server controls how the lock modes
interact.
For example, an exclusive lock cannot be
obtained if other connections hold shared
locks on the resource.
Shared (S)
Used for operations that do not change or
update data (read-only operations), such
as a SELECT statement.
Update (U)
Used on resources that can be updated.
Deadlock Avoidance-see later
Exclusive
(X)
Used for data-modification operations,
such as INSERT, UPDATE, or DELETE.
Ensures that multiple updates cannot be
made to the same resource at the same
time.
Intent
What transaction intends to do: intent
shared (IS), intent exclusive (IX), and
shared with intent exclusive (SIX).
Prevents other transactions acquiring
exclusive locks and preventing the intent.
Schema
Used when an operation dependent on the
schema of a table is executing. The types
of schema locks are: schema modification
(Sch-M) and schema stability (Sch-S).
Bulk
Update
(BU)
Used when bulk-copying data into a table
and the TABLOCK hint is specified.
Upgrade and DownGrade
Upgrade
A transaction may be permitted to upgrade a
shared lock to exclusive lock.
Allows transaction to examine data before
deciding to do an update.
Downgrade
Transactions may be permitted to downgrade
an exclusive lock to a shared lock.
If upgrade and downgrade not available then
transactions may need to unnecessarily lock
substantial amounts of data in case they need
updating
Deadlock
‘Impasse that may result when two or
more transactions are each waiting for
locks to be released that are held by the
other’
Begg and Connolly
Can only be resolved by failing one of the
transactions.
Transaction processor should
automatically restart failed transaction
when locks released.
Can be prevented, but often easier to
allow it to occur, detect it, and recover.
Upgrade ExampleSQL*Server
Update locks prevent a common form of deadlock.
e.g.a transaction reading a record, acquires a
shared lock, and then modifies the data.
This requires lock upgrade to an exclusive lock.
If two transactions acquire shared locks on a
resource and then attempt to update data
concurrently, one transaction attempts the lock
upgrade to an exclusive lock.
The shared lock to exclusive lock upgrade must
wait
This is because the exclusive lock for one
transaction is not compatible with the shared lock
of the other transaction; a wait occurs.
The second transaction attempts to acquire an
exclusive lock for its update.
Because both transactions are upgrading to
exclusive locks and they are each waiting for
the other transaction to release its shared
locking, a deadlock occurs.
To avoid this potential deadlock problem, update
locks are used.
Only one transaction can obtain an update lock to
a resource at a time.
If a transaction modifies a resource, the update
lock is upgraded to an exclusive lock
Otherwise, the lock is downgraded to a shared
lock.ode
Two Phase Locking
Transaction divided into two phases
Growing Phase
Acquires all locks needed. No locks
released. Upgrades allowed. No
downgrading allowed.
Shrinking Phase
Locks released but no new locks
allowed. No upgrades allowed.
Downgrading allowed.
SQL Syntax
SET TRANSACTION ISOLATION
LEVEL
{
READ COMMITTED
| READ UNCOMMITTED
| REPEATABLE READ
| SERIALIZABLE
}
Can be executed before a transaction to
request locking behaviour via the Isolation
Level
Exec sp_lock
Provides current locking info.
Exercise
Refer to presentation on transaction
(concurrency problems)
Rework the examples given to illustrate
the problems, using shared and exclusive
locks and two phase locking to eliminate
the problems.
(Answers in Connolly and Begg p. 566)