chapter 20 transaction management thomas connolly, carolyn begg, database system, a practical...
TRANSCRIPT
![Page 1: Chapter 20 Transaction Management Thomas Connolly, Carolyn Begg, Database System, A Practical Approach to Design Implementation and Management, 4 th Edition,](https://reader030.vdocuments.us/reader030/viewer/2022033009/5697bfdb1a28abf838cb0a19/html5/thumbnails/1.jpg)
Chapter 20
Transaction Management
Thomas Connolly, Carolyn Begg, Database System, A Practical Approach to Design Implementation and
Management, 4th Edition, Addison Wesley
Pg 572 ~ 678
![Page 2: Chapter 20 Transaction Management Thomas Connolly, Carolyn Begg, Database System, A Practical Approach to Design Implementation and Management, 4 th Edition,](https://reader030.vdocuments.us/reader030/viewer/2022033009/5697bfdb1a28abf838cb0a19/html5/thumbnails/2.jpg)
Learning Outcomes
• Transaction
• Concurrent Processing
• Backup and Recovery
![Page 3: Chapter 20 Transaction Management Thomas Connolly, Carolyn Begg, Database System, A Practical Approach to Design Implementation and Management, 4 th Edition,](https://reader030.vdocuments.us/reader030/viewer/2022033009/5697bfdb1a28abf838cb0a19/html5/thumbnails/3.jpg)
Transaction - I
• Definition– An action or actions to read or update the
contents of the database
• Types– Committed– Aborted– Compensating
![Page 4: Chapter 20 Transaction Management Thomas Connolly, Carolyn Begg, Database System, A Practical Approach to Design Implementation and Management, 4 th Edition,](https://reader030.vdocuments.us/reader030/viewer/2022033009/5697bfdb1a28abf838cb0a19/html5/thumbnails/4.jpg)
Transaction - II
• Property of Transactions (ACID)– Atomicity (all or nothing)– Consistency (state by database constraints and
applications)– Isolation (independent)– Durability (permanent)
![Page 5: Chapter 20 Transaction Management Thomas Connolly, Carolyn Begg, Database System, A Practical Approach to Design Implementation and Management, 4 th Edition,](https://reader030.vdocuments.us/reader030/viewer/2022033009/5697bfdb1a28abf838cb0a19/html5/thumbnails/5.jpg)
Concurrent Processing
• Definition
• Problems
• Control
![Page 6: Chapter 20 Transaction Management Thomas Connolly, Carolyn Begg, Database System, A Practical Approach to Design Implementation and Management, 4 th Edition,](https://reader030.vdocuments.us/reader030/viewer/2022033009/5697bfdb1a28abf838cb0a19/html5/thumbnails/6.jpg)
Concurrent Processing
• Multiprogramming
• Interleaved between two transactions– CPU– I/O
• Logical unit of work
![Page 7: Chapter 20 Transaction Management Thomas Connolly, Carolyn Begg, Database System, A Practical Approach to Design Implementation and Management, 4 th Edition,](https://reader030.vdocuments.us/reader030/viewer/2022033009/5697bfdb1a28abf838cb0a19/html5/thumbnails/7.jpg)
Concurrent Processing Problem
• No problem– Write different data– Update different data– Read the same data
• Problem– Write the same data– Update the same data
![Page 8: Chapter 20 Transaction Management Thomas Connolly, Carolyn Begg, Database System, A Practical Approach to Design Implementation and Management, 4 th Edition,](https://reader030.vdocuments.us/reader030/viewer/2022033009/5697bfdb1a28abf838cb0a19/html5/thumbnails/8.jpg)
Concurrent Processing Problems
• Lost update– Two transactions simultaneously update the same files
• Uncommitted update– Transaction 2 uses the result updated by transaction 1
– Transaction 1 aborts and rolls back
– Transaction 2 commits
• Inconsistent Analysis– Transaction 1 reads
– Transaction 2 reads and uses for calculation
– Transaction 1 updates and commits
– Transaction 2 updates and commits
![Page 9: Chapter 20 Transaction Management Thomas Connolly, Carolyn Begg, Database System, A Practical Approach to Design Implementation and Management, 4 th Edition,](https://reader030.vdocuments.us/reader030/viewer/2022033009/5697bfdb1a28abf838cb0a19/html5/thumbnails/9.jpg)
SERIALIZABILITY
• Transaction results form concurrent processing are the same as if stand-alone sequential processing was used
• Ensure no anomalies arise from concurrent processing
![Page 10: Chapter 20 Transaction Management Thomas Connolly, Carolyn Begg, Database System, A Practical Approach to Design Implementation and Management, 4 th Edition,](https://reader030.vdocuments.us/reader030/viewer/2022033009/5697bfdb1a28abf838cb0a19/html5/thumbnails/10.jpg)
Concurrency Control
• Locking
• Deadlock
• Two-phase locking
• Timestamping
• Optimistic technique
![Page 11: Chapter 20 Transaction Management Thomas Connolly, Carolyn Begg, Database System, A Practical Approach to Design Implementation and Management, 4 th Edition,](https://reader030.vdocuments.us/reader030/viewer/2022033009/5697bfdb1a28abf838cb0a19/html5/thumbnails/11.jpg)
Locking
• Types– Shared Locks vs. Exclusive Locks– Read Locks vs. Write Locks– Upgrade vs. Downgrade
• Granularity– Database– file– page– record– field
![Page 12: Chapter 20 Transaction Management Thomas Connolly, Carolyn Begg, Database System, A Practical Approach to Design Implementation and Management, 4 th Edition,](https://reader030.vdocuments.us/reader030/viewer/2022033009/5697bfdb1a28abf838cb0a19/html5/thumbnails/12.jpg)
Deadlock
• Definition– Tow or more transactions each wait for locks held by
other transaction
– Livelock
• Control– Wait-Die
– Wound-wait
– Time out
– Conservative 2PL
![Page 13: Chapter 20 Transaction Management Thomas Connolly, Carolyn Begg, Database System, A Practical Approach to Design Implementation and Management, 4 th Edition,](https://reader030.vdocuments.us/reader030/viewer/2022033009/5697bfdb1a28abf838cb0a19/html5/thumbnails/13.jpg)
Two-phase Locking
• Growing phase– Get all locks– Upgrade locks
• Shrinking phase– Downgrade locks– Once starting to release a lock - no more new
locks
![Page 14: Chapter 20 Transaction Management Thomas Connolly, Carolyn Begg, Database System, A Practical Approach to Design Implementation and Management, 4 th Edition,](https://reader030.vdocuments.us/reader030/viewer/2022033009/5697bfdb1a28abf838cb0a19/html5/thumbnails/14.jpg)
Timestamping
• Timestamp– unique identifier as relative starting time of a
transaction– Read-timestamp & write timestamp
• Timestamp protocol– Transactions with smaller timestamps get
priority in the event of conflict– Transaction is only allowed on the item with
smaller read-timestamp or write timestamp
![Page 15: Chapter 20 Transaction Management Thomas Connolly, Carolyn Begg, Database System, A Practical Approach to Design Implementation and Management, 4 th Edition,](https://reader030.vdocuments.us/reader030/viewer/2022033009/5697bfdb1a28abf838cb0a19/html5/thumbnails/15.jpg)
Optimistic Technique
• Read phase
• Validate phase
• Write phase
![Page 16: Chapter 20 Transaction Management Thomas Connolly, Carolyn Begg, Database System, A Practical Approach to Design Implementation and Management, 4 th Edition,](https://reader030.vdocuments.us/reader030/viewer/2022033009/5697bfdb1a28abf838cb0a19/html5/thumbnails/16.jpg)
Database Recovery
• Definition– Restoring the database to its correct state in the event
of a failure
• Reasons– Physical (fire, flood, etc.)– Sabotage– Carelessness– Hardware– Software (application/system)
![Page 17: Chapter 20 Transaction Management Thomas Connolly, Carolyn Begg, Database System, A Practical Approach to Design Implementation and Management, 4 th Edition,](https://reader030.vdocuments.us/reader030/viewer/2022033009/5697bfdb1a28abf838cb0a19/html5/thumbnails/17.jpg)
Database Backup
• Backup– Copy of the database
• Transaction log– Transaction ID, time, operation, object, before
image, after image, prior pointer, next pointer
• Checkpoint– Synchronize transaction log and the database– Write data from buffers to database on the disk– Write checkpoint to log identify current
transaction(s)
![Page 18: Chapter 20 Transaction Management Thomas Connolly, Carolyn Begg, Database System, A Practical Approach to Design Implementation and Management, 4 th Edition,](https://reader030.vdocuments.us/reader030/viewer/2022033009/5697bfdb1a28abf838cb0a19/html5/thumbnails/18.jpg)
Recovery Methods
• Reprocessing– Record all transactions since last backup and
replay the following transactions
• Rollfoward– Use the transaction log to change any
committed transactions on the database or since last checkpoint
• Rollback– Use transaction log to undo any aborted
transactions
![Page 19: Chapter 20 Transaction Management Thomas Connolly, Carolyn Begg, Database System, A Practical Approach to Design Implementation and Management, 4 th Edition,](https://reader030.vdocuments.us/reader030/viewer/2022033009/5697bfdb1a28abf838cb0a19/html5/thumbnails/19.jpg)
Shadow Paging Method
• Current page table vs. Shadow page table
• Pros & cons– Faster– Less overhead– Data fragmentation– Reclaim inaccessible blocks