Transcript Locks - Webcourse

Data Concurrency
David Konopnicki 1997
Revised by Mordo Shalom 2004
1
Agenda
Data concurrency, integrity and consistency
 The automatic locking mechanism in
ORACLE
 Explicit (manual) locking
 ORACLE lock management services

2
What are concurrency and
consistency
In single user database, a user can modify
data without concern of other users
modifying or accessing the same data.
 In multi-user database, several users
accessing the same data items is a problem.
 Transaction: a set of user actions (e.g. SQL
queries) that must be executed together in
some level of isolation.

3
Transactions in ORACLE


Transactions are started automatically.
A transaction is ended by:





Commit (explicitly or by disconnect)
Rollback (explicity or by abort)
A DDL statement which begins a new transaction
A new transaction is started automatically with the
next statement.
Autonomous transactions:


Transactions nested in time
The inner transaction may commit while the outer aborts
4
Error Handling
Statement level rollback: Effects of an
erroneous statement are allways rolled back.
 Resumable Errors (Out of Space, etc.),
Resumable Statements .
 Errors returned to the program may be
handled by it, then the transaction continues,
otherwise it aborts

5
Commit (Regular Transactions)

Before Commit, changes were made to:
 The
database buffers
 Rollback segments
 Redo Log buffers

During Commit:
 The
SCN number of the transaction is written to the
rollback segments.
 Redo log buffers are writen to disc.
 The SCN is written to the redo log. (COMMIT)
 Locks are released
6
Commit (Discrete Transactions)
Short, Nondistributed transactions
 Declared as such by
BEGIN_DISCRETE_TRANSACTION
 Before Commit, changes were made to:

 Special

Redo Log buffers
During Commit:
 Redo
log buffers are writen to disc.
 The SCN is written to the redo log. (COMMIT)
 Database buffers are updated
 Locks are released
7
Types of Rollback
Statement level
 Rollback to savepoint
 Rollback on user request
 Rollback because abnormal process
termination
 Multiple Rollbacks because abnormal instance
termination
 Rollback of incomplete transactions during
recovery.

8
Rollback

Before Rollback, changes were made to:
 The
database buffers
 Rollback segments
 Redo Log buffers

During Rollback:
 The
rollback segments are applied to the database
buffers.
 Locks are released
9
2PC – Two Phase Commit
In distributed transactions.
 Transparent to user.
 First phase: All the servers are put in precommit status (some of them may abort).
 Second phase: If all the servers are in precommit, all of them commit otherwise all of
them roll back.
 RECO process resumes in-doubt 2PC’s
(network failures etc..)

10
Definitions
Data concurrency: Coordination access to
data by several users.
 Data consistency: A user sees a consistent
view of the data i.e. all data committed by
other transactions as of that time and all the
changes made by the user up to that time.

11
General concurrency issues

Some of the problems:
 Inconsistent
reads (in one query)
 Non-repeatable reads (phantoms)
 Dirty reads
 Lost updates
 Destructive DDL operations
12
General locking concepts
Locks are used to prevent destructive
interactions between users accessing the
same resources.
 Resources are:

 User
objects (tables and rows)
 System objects
13
Restrictiveness of locks
Exclusive locks: prohibit the sharing of a
resource
 Share locks: allow sharing

Share locks allow a higher degree of data
concurrency.
14
Deadlocks
Transaction 1
Update EMP
set sal = sal * 1.1
where
empno=1000;
Update EMP
set sal=sal*1.1
where
empno=2000;
DEADLOCK
Time
1
2
3
Transaction 2
Update EMP
set sal = sal * 1.1
where
empno=2000;
Update EMP
set sal = sal * 1.1
where
empno=1000;
ERROR
15
Solving Deadlocks
Local deadlocks are resolved using a waitfor graph.
 In distributed ORACLE, timeout is used.
 Deadlock is solved by rolling-back a
statement in one of the transactions
involved in the deadlock.

16
Lock Escalation
Locks on rows -> locks on table.
 Causes a lot of deadlocks
 Therefore: ORACLE never escalates locks.

17
Lock conversion

Re-request on an already locked item.
 For
a less restrictive lock: No problem.
 For a more restrictive lock: May wait.
18
Transactions and Data
Concurrency
All locks acquired by statements within a
transaction are hold for the duration of the
transaction.
 That changes made by statements of a
transaction only become visible to other
transactions after the first one is committed.
 The locks acquired by a transaction are
released when it is committed or rolledback.
19

Multi-version consistency model

ORACLE provides two levels of readconsistency:
 Statement
level read consistency that is always
enforced.
 Transaction level read consistency.
read-only transaction.
 set manually using exclusive locks.

20
Rollback Segment (for read
consistency)
Current SCN
1023
1023
1024
1024
1023
Rollback
Segments
1023
1024
1023
1023
21
ISO-SQL92 Isolation Levels
Isolation
Dirty Nonrepeatable Phantom
Level
Read Read
Read
Read
Y
Y
Y
Uncommitted
Read
N
Y
Y
Committed
Repeatable
N
N
Y
Read
Serializable
N
N
N
22
Oracle 9i Isolation Levels
Isolation Level
Nonrepeatable and
Phantom
Reads
Read Committed
Y
Serializable/
Read Only
N
Serializable Transactions may get:
Can not serialize access … error.
23
How ORACLE locks data
Readers do not wait for readers.
 Writers do not wait for readers.
 Writers only waits for writers if they
attempt to update the same rows at the same
time.
 ORACLE automatically locks data at the
lowest level of restrictiveness.

24
Two types of locks

DML: protects data in tables.
 Row
locks.
 Table locks.

DDL: protects the schema.
25
Row locks
Inserted when a row is modified by
INSERT, UPDATE, DELETE and SELECT
... FOR UPDATE.
 Acquiring a row lock necessitates the
acquisition of a table lock (DDL).
 A row lock is always exclusive (remember,
only writers wait for each other).

26
Table locks - DML
DML statement
Row locks?
Mode of table lock
Select ... From ...
No
None
Insert ... Into ...
Yes
RX
Update Table ...
Yes
RX
Delete From Table ...
Yes
RX
Select From Table ...
For Update of ...
Yes
RS
27
Locks Compatibility
RS
RX
S
SRX
X
RS
Y
Y
Y
Y
N
RX
Y
Y
N
N
N
S
Y
N
Y
N
N
SRX
Y
N
N
N
N
X
N
N
N
N
N
28