1 / 15

Transactions and Concurrency

This overview covers vital concepts related to transactions and concurrency in database management systems (DBMS). A transaction is defined as an indivisible unit of work that must maintain properties of atomicity, consistency, isolation, and durability (ACID). The text explains the importance of transaction integrity, SQL transaction boundaries such as COMMIT and ROLLBACK, and concurrency control methods like locking and timestamping. It emphasizes how the DBMS manages multiple concurrent transactions to ensure data integrity and prevent undesirable interactions.

elisha
Télécharger la présentation

Transactions and Concurrency

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. Transactions and Concurrency Edel Sherratt

  2. Overview • What is a Transaction • Transaction Integrity • Transactions and SQL • Concurrency Control

  3. What is a transaction • A useful piece of work • For example: • record customer order • record a lodgement to a bank account • record a withdrawal • prepare a statement of account • Often embodied as a single execution of an application program

  4. A transaction has steps For example, a business to business customer order • Enter order data • Read customer record (or create a new record) • If (balance + order amount > credit then • reject order else • increase balance by order amount • store updated customer record • store the accepted order record

  5. Transaction integrity • Preserve • atomicity • permanence • independence • To preserve transation integrity, the DBMS must recognize transaction boundaries – the logical beginning and end of each transaction

  6. ACID properties Atomicity • A transaction is an indivisible unit of work; it is either performed in its entirety, or is not performed at all. Consistency • Database goes from one consistent state (representation of reality) to another Isolation • Partial effect of one transaction invisible to other transactions; overall effect is as if transactions were executed sequentially Durability • Effects of a successful transaction are permanently recorded in the database

  7. Transaction boundaries in SQL • The first SQL statement marks the beginning of a transaction • COMMIT marks the end of a successful transaction • ROLLBACK undoes the effects of a transaction • COMMIT and ROLLBACK mark transaction boundaries • The point at which a transaction is committed or rolled back is called a commit point • The next SQL statement after a COMMIT or ROLLBACK starts the next transaction

  8. General structure – embedded SQL … other code, part of the application program … SQL – the next statements are SQL UPDATE borrowers SET name = ‘Jones’ WHERE card_no =‘C12’ … other code … if (some condition) then SQL COMMIT else SQL ROLLBACK

  9. Concurrency Control • Multiple concurrent transactions • Avoid undesirable interactions between transactions • Preserve ACID properties • No incomplete transactions, no interference between transactions • Effect of executing a collection of transactions concurrently is the same as if the transactions were run one by one in some order - serializability • Responsibility of DBMS

  10. Example: concurrent updates of A. Smith’s savings account; starting balance is £100 • lodge £25 • retrieve customer record for A. Smith • add £25 to the balance • Store customer record • withdraw £50 • retrieve customer record for A. Smith • subtract £50 from the balance • store customer record What if the steps of these transactions were interleaved so that their execution order were ADBCEF?

  11. Locking • Data is locked when a transaction begins, and released when the transaction is committed or rolled back • Lock level: database, table, tuple, attribute • Lock mode: shared (read access only), exclusive • Problems with locking • deadlock • starvation • Dealing with deadlock • prevention • detection and resolution

  12. Timestamping • The system generates timestamps, usually a system clock time • Each transaction is stamped with its start time • Each data item is stamped with the times of the transactions that last read and last wrote the data item • Effect of running all the transactions concurrently is as if each transaction executed instantaneously at the time indicated by its timestamp • No transaction is allowed to read a value that was written in its future • No transaction writes a value to a data item if the old value of the data item is read in the transaction’s future

  13. Concurrency control by timestamping • Writing: a transaction with timestamp t tries to write an item with read time tr and write time tw • perform the write if t ≥ trand t ≥ tw;if t ≥ tw set the write time t • do nothing if tr ≤ t < twthe transaction need not roll back since no other transaction has attempted to read the item between t and tw • roll back if t < tr • Reading: the transaction tries to read the item • perform the read if t ≥ tw;if t > trthe set the read time to t • roll back if t < tw

  14. Potential problem • excessive rollback • could cause more delay than a locking system • compare with starvation

  15. Summary • What is a Transaction • Transaction Integrity • Transactions and SQL • Concurrency Control

More Related