Docsity
Docsity

Prepare for your exams
Prepare for your exams

Study with the several resources on Docsity


Earn points to download
Earn points to download

Earn points by helping other students or get them with a premium plan


Guidelines and tips
Guidelines and tips

DataBase Development and Implementation Lec12 - Transactions Management, Concurrency Control and Locking, Study notes of Database Management Systems (DBMS)

In this document description about Transactions Management, Concurrency Control

Typology: Study notes

2010/2011

Uploaded on 09/08/2011

rossi46
rossi46 🇬🇧

4.5

(10)

313 documents

1 / 6

Toggle sidebar

This page cannot be seen from the preview

Don't miss anything!

bg1
DBDI 30/05/2007
Lecture_12 /
XatMgmt,ConcCntrl&Locking 1
DBDI / Lecture 12
Transactions Management,
Concurrency Control &
Locking
Dr. Ala Al-Zobaidie
The slides are based on the textbook Database Systems by Thomas Connolly & Carolyn Begg
30/05/2007 DBDI / CocnCntrl 2
Lecture - Objectives
Database Integrity
Function and importance of
transactions.
Properties of transactions.
Locking
Serialization
Deadlock
Approaches for Concurrency
Management
30/05/2007 DBDI / CocnCntrl 3
Database Integrity
Database Integrity:
Reliability & Consistency
Must be maintained:
After HW or SW failures
During multiple simultaneous access to data
Requires:
Concurrency Control mechanism
allows simultaneous access in a controlled manner
Recovery procedure
the process of restoring the Database to a correct state
after a failure
Depends on the concept of a Transaction
}
30/05/2007 DBDI / CocnCntrl 4
Properties of Transactions
Transaction:
a logical unit of work to perform on Database
object
e.g. creation, alteration, or deletion of a record.
Transactions could be simple or complex
Atomicity
All or nothing; indivisible unit
Consistency
Database should stay in a consistent state.
Independence
independent execution of other transactions.
Durability
Permanent effects of update.
A
C
I
D
30/05/2007 DBDI / CocnCntrl 5
Transferring £100 from account 1234 to
Account 4567.
Together they comprise a single
transaction.
Potential problem:
Database crash !!
UPDATE ACCOUNTS
SET BALANCE = BALANCE - 100
WHERE ACCOUNT = 1234;
UPDATE ACCOUNTS
SET BALANCE = BALANCE + 100
WHERE ACCOUNT = 4567;
Transactions Management
Scenario:
transferring money from one account to another in one
bank requires the SQL commands:
30/05/2007 DBDI / CocnCntrl 6
Crash may leave the Database in
inconsistent state (c.f. database integrity)
in our e.g., it would be better if neither of the
commands had been executed.
Transaction integrity
demands that the effects of a transaction
should be either complete or not enacted at
all.
Commit/Rollback protocol
exist to support transaction integrity
Transaction integrity
pf3
pf4
pf5

Partial preview of the text

Download DataBase Development and Implementation Lec12 - Transactions Management, Concurrency Control and Locking and more Study notes Database Management Systems (DBMS) in PDF only on Docsity!

DBDI / Lecture 12

Transactions Management,

Concurrency Control &

Locking

Dr. Ala Al-Zobaidie

The slides are based on the textbook Database Systems by Thomas Connolly & Carolyn Begg

30/05/2007 DBDI / CocnCntrl 2

Lecture - Objectives

  • Database Integrity
  • Function and importance of

transactions.

  • Properties of transactions.
  • Locking
  • Serialization
  • Deadlock
  • Approaches for Concurrency

Management

30/05/2007 DBDI / CocnCntrl 3

Database Integrity

  • Database Integrity:
    • Reliability & Consistency
    • Must be maintained:
      • After HW or SW failures
      • During multiple simultaneous access to data
    • Requires:
      • Concurrency Control mechanism
        • allows simultaneous access in a controlled manner
      • Recovery procedure
        • the process of restoring the Database to a correct state

after a failure

  • Depends on the concept of a Transaction

}

30/05/2007 DBDI / CocnCntrl 4

Properties of Transactions

  • Transaction:
    • a logical unit of work to perform on Database object
    • e.g. creation, alteration, or deletion of a record.
    • Transactions could be simple or complex

Atomicity

