1 / 31

Database Transactions and Concurrency Control

Learn about managing multi-user databases, concurrency control, resource locking, and transaction management for database reliability and security. Understand ACID properties, transaction isolation levels, deadlock locking, and rollback strategies.

hendersonc
Download Presentation

Database Transactions and Concurrency Control

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Processing Chapter 9 Managing Multi-user Databases

  2. Multi-User Issues • Concurrency Control • Database Reliability • Database Security • Database Administration

  3. Multi-User Databases • Database usage • Must plan for managing transactions

  4. Chapter 9 Transactions Trans 6 Trans 4 Trans 3 Trans 5 Trans 7 Trans 1 Trans 2 (TIME)

  5. Finite State Machine: exam question review process Version 2 Final Edit Question Question Submitted by author Edit Question Generate Review Send back to Author Assign Course/LU Re-assign LU to Skill Author Edit Review Final Edit Get Reviews Send to Beta Question has Problem Release for review Send to Final Edit Send to Exit Reject Question Reject Question Send to Review Beta Exam Generate Beta Stats Beta Edit Question Reject Put on Beta Exam Put on Exit Exam Problem Author Review Exit Exam Send to Review Generate Exam Stats Reject Question Send to Beta Send to Exit Retire Question Retired Transactions occur concurrently.

  6. Remedy for inconsistencies caused by concurrent processing • Resource Locking “disallow sharing by locking data that are retrieved for update”

  7. Lock Terminology • Implicit locksplaced by the DBMS • Explicit locksplaced by command • Lock granularitythe size of the lock • Exclusive lock from access of any type • Shared lock from change but not read

  8. Lock Granularity • Lock granularitythe size of the lock • Can lock at the row-level, key (index), page, Table, or Database level • There is a trade-off between cost of system overhead and concurrency • Concurrency cost: restricted access to object; some transactions may have to wait.

  9. Lock Granularity

  10. Serializable Transactions “a scheme for processing concurrent transactions” • Two-phased locking • Growing and Shrinking phase • Once a lock is released, transaction can not place any more locks.

  11. Chapter 10 Serializable Even though the transactions are physically executed concurrently in time… Trans 6 Trans 4 Trans 3 Trans 5 Trans 7 Trans 1 Trans 2 (TIME)

  12. Chapter 10 Serializable The logical result is the same as if they where executed in sequence, one after the other. • We affect this through adjusting the transaction isolation level and resource locking. Trans 1 Trans 2 Trans 3 Trans 4 Trans 5 Trans 6 Trans 7 (TIME)

  13. Serializable • To avoid a lost update, transactions must be isolated at the Repeatable read level or higher. SET TRANSACTION ISOLATION LEVEL REPEATABLE READ BEGIN TRANSACTION UPDATE QUERYSUBMISSION SET SQLSUBMISSION = ‘/*Basic structure of CREATE…’ WHERE QUERYID = 84 COMMIT TRANSACTION

  14. Deadlock Locking solves one problem, but introduces another… “deadly embrace”; each transaction waiting for a resource that the other person has locked

  15. Page 314

  16. Transaction • Concept of a Transaction • A series of Reads and Writes to the database • A transaction is a sequence of operations performed as a single logical unit of work. A logical unit of work must exhibit four properties, called the ACID (Atomicity, Consistency, Isolation, and Durability) properties, to qualify as a transaction: • Part of a broader area: Transaction Management Source: Microsoft documentation

  17. ACID • Atomicity • A transaction must be an atomic unit of work; either all of its data modifications are performed, or none of them is performed

  18. ACID • Transaction boundaries • Controlled at the connection level • BEGIN & COMMIT BEGIN TRANSACTION Marks the starting point of an explicit, local transaction. Syntax BEGIN TRAN [ SACTION ] [ transaction_name | @tran_name_variable [WITH MARK [ 'description' ] ] ]

  19. ACID • COMMIT TRAN • A COMMIT statement guarantees all of the transaction's modifications are made a permanent part of the database. • A COMMIT also frees resources, such as locks, used by the transaction.

  20. ACID • BEGIN TRANSACTION represents a point at which the data referenced by a connection is logically and physically consistent. • If errors are encountered, all data modifications made after the BEGIN TRANSACTION can be rolled back to return the data to this known state of consistency.

  21. ACID • ROLLBACK TRAN • Rolls back an explicit or implicit transaction to the beginning of the transaction, or to a savepoint inside a transaction. • It also frees resources held by the transaction. • A ROLLBACK TRANSACTION statement does not produce any messages to the user. If warnings are needed in stored procedures or triggers, use the RAISERROR or PRINT statements. RAISERROR is the preferred statement for indicating errors. • ROLLBACK is expensive…

  22. ACID • ROLLBACK TRAN • Rollback and rollforward are possible because the dbms maintains transaction logs. • You can view SQL Server transaction logs with the undocumented Database Console command: • DBCC log ({dbid|dbname}, [, type={-1|0|1|2|3|4}]) • DBCC log (NorthWind, type=2)

  23. ACID • savepoint • A savepoint allows you to rollback a portion of a transaction rather than the entire transaction • Savepoints allow you to commit part of a transaction while rolling back the remainder. • A bit risky though. There is still the chance that an error affects a change made prior to the savepoint.

  24. ACID • Consistency • When completed, a transaction must leave all data in a consistent state. In a relational database, all rules must be applied to the transaction’s modifications to maintain all data integrity.

  25. ACID • Consistency is maintained by rolling back the transaction to the point where it started (or locking resources).

  26. ACID • Isolation • Modifications made by concurrent transactions must be isolated from the modifications made by any other concurrent transactions. A transaction either sees data in the state it was in before another concurrent transaction modified it, or it sees the data after the second transaction has completed, but it does not see an intermediate state. This is referred to as serializability because it results in the ability to reload the starting data and replay a series of transactions to end up with the data in the same state it was in after the original transactions were performed. • We’ll look at example problems shortly

  27. ACID • Durability • After a transaction has completed, its effects are permanently in place in the system. The modifications persist even in the event of a system failure.

More Related