Transaction Management-Databases-Lecture 08 Slides-Computer Science, Slides of Database Management Systems (DBMS)

Download Transaction Management-Databases-Lecture 08 Slides-Computer Science and more Slides Database Management Systems (DBMS) in PDF only on Docsity!

1

Lecture 08:

Transaction management

overview

www.cl.cam.ac.uk/Teaching/current/Databases/

Today’s lecture

• Why do we want concurrent execution of

user programs?

• What properties might we wish for?

• What‟s a transaction?

• What are the problems when interleaving

transactions?

• How might we overcome these?

Transactions cont.

• Thus a transaction is the DBMS‟s abstract view

of a user program: a series of reads/writes of

database objects

• Users submit transactions, and can think of each

transaction as executing by itself

• The concurrency is achieved by the DBMS, which interleaves actions of the various transactions
• Issues:
• Interleaving transactions, and
• Crashes!

Goal: The ACID properties

• Atomicity : Either all actions are carried out, or

none are

• Consistency : If each transaction is consistent,

and the database is initially consistent, then it is

left consistent

• Isolation : Transactions are isolated, or

protected, from the effects of other scheduled

transactions

• Durability : If a transactions completes

successfully, then its effects persist

AAtomicity cont.

• A DBMS ensures atomicity by undoing

the actions of partial transactions

• To enable this, the DBMS maintains a

record, called a log , of all writes to the

database

• The component of a DBMS responsible for

this is called the recovery manager

Consistency

• Users are responsible for ensuring transaction consistency - when run to completion against a consistent database instance, the transaction leaves the database consistent
• For example, consistency criterion that my inter-account- transfer transaction does not change the total amount of money in the accounts!
• Database consistency is the property that every transaction sees a consistent database instance. It follows from transaction atomicity, isolation and transaction consistency

Integrity Constraints!

Durability

• DBMS uses the log to ensure durability

• If the system crashed before the changes

made by a completed transaction are

written to disk, the log is used to

remember and restore these changes

when the system is restarted

• Again, this is handled by the recovery

manager

Transactions and

schedules

• A transaction is seen by the DBMS as a

series, or list, of actions

• Includes read and write of objects
• We‟ll write this as R(o) and W(o) (sometimes

RT(o) and WT(o) )

• For example

T1: [R(a), W(a), R(c), W(c)]

T2: [R(b), W(b)]

• In addition, a transaction should specify as

its final action either commit , or abort

Schedules cont.

• A complete schedule is one that contains

an abort or commit action for every

transaction that occurs in the schedule

• A serial schedule is one where the

actions of different transactions are not

interleaved

Serialisability

• A serialisable schedule is a schedule

whose effect on any consistent database

instance is identical to that of some

complete serial schedule

• NOTE:

• All different results assumed to be acceptable
• It‟s more complicated when we have

transactions that abort

• We‟ll assume that all „side-effects‟ of a

transaction are written to the database

16

WR conflicts

• Transaction T2 reads a database object

that has been modified by T1 which has

not committed

T1: R(a),W(a), R(b),W(b),C T2: R(a),W(a),R(b),W(b),C

Debit € 100 from a

Credit € 100 to b

Read a and b and add 6% interest

“ Dirty read ”

RW conflicts

• Transaction T2 could change the value of an

object that has been read by a transaction T1,

while T1 is still in progress

T1: R(a), R(a), W(a), C T2: R(a),W(a),C

T1: R(a), W(a),C T2: R(a), W(a),C (^) A is 4 

Read A (5) Write 5+1=

Read A (5) Write 5-1=

“Unrepeatable Read”

Serialisability and aborts

• Things are more complicated when

transactions can abort

T1:R(a), W(a), Abort T2: R(a),W(a),R(b),W(b),C

Deduct € 100 from a

Add 6% interest to a and b Can’t undo T It’s committed 

Strict two-phase locking

• DBMS enforces the following locking protocol:
  • Each transaction must obtain an S ( shared ) lock before reading, and an X ( exclusive ) lock before writing
  • All locks held by a transaction are released when the transaction completes
  • If a transaction holds an X lock on an object, no other transaction can get a lock (S or X) on that object
• Strict 2PL allows only serialisable schedules