All or nothing; indivisible unit

Consistency

Database should stay in a consistent state.

Independence

independent execution of other transactions.

Durability

Permanent effects of update.

A C I D

  • Transferring £100 from account 1234 to Account 4567.
  • Together they comprise a single transaction.
  • Potential problem:
    • Database crash !!

UPDATE ACCOUNTS

SET BALANCE = BALANCE - 100

WHERE ACCOUNT = 1234;

UPDATE ACCOUNTS

SET BALANCE = BALANCE + 100

WHERE ACCOUNT = 4567;

Transactions Management

Scenario:

transferring money from one account to another in one bank requires the SQL commands:

  • Crash may leave the Database in

inconsistent state (c.f. database integrity)

  • in our e.g., it would be better if neither of the commands had been executed.
  • Transaction integrity
  • demands that the effects of a transaction should be either complete or not enacted at all.
  • Commit/Rollback protocol
  • exist to support transaction integrity

Transaction integrity

30/05/2007 DBDI / CocnCntrl 7

  • Commit
    • is when changes to a Database are made

permanent

  • when a Database crashes, any changes that

have not been committed will be lost

UPDATE ACCOUNTS

SET BALANCE = BALANCE - 100

WHERE ACCOUNT = 1234;

UPDATE ACCOUNTS

SET BALANCE = BALANCE + 100

WHERE ACCOUNT = 4567;

COMMIT;

Commit/Rollback protocol

  • We can issue an

explicit commit command when both of these update commands have been

issued

30/05/2007 DBDI / CocnCntrl 8

  • Rollback
    • a mechanism to undo the effects of a transaction.
    • when issued all of the Database changes since last commit are undone.
    • the Rollback in 4 undoes the effect of the

Update in 2

  • because the Update has not been committed 1. Select name from customers where refno = 1; {returns ‘P Abdul’} 2. Update customers set name = ‘J Jones’ where refno =1; 3. Select name from customers where refno = 1; {returns ‘J Jones’} 4. ROLLBACK; 5. Select name from customers where refno = 1; {returns ‘P Abdul’}

Commit/Rollback Protocol /

30/05/2007 DBDI / CocnCntrl 9

  • Suppose we issue a Commit command
  • The Commit command in 3 makes the change permanent
  • The above can be modified to use Save-points or Checkpoints
  • **When a Rollback is issued, it may make reference to a checkpoint
  1. Select name from customers where refno = 1; {returns ‘P Abdul’}
  2. Update customers set name = ‘J Jones’ where refno =1;
  3. COMMIT ;
  4. Select name from customers where refno = 1; {returns ‘J Jones’}
  5. ROLLBACK ;
  6. Select name from customers where refno = 1; {returns ‘J Jones’}**

Commit/Rollback Protocol /

30/05/2007 DBDI / CocnCntrl 10

Checkpoints

  • The point of synchronisation between the Database and transaction log file.
  • All buffers are force-written to secondary storage **1. Select name from customers where refno = 1; {returns ‘P Abdul’}
  1. Update customers set name = ‘J Jones’ where refno = 1;
  2. COMMIT;
  3. Select name from customers where refno = 1; {returns ‘J Jones’}
  4. Update customers set name = ‘P Smith’ where refno = 1;
  5. SAVEPOINT_1;
  6. Update customers set name = ‘A Sharif’ where refno = 1;
  7. ROLLBACK TO SAVEPOINT_1;
  8. Select name from customers where refno = 1; {returns ‘P Smith’}**

Valid Concurrent Transaction / Example 1

108 W(X)

X:=X+M 108

R(X) 105

45 W(Y)

Y:=Y-N 45

R(Y) 50

105 W(X)

X:=X+N 105

R(X) 100

X Y X Y X

Database T1 T Example:

N= 5; M= 3

Is it Serial

Schedule?

Time

Valid Concurrent Transaction / Example 2

45 W(Y)

Y:=Y-N 45

R(Y) 50

108 W(X)

X:=X+N 108

R(X) 103

103 W(X)

X:=X+M 103

R(X) 100

X Y X Y X

Database T1 T Example:

N= 5; M= 3

Is it Serial

Schedule?

Example 1 & 2

are Equivalent

Schedules

Time

30/05/2007 DBDI / CocnCntrl 19

Inconsistent Analysis

