transactions and acid properties - it uitu.dk/~mogel/sidd2011/lectures/sidd.2011.13.pdf ·...
TRANSCRIPT
![Page 1: Transactions and ACID properties - IT Uitu.dk/~mogel/SIDD2011/lectures/SIDD.2011.13.pdf · Transactions and ACID properties Introduction to Database Design 2011, Lecture 13 . Rasmus](https://reader033.vdocuments.us/reader033/viewer/2022042112/5e8e7171132d6d6938784c74/html5/thumbnails/1.jpg)
Rasmus Ejlers Møgelberg
Transactions and ACID properties
Introduction to Database Design 2011, Lecture 13
![Page 2: Transactions and ACID properties - IT Uitu.dk/~mogel/SIDD2011/lectures/SIDD.2011.13.pdf · Transactions and ACID properties Introduction to Database Design 2011, Lecture 13 . Rasmus](https://reader033.vdocuments.us/reader033/viewer/2022042112/5e8e7171132d6d6938784c74/html5/thumbnails/2.jpg)
Rasmus Ejlers Møgelberg
Course overview
• Communicating with DBMSs
• Designing databases
• Making databases efficient
• Making databases reliable
2
![Page 3: Transactions and ACID properties - IT Uitu.dk/~mogel/SIDD2011/lectures/SIDD.2011.13.pdf · Transactions and ACID properties Introduction to Database Design 2011, Lecture 13 . Rasmus](https://reader033.vdocuments.us/reader033/viewer/2022042112/5e8e7171132d6d6938784c74/html5/thumbnails/3.jpg)
Rasmus Ejlers Møgelberg
Overview
• Transactions
• ACID properties
• Concurrency and safe schedules
3
![Page 4: Transactions and ACID properties - IT Uitu.dk/~mogel/SIDD2011/lectures/SIDD.2011.13.pdf · Transactions and ACID properties Introduction to Database Design 2011, Lecture 13 . Rasmus](https://reader033.vdocuments.us/reader033/viewer/2022042112/5e8e7171132d6d6938784c74/html5/thumbnails/4.jpg)
Rasmus Ejlers Møgelberg
Transactions
• Transactions are units of work
• Example
- Read balance of account A
- Compute new balance = balance(A) - 50
- Write new balance of A
- Read balance of account B
- Compute new balance = balance(B) + 50
- Write balance of account B
• Transaction should be atomic: all or nothing
4
![Page 5: Transactions and ACID properties - IT Uitu.dk/~mogel/SIDD2011/lectures/SIDD.2011.13.pdf · Transactions and ACID properties Introduction to Database Design 2011, Lecture 13 . Rasmus](https://reader033.vdocuments.us/reader033/viewer/2022042112/5e8e7171132d6d6938784c74/html5/thumbnails/5.jpg)
Rasmus Ejlers Møgelberg
Transactions
• In database practice transactions are sequences of SQL statements
• Possibly intertwined by computations
• Can be written in
- programming language (e.g. Java) accessing a database
- procedural component of SQL
• SQL: begin atomic ... end
• Here we simplify and consider just sequences of reads and writes
5
![Page 6: Transactions and ACID properties - IT Uitu.dk/~mogel/SIDD2011/lectures/SIDD.2011.13.pdf · Transactions and ACID properties Introduction to Database Design 2011, Lecture 13 . Rasmus](https://reader033.vdocuments.us/reader033/viewer/2022042112/5e8e7171132d6d6938784c74/html5/thumbnails/6.jpg)
Rasmus Ejlers Møgelberg
ACID
• ACID stands for
- Atomicity
- Consistency
- Isolation
- Durability
6
![Page 7: Transactions and ACID properties - IT Uitu.dk/~mogel/SIDD2011/lectures/SIDD.2011.13.pdf · Transactions and ACID properties Introduction to Database Design 2011, Lecture 13 . Rasmus](https://reader033.vdocuments.us/reader033/viewer/2022042112/5e8e7171132d6d6938784c74/html5/thumbnails/7.jpg)
Rasmus Ejlers Møgelberg
Consistency
• Database must always be consistent wrt real world rules, e.g.,
- Integrity constraints such as referential integrity must be satisfied
- Rules of real world situation must be satisfied, e.g.,
• Account balance must always be above a certain number (e.g. 0)
• Should also reflect real world as it is now
- e.g. balance stored should correspond to actual balance of account
7
![Page 8: Transactions and ACID properties - IT Uitu.dk/~mogel/SIDD2011/lectures/SIDD.2011.13.pdf · Transactions and ACID properties Introduction to Database Design 2011, Lecture 13 . Rasmus](https://reader033.vdocuments.us/reader033/viewer/2022042112/5e8e7171132d6d6938784c74/html5/thumbnails/8.jpg)
Rasmus Ejlers Møgelberg
Transactional consistency
• Transaction leaves database in consistent state
- (may assume database consistent before transaction start)
• May be required to satisfy other rules, e.g. leave the sum of the balances unchanged
- (no money created or lost)
• During transaction consistency requirement may be violated temporarily
• Transactional consistency responsibility of transaction designer
8
![Page 9: Transactions and ACID properties - IT Uitu.dk/~mogel/SIDD2011/lectures/SIDD.2011.13.pdf · Transactions and ACID properties Introduction to Database Design 2011, Lecture 13 . Rasmus](https://reader033.vdocuments.us/reader033/viewer/2022042112/5e8e7171132d6d6938784c74/html5/thumbnails/9.jpg)
Rasmus Ejlers Møgelberg
Atomicity
• Transaction can be considered a unit of work
- All or nothing!
• Consistency is impossible without atomicity
• Sometimes not possible to complete a started transaction, e.g.
- In case of hardware failure or loss of connection
- The application program may choose to abandon transaction
- The DBMS may refuse to complete the transaction
• In these cases we say that transaction fails
9
![Page 10: Transactions and ACID properties - IT Uitu.dk/~mogel/SIDD2011/lectures/SIDD.2011.13.pdf · Transactions and ACID properties Introduction to Database Design 2011, Lecture 13 . Rasmus](https://reader033.vdocuments.us/reader033/viewer/2022042112/5e8e7171132d6d6938784c74/html5/thumbnails/10.jpg)
Rasmus Ejlers Møgelberg
Atomicity
• If a transaction fails it must be aborted
• This involves rolling back the transaction
• i.e., undoing all changes made by transaction
• Concurrency makes this complicated
- e.g., changes made by transaction may have already been read
• Ability to roll back is implemented using a log of changes made to the database
10
![Page 11: Transactions and ACID properties - IT Uitu.dk/~mogel/SIDD2011/lectures/SIDD.2011.13.pdf · Transactions and ACID properties Introduction to Database Design 2011, Lecture 13 . Rasmus](https://reader033.vdocuments.us/reader033/viewer/2022042112/5e8e7171132d6d6938784c74/html5/thumbnails/11.jpg)
Rasmus Ejlers Møgelberg
Durability
• Durability is about trustworthy storage
• A transaction that has successfully completed is said to be committed
• Changes made by a committed transaction must be durable, i.e., able to survive
- Power failure
- Hardware failure etc
11
![Page 12: Transactions and ACID properties - IT Uitu.dk/~mogel/SIDD2011/lectures/SIDD.2011.13.pdf · Transactions and ACID properties Introduction to Database Design 2011, Lecture 13 . Rasmus](https://reader033.vdocuments.us/reader033/viewer/2022042112/5e8e7171132d6d6938784c74/html5/thumbnails/12.jpg)
Rasmus Ejlers Møgelberg
Durability
• When committing, changes must be written to non-volatile storage
• In practice, log is written to non-volatile storage
• Storage must be able to survive hardware failure
- Maintain multiple copies of data
- RAID
12
![Page 13: Transactions and ACID properties - IT Uitu.dk/~mogel/SIDD2011/lectures/SIDD.2011.13.pdf · Transactions and ACID properties Introduction to Database Design 2011, Lecture 13 . Rasmus](https://reader033.vdocuments.us/reader033/viewer/2022042112/5e8e7171132d6d6938784c74/html5/thumbnails/13.jpg)
Rasmus Ejlers Møgelberg
Transaction model
13
![Page 14: Transactions and ACID properties - IT Uitu.dk/~mogel/SIDD2011/lectures/SIDD.2011.13.pdf · Transactions and ACID properties Introduction to Database Design 2011, Lecture 13 . Rasmus](https://reader033.vdocuments.us/reader033/viewer/2022042112/5e8e7171132d6d6938784c74/html5/thumbnails/14.jpg)
Rasmus Ejlers Møgelberg
Isolation
• Transactions may not interfere with each other
• In reality transactions are executed concurrently
• Statements of transactions intertwined
• DBMS should create illusion of transactions being executed sequentially
• Isolation is necessary for consistency
14
![Page 15: Transactions and ACID properties - IT Uitu.dk/~mogel/SIDD2011/lectures/SIDD.2011.13.pdf · Transactions and ACID properties Introduction to Database Design 2011, Lecture 13 . Rasmus](https://reader033.vdocuments.us/reader033/viewer/2022042112/5e8e7171132d6d6938784c74/html5/thumbnails/15.jpg)
Rasmus Ejlers Møgelberg
Need for concurrency
• Resources operate in parallel
- Multiple CPUs
- Data stored on multiple disks
• Computation may be stalled while waiting for data
• Gains of concurrency
- Increased throughput: idle resources can be utilised
- Decreased waiting time: new transactions can start executing immediately
15
![Page 16: Transactions and ACID properties - IT Uitu.dk/~mogel/SIDD2011/lectures/SIDD.2011.13.pdf · Transactions and ACID properties Introduction to Database Design 2011, Lecture 13 . Rasmus](https://reader033.vdocuments.us/reader033/viewer/2022042112/5e8e7171132d6d6938784c74/html5/thumbnails/16.jpg)
Rasmus Ejlers Møgelberg
Concurrency and safe schedules
![Page 17: Transactions and ACID properties - IT Uitu.dk/~mogel/SIDD2011/lectures/SIDD.2011.13.pdf · Transactions and ACID properties Introduction to Database Design 2011, Lecture 13 . Rasmus](https://reader033.vdocuments.us/reader033/viewer/2022042112/5e8e7171132d6d6938784c74/html5/thumbnails/17.jpg)
Rasmus Ejlers Møgelberg
A serial schedule
17
T1 T2
read(A)A := A - 50write(A)read(B)B := B + 50write(B)
read(A)A := A + 20write(A)
![Page 18: Transactions and ACID properties - IT Uitu.dk/~mogel/SIDD2011/lectures/SIDD.2011.13.pdf · Transactions and ACID properties Introduction to Database Design 2011, Lecture 13 . Rasmus](https://reader033.vdocuments.us/reader033/viewer/2022042112/5e8e7171132d6d6938784c74/html5/thumbnails/18.jpg)
Rasmus Ejlers Møgelberg
A good concurrent schedule
18
T1 T2
read(A)A := A - 50write(A)
read(A)A := A + 20write(A)
read(B)B := B + 50write(B)
![Page 19: Transactions and ACID properties - IT Uitu.dk/~mogel/SIDD2011/lectures/SIDD.2011.13.pdf · Transactions and ACID properties Introduction to Database Design 2011, Lecture 13 . Rasmus](https://reader033.vdocuments.us/reader033/viewer/2022042112/5e8e7171132d6d6938784c74/html5/thumbnails/19.jpg)
Rasmus Ejlers Møgelberg
A bad concurrent schedule
19
T1 T2
read(A)A := A - 50
read(A)A := A + 20write(A)
write(A)read(B)B := B + 50write(B)
![Page 20: Transactions and ACID properties - IT Uitu.dk/~mogel/SIDD2011/lectures/SIDD.2011.13.pdf · Transactions and ACID properties Introduction to Database Design 2011, Lecture 13 . Rasmus](https://reader033.vdocuments.us/reader033/viewer/2022042112/5e8e7171132d6d6938784c74/html5/thumbnails/20.jpg)
Rasmus Ejlers Møgelberg
Schedules
• The DBMS receives a sequence of read and write requests from different transactions
• A schedule is an ordering of the reads and writes respecting the internal ordering in each transaction
20
![Page 21: Transactions and ACID properties - IT Uitu.dk/~mogel/SIDD2011/lectures/SIDD.2011.13.pdf · Transactions and ACID properties Introduction to Database Design 2011, Lecture 13 . Rasmus](https://reader033.vdocuments.us/reader033/viewer/2022042112/5e8e7171132d6d6938784c74/html5/thumbnails/21.jpg)
Rasmus Ejlers Møgelberg
Examples
• Two schedules
• Schedule on right is equivalent to first T1 then T2
• It is up to DBMS to avoid bad schedules such as the one on the left
21
T1 T2
read(A)read(A)write(A)
write(A)read(B)write(B)
T1 T2
read(A)write(A)
read(A)write(A)
read(B)write(B)
![Page 22: Transactions and ACID properties - IT Uitu.dk/~mogel/SIDD2011/lectures/SIDD.2011.13.pdf · Transactions and ACID properties Introduction to Database Design 2011, Lecture 13 . Rasmus](https://reader033.vdocuments.us/reader033/viewer/2022042112/5e8e7171132d6d6938784c74/html5/thumbnails/22.jpg)
Rasmus Ejlers Møgelberg
Conflicting operations
• Two operations commute if the order in which they are executed does not affect the result
• If they do not commute we say that they conflict
22
read(A), read(B) = read(B), read(A)write(A), read(B) = read(B),write(A)
write(A),write(B) = write(B),write(A)read(A), read(A) = read(A), read(A)
write(A), read(A) != read(A),write(A)write(A),write(A) != write(A),write(A)
![Page 23: Transactions and ACID properties - IT Uitu.dk/~mogel/SIDD2011/lectures/SIDD.2011.13.pdf · Transactions and ACID properties Introduction to Database Design 2011, Lecture 13 . Rasmus](https://reader033.vdocuments.us/reader033/viewer/2022042112/5e8e7171132d6d6938784c74/html5/thumbnails/23.jpg)
Rasmus Ejlers Møgelberg
Conflict equivalence
• Two schedules are conflict equivalent if they differ only up to swapping commuting operations
• Executing conflict equivalent schedules gives same result
23
T1 T2
read(A)write(A)
read(A)write(A)
read(B)write(B)
T1 T2
read(A)write(A)read(B)write(B)
read(A)write(A)
![Page 24: Transactions and ACID properties - IT Uitu.dk/~mogel/SIDD2011/lectures/SIDD.2011.13.pdf · Transactions and ACID properties Introduction to Database Design 2011, Lecture 13 . Rasmus](https://reader033.vdocuments.us/reader033/viewer/2022042112/5e8e7171132d6d6938784c74/html5/thumbnails/24.jpg)
Rasmus Ejlers Møgelberg
A non-example
• The following schedules are not conflict equivalent
• Suppose e.g. T1 transfers all money available in A to B
24
T1 T2
read(A)write(A)
read(A)write(A)
read(B)write(B)
T1 T2
read(A)write(A)
read(A)write(A)read(B)write(B)
![Page 25: Transactions and ACID properties - IT Uitu.dk/~mogel/SIDD2011/lectures/SIDD.2011.13.pdf · Transactions and ACID properties Introduction to Database Design 2011, Lecture 13 . Rasmus](https://reader033.vdocuments.us/reader033/viewer/2022042112/5e8e7171132d6d6938784c74/html5/thumbnails/25.jpg)
Rasmus Ejlers Møgelberg
Conflict serializability
• A schedule is conflict serializable if it is conflict equivalent to a serial schedule
• Example:
25
T1 T2
read(A)write(A)
read(A)write(A)
read(B)write(B)
![Page 26: Transactions and ACID properties - IT Uitu.dk/~mogel/SIDD2011/lectures/SIDD.2011.13.pdf · Transactions and ACID properties Introduction to Database Design 2011, Lecture 13 . Rasmus](https://reader033.vdocuments.us/reader033/viewer/2022042112/5e8e7171132d6d6938784c74/html5/thumbnails/26.jpg)
Rasmus Ejlers Møgelberg
A non-serializable schedule
• The following is neither conflict equivalent to T1T2 nor T2T1
26
T1 T2
read(A)read(A)write(A)
write(A)read(B)write(B)
![Page 27: Transactions and ACID properties - IT Uitu.dk/~mogel/SIDD2011/lectures/SIDD.2011.13.pdf · Transactions and ACID properties Introduction to Database Design 2011, Lecture 13 . Rasmus](https://reader033.vdocuments.us/reader033/viewer/2022042112/5e8e7171132d6d6938784c74/html5/thumbnails/27.jpg)
Rasmus Ejlers Møgelberg
Serializable schedules
• Serializable schedules are the ‘good schedules’
• Parallel executions of transactions
• But still maintain illusion of serial execution
• DBMS should ensure that only serializable schedules occur
• This is usually done using locks
27
![Page 28: Transactions and ACID properties - IT Uitu.dk/~mogel/SIDD2011/lectures/SIDD.2011.13.pdf · Transactions and ACID properties Introduction to Database Design 2011, Lecture 13 . Rasmus](https://reader033.vdocuments.us/reader033/viewer/2022042112/5e8e7171132d6d6938784c74/html5/thumbnails/28.jpg)
Rasmus Ejlers Møgelberg
Detecting non-serializability
28
T1 T2 T3 T4
read(A)write(A)
read(A)write(B)read(C)
write(C)read(B)
![Page 29: Transactions and ACID properties - IT Uitu.dk/~mogel/SIDD2011/lectures/SIDD.2011.13.pdf · Transactions and ACID properties Introduction to Database Design 2011, Lecture 13 . Rasmus](https://reader033.vdocuments.us/reader033/viewer/2022042112/5e8e7171132d6d6938784c74/html5/thumbnails/29.jpg)
Rasmus Ejlers Møgelberg
Precedence graph
• A cycle, so not conflict serializable
• Theorem. A schedule is conflict serializable if and only if its precedence graph is acyclic
29
T1!! T2
!! T3"" !! T4
![Page 30: Transactions and ACID properties - IT Uitu.dk/~mogel/SIDD2011/lectures/SIDD.2011.13.pdf · Transactions and ACID properties Introduction to Database Design 2011, Lecture 13 . Rasmus](https://reader033.vdocuments.us/reader033/viewer/2022042112/5e8e7171132d6d6938784c74/html5/thumbnails/30.jpg)
Rasmus Ejlers Møgelberg
Another example
30
T1 T2 T3 T4
read(A)write(C)
write(B)write(B)read(D)
read(C)write(A)
write(D)
![Page 31: Transactions and ACID properties - IT Uitu.dk/~mogel/SIDD2011/lectures/SIDD.2011.13.pdf · Transactions and ACID properties Introduction to Database Design 2011, Lecture 13 . Rasmus](https://reader033.vdocuments.us/reader033/viewer/2022042112/5e8e7171132d6d6938784c74/html5/thumbnails/31.jpg)
Rasmus Ejlers Møgelberg
Precedence graph
• Equivalent serial schedules
31
T1T2T3T4
T1T3T2T4
T2
!!!!!
!!!!
T1
"""""""""
!!!!!
!!!!
T4
T3
"""""""""
![Page 32: Transactions and ACID properties - IT Uitu.dk/~mogel/SIDD2011/lectures/SIDD.2011.13.pdf · Transactions and ACID properties Introduction to Database Design 2011, Lecture 13 . Rasmus](https://reader033.vdocuments.us/reader033/viewer/2022042112/5e8e7171132d6d6938784c74/html5/thumbnails/32.jpg)
Rasmus Ejlers Møgelberg
Yet another example
• Not conflict serializable
• But result the same as running
32
T1 T2 T3
read(B)write(B)write(A)
write(A)write(A)
T2T1T3
![Page 33: Transactions and ACID properties - IT Uitu.dk/~mogel/SIDD2011/lectures/SIDD.2011.13.pdf · Transactions and ACID properties Introduction to Database Design 2011, Lecture 13 . Rasmus](https://reader033.vdocuments.us/reader033/viewer/2022042112/5e8e7171132d6d6938784c74/html5/thumbnails/33.jpg)
Rasmus Ejlers Møgelberg
View serializability
• Two schedules are view equivalent if
- Corresponding reads in the two schedules always read same value
- The changes made to the database are always the same
• A schedule is view serializable if it is view equivalent to a serial schedule
• Schedule on previous slide is view serializable
33
![Page 34: Transactions and ACID properties - IT Uitu.dk/~mogel/SIDD2011/lectures/SIDD.2011.13.pdf · Transactions and ACID properties Introduction to Database Design 2011, Lecture 13 . Rasmus](https://reader033.vdocuments.us/reader033/viewer/2022042112/5e8e7171132d6d6938784c74/html5/thumbnails/34.jpg)
Rasmus Ejlers Møgelberg
View serializability
• The following is not view serializable
• To see this need to check all serial combinations
34
T1 T2 T3
read(B)write(B)read(A)
write(A)write(A)
![Page 35: Transactions and ACID properties - IT Uitu.dk/~mogel/SIDD2011/lectures/SIDD.2011.13.pdf · Transactions and ACID properties Introduction to Database Design 2011, Lecture 13 . Rasmus](https://reader033.vdocuments.us/reader033/viewer/2022042112/5e8e7171132d6d6938784c74/html5/thumbnails/35.jpg)
Rasmus Ejlers Møgelberg
View serializability
• We have two notions of serializability
• Conflict serializable schedules are also view serializable
• (because swapping commuting operations does not change behaviour of schedule)
• View serializable schedules need not be conflict serializable
• (see example a few slides back)
35
![Page 36: Transactions and ACID properties - IT Uitu.dk/~mogel/SIDD2011/lectures/SIDD.2011.13.pdf · Transactions and ACID properties Introduction to Database Design 2011, Lecture 13 . Rasmus](https://reader033.vdocuments.us/reader033/viewer/2022042112/5e8e7171132d6d6938784c74/html5/thumbnails/36.jpg)
Rasmus Ejlers Møgelberg
Summary
• ACID requirements for databases
- Atomicity, consistency, isolation, durability
• Isolation is an illusion
- In reality transactions are evaluated in parallel
• Two notions of good schedules
- Conflict serializability
- View serializability
• For exam you should be able to use these
36