Lecture 8Transactions & Concurrency

UFCE8K-15-M: Data Management

Transactions

o ‘Transaction’ in a DBMS means an elemental unit of work which is either completed in full or fails totally

o DB Transactions are not the same as a user transaction such as ‘place an order’

o Each transaction hasobegin transactiono some work - reads and writes to the dbo either COMMIT (make changes permanent)oor ROLLBACK (destroy all evidence of work)

UFIE8K-15-M Data Management 2014/15 2

ACID

o (A) Atomicityo Transaction is indivisible unit of work - can’t partly succeed

o (C) Consistencyo Transaction (failed or completed) must leave DB in a

consistant state o (I) Isolation

o Each transaction must appear to run in isolation to any other transactions

o (D) Durabilityo Work done must be permanent

UFIE8K-15-M Data Management 2014/15 3

Implementing ACID

o (A) Atomicityo system must be able to undo work if transaction fails - e.g. due to

failed integrity constrainto system could record the before state of an record and restore

these when rollback requiredo (C) Consistency

o fail if an integrity or transaction constraint violatedo (I) Isolation

o concurrency problem >>o (D) Durability

o DB backup and recovery, transaction logging

UFIE8K-15-M Data Management 2014/15 4

Concurrency Control

o If transactions could be serialised - executed one at a time - each would execute in Isolation

o but this would slow the system down - most of the time is spent waiting for disk access

o If not prevented, interaction between transactions can cause anomalies

UFIE8K-15-M Data Management 2014/15 5

Lost Update

o – bot– read(x)– x=x+1– write(x)– commit

o if x=2 at start, it should be 4 at end

o T2

– bot– read(x)– x=x+1– write(x)– commit

o but its only 3!

UFIE8K-15-M Data Management 2014/15 6

Dirty Read

o T1– bot– read(x)– x=x+1– write(x)

– abort

o x should be 3

o T2

– bot

– read(x)– x=x+1– write(x)– commit

o but it is 4!

UFIE8K-15-M Data Management 2014/15 7

Inconsistent Read

o T1– bot– read(x)

– read(x)– commit

o T1 sees different values of x during its execution

o T2– bot– read(x)– x=x+1– write(x)– commit

UFIE8K-15-M Data Management 2014/15 8

Ghost update

o x+y must = 100 o T1

– bot– read(y)

– read(x)– ? x+y=90

– commit

o T2– bot– read(y)– read(x)– x=x -10– write(x)– y=y+10– write(y)– commit

o now x+y = 100 again

UFIE8K-15-M Data Management 2014/15 9

Scheduling

o A schedule is a sequence of operations (reads or writes) from multiple transactions

o Reads and writes are assumed to be atomic themselveso The Scheduler tries to create a schedule which preserves

Isolationo Serial schedule puts one transaction after another, but this

will be inefficiento Need to find equivalent, shorter schedules

UFIE8K-15-M Data Management 2014/15 10

Pessimistic/Optimistic

o Pessimistico assume transactions will interact and prevent ito Lockingo Timestamping

o Optimistico assume transactions will NOT interact but take action if

they do

UFIE8K-15-M Data Management 2014/15 11

Locking

o Read lock (shared lock)o any number can read but no one can writeo readers need counting

o Write lock (exclusive lock)o no one else can read or write

o Unlock - drop the locko Transaction waits if resource already lockedo Locks held in DB - lock tableo Lock granularity

o best if only a record is locked but lock table large

UFIE8K-15-M Data Management 2014/15 12

Two-phase locking

o Discipline on transactions to ensure schedule is serialiableo Growing phase:

o Transaction must acquire all its locks in one phaseo Lock level can be escalated ( read > write)

o Shrinking phaseo Transaction must release all its locks in the second

phaseo Lock level can reduce (write > read)

o Can’t acquire a lock after releasing a lock

UFIE8K-15-M Data Management 2010 13

Lost Update with Locking

o T1– bot– wlock(x)– read(x)– x=x+1– write(x)– unlock(x)– commit

o T2

– bot– wlock(x)– wait– wait– wait– read(x)– x=x+1– write(x)– commit

UFIE8K-15-M Data Management 2014/15 14

Dirty Read with locking

o T1– bot– wlock(x)– read(x)– x=x+1– write(x)

– unlock(x)

– abort

o x should be 3 but it is still 4 !

o T2

– bot

– wlock(x)– wait– wait– wait– read(x)– x=x+1– write(x)– unlock(x)– commit

UFIE8K-15-M Data Management 2014/15 15

Strict Two-phase locking

o Locks can only be released after commito Deadlock

o T1 wlocks(x) and waits to wlock(y)o T2 wlocks(y) and waits to wlock(x)

UFIE8K-15-M Data Management 2014/15 16

Deadlock

o Deadlock– T1 wlocks(x) and waits to wlock(y)– T2 wlocks(y) and waits to wlock(x)

o Approaches– timeout - lock expires so transaction aborts– deadlock detection - identifying deadlocks and killing

one transaction

UFIE8K-15-M Data Management 2014/15 17