Balance = 0

TA {Select Sum (Balance) From Account;}

TB {Update Accounts Set Balance = Balance - 100 Where Accno = 3; Update Accounts Set Balance = Balance + 100 Where Accno = 1;}

Retrieves account 3 Balance = 100

Retrieves account 1 Balance = 100

Retrieves Account 1 Balance = 100 Total = 100

Retrieves Account 2 Balance = 100 Total = 200

  1. Updates account 3

Balance = 0 Total = 200

  1. Retrieves Account 3

Balance = 100 Total = 300

  1. Retrieves Account 4

  2. Updates account 1^ Balance = 200

30/05/2007 DBDI / CocnCntrl 20

Temporary Update / Case 1

108 W(X)

X:=X+M 103

R(X) 100

100 undo

abort

105 W(X)

X:=X+N 105

R(X) 100

X Y X Y X

Database T1 T Example:

N= 5; M= 3

This scenario

is ok.

T1's effects

have been

eliminated.

Time

30/05/2007 DBDI / CocnCntrl 21

Temporary Update / Case 2

108 W(X)

100 undo

X:=X+M 108

R(X) 105

abort

105 W(X)

X:=X+N 105

R(X) 100

X Y X Y X

Database T1 T Example:

N= 5; M= 3

some of T1's

effects have

been retained!

Time

30/05/2007 DBDI / CocnCntrl 22

Temporary Update / Case 3

100 undo

108 W(X)

X:=X+M 108

R(X) 105

abort

105 W(X)

X:=X+N 105

R(X) 100

X Y X Y X

Database T1 T Example:

N= 5; M= 3

T2's effects

have been

lost, even after

commit!

What is this

problem?

Time

DBMS Transaction Subsystem

Locking

  • How to avoid all previous problems?
    • Lock the object to prevent access by other

transactions

  • A transaction releases the object when it

finishes with it

  • Other transactions need to queue until the

object is released

  • The lock could be shared or exclusive

30/05/2007 DBDI / CocnCntrl 25

Shared & Exclusive Locks

• A Shared lock S is placed on an object

that is being accessed for read only

purposes

  • many S locks may be placed
  • an X lock must wait

• An exclusive lock X , when an object is

being altered

  • No other lock may be placed
  • All transactions must wait

30/05/2007 DBDI / CocnCntrl 26

The Locking Protocol

• Relate this to SQL:

• Many ‘read-only’ operations (e.g. Select)

• One ‘update’ operation (e.g. Delete)

• The Lost Update Problem:

  • TA will place an X lock on Account 1234 before it starts update

• The uncommitted dependency:

  • TA will lock TB out from Account 1234 until it has completed the rollback

• The inconsistent analysis:

  • TA will place an S lock on all of the account records.

30/05/2007 DBDI / CocnCntrl 27

Serialization

Release S on Obj

Place S on Obj

Place S on Obj

Place S on Obj

Place X on Obj

Release S on Obj

Release X on Obj1 Place S on Obj

Place X on Obj

Release S on Obj

Release X on Obj

TA: S lock on Obj1; S on Obj2; X on Obj

TB: S lock on Obj1; X on Obj1; S on Obj

• TB has updated Obj1 before TA: Error

Place X on Obj

Denied, Wait for X on Obj

30/05/2007 DBDI / CocnCntrl 28

Serialisation

• the effects should be the same as if they

operated in a purely serial manner

• Serializable transactions:

– when the conflicting requests are presented in

the same order as if the transactions were

enacted consecutively

• Hence, TA should place X lock from start

Two-Phase Locking

Place X on Obj

Place X on Obj

Place S on Obj

Place X on Obj

Release all locks^ Wait

Place S on Obj

TA: S lock on Obj1; S on Obj2; X on Obj

TB: S lock on Obj1; X on Obj1; S on Obj

• Acquiring locks phase & releasing locks phase

• Two-Phase locking

Release all locks

Request X not S Requests denied, Wait

Wait

Problems with Locking

• Appropriate locking can guarantee

correctness, However, it also introduces

potential undesirable effects:

– Deadlock

  • No transactions can proceed; each waiting on lock held by another.

– Starvation

  • One transaction is permanently "frozen out" of access to data.

– reduced performance

  • Locking introduces delays while waiting for locks to be released.