1 / 31

SQL Transactions

DBTech VET. www. DBTechNet .org. SQL Transactions. (Basics). Disclaimer This project has been funded with support from the European Commission. This publication [communication] reflects the views only of the authors,

miach
Download Presentation

SQL Transactions

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. DBTech VET www.DBTechNet.org SQL Transactions (Basics) Disclaimer This project has been funded with support from the European Commission. This publication [communication] reflects the views only of the authors, and the Commission cannot be held responsible for any use which may be made of the information contained therein. Martti Laiho

  2. Contents • [ Introduction of the Virtual Laboratory, DB2, Oracle, ... ] • SQL-server,SQL-client, SQL-session • Dialogue: request, result, diagnostics • SQL transaction • SQLcode, SQLSTATE , diagnostics • Autocommit mode • Implicit/explicit start of transaction • Commit/Rollback • Concurrency anomalias • ACID principle • Isolation levels • ”Best practices” • Excercises for using a DBMS product Martti Laiho

  3. VirtualBox DebianDB Martti Laiho

  4. A sample MySQL test Martti Laiho

  5. Fig 1 User Interface / user transactions Application logic Retry wrapper Retry wrapper … … Transaction Transaction Transaction Data access DBMS stored database procedures

  6. Fig 2 Application Data access client Data access API exception Service processing Optimized execution plans JDBC / ODBC /.. ”Command” i.e. service request Server Data cache ”bufferpool” Driver Diagnostics as service response on errors exception Client-side data cache of resultset database Client-side cache of resultset • Data • Stored procedures • Execution plans Data row or flow of the resultset

  7. The abstract SQL API and the basic Client/Server architecture SQL editor appl. client Interactive SQL Embedded SQL, ... RDBMS SQL engine abstract layers of a RDBMS Transaction Manager DB engine Martti Laiho

  8. A generic overview of a database server Database server (instance) - Processes, threads and caches DBMS services: Listener Server agents Transaction manager SQL engine (parser) Security manager Query Optimizer Concurrency manager (Lock manager) Deadlock detector Recovery manager Relational engine Memory manager OpSys: File manager Disk manager Control Buffers Data Buffer (Bufferpool) LRU • Connections • Transaction control • Locking lists • SQL cache, etc. Table pages and index pages x Database backups rewriting pages at Checkpoints Backup Log Buffer fetching needed pages before & after images Restore and Roll-forward recovery of transactions Flush to log on Commit/Rollback to transaction logs Database files Archive of log history

  9. SQL Transaction Context: Use case / User transaction / “Business transaction” Sequence of SQL transactions SQL transaction: Begin work actions .. ... Commit / Rollback Database in consistent state Database in consistent state LUW = Logical Unit of Work ACID principle‏: - Atomicity - Consistency - Isolation - Durability Martti Laiho 1998

  10. Database SQL Transaction SELECT … if .. INSERT … if ... UPDATE … if ... DELETE … if … … COMMIT | ROLLBACK Transaction log Martti Laiho

  11. SQLcode, SQLSTATE ISO SQL-89 SQLcode: Integer: 100 No data 0 successful execution < 0 errors String of 5 characters: ISO SQL-92 SQLSTATE: class subclass Successful execution 0 0 0 0 0 Warning 0 1 n n n No data 0 2 0 0 0 . . . Transaction rollback 4 0 0 0 0 0 0 1 Serialization failure 0 0 2 Integrity constraint violation 0 0 3 Statement completion unknown 0 0 4 Triggred action exception etc - lots of standardized and implementation dependent codes Martti Laiho

  12. SQL Transaction Statements SQL dialects BEGIN [ {WORK | TRANSACTION} ] ; | SET TRANSACTION ISOLATION LEVEL <isolationlevel> ; | START TRANSACTION <mode> [,…] ; <SQL statement> ; . . . [SAVEPOINT <savepointname> ; <SQL statement> ; . . . ROLLBACK TO <savepointname>; ] <SQL statement> ; . . . . . . { ROLLBACK | COMMIT } [WORK] ; ISO SQL:1999 <mode> ::= <isolationlevel> | <accessmode> | <diagnosticssize> <isolationlevel> ::= READ UNCOMMITTTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE <accessmode> ::= READ ONLY | READ WRITE <diagnosticssize> ::= DIAGNOSTICS <maxdiagnosticsdetailcount> [SAVEPOINT <savepointname> ; <SQL statement> ; . . . ROLLBACK TO <savepointname>; ] Martti Laiho

  13. DIAGNOSTICS Items <header> <detail> (1) .. (<max diagnostics detail count> ) <SQL statement> ; GET DIAGNOSTICS <target> = <item> [, . . . ] If SQLSTATE = . . . Martti Laiho

  14. Database ACID SQL transaction [{SET | START} TRANSACTION [READ ONLY | READ WRITE ] ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE } [ if ..] ] SET {UNIQUE | REFERENCIAL} CONSTRAINTS {DEFERRED | IMMEDIATE } [ LOCK TABLE … ] SELECT … if .. INSERT … if ... UPDATE … if ... DELETE … if … SAVEPOINT spn … COMMIT | ROLLBACK if ... Isolation Consistency - by DBMS - logical Atomicity Durability Transaction log Martti Laiho

  15. Differently behaving products • As default in AUTOCOMMIT mode ? • Implicit or explicit starts of transactions • Implicit COMMIT on DDL ? • Default isolation • What is considered as error or Warning ? • Value truncation, value overflow, … • Error in command • Rolls back the command • Rolls back the command and discards commands until end of transaction • Rolls back the transaction • Concurrency control mechanism Martti Laiho

  16. Potential errors ”Begin transaction” call subprogram ”<SQL commands>” . . . ”COMMIT” subprogram ”CALL <SQL routine>” database ”<SQL commands>” back to autocommit mode ? Rollback? Stored routine <SQL statements> ”COMMIT” ? Rollback? COMMIT ? GET DIAGNOSTICS act_trans = TRANSACTION_ACTIVE If TRANSACTION_ACTIVE = 1 then … else AutoCommit mode ? Martti Laiho

  17. Concurrency Control Technologies • SQL standard defines Isolation Levels for transaction context based on anomalies, without concerning the technologies • Concurrency Control Implementations tuned by Isolation Levels: • Optimistic Concurrency Control (OCC) 100% isolated • Locking Schemes (LSCC) 0% ..100% • Multi-Versioning (MVCC) %? • Cursor level concurrency control, SELECT .. FOR UPDATE • Client-side • Row Version Verification (RVV) aka. "Optimistic Locking" Martti Laiho

  18. Concurrency Problems Typical anomalies‏(C J Date, Milton, SQL-92 )‏ 1 Lost Update Problem 2 Uncommitted Dependency Problem (Dirty Read)‏ 3 Inconsistent Analysis Problems a) Decreasing Read Set (Nonrepeatable Read)‏ b) Increasing Read Set (Phantoms) ‏ Martti Laiho

  19. ..Typical anomalies 1. The Lost Update Problem C. J. Date: Lost Update ”Tellers” transaction A transaction B account x: balance 1000 € “I will take 200 €” “I will take 500 €” 1. Read account x 3. balance = balance -200 5. Write account x 2. Read account x 4. balance = balance -500 6. Write account x time Martti Laiho

  20. ..Typical anomalies 1. The Lost Update Problem C. J. Date: Lost Update ”Tellers” transaction A transaction B account x: balance 1000 € “I will take 200 €” “I will take 500 €” 1. Read account x 3. balance = balance -200 5. Write account x 2. Read account x 4. balance = balance -500 6. Write account x Lost update! time Martti Laiho

  21. Locking Scheme Concurrency Control (LSCC)‏ Compatibility of S and X locks Locking granularity: Lock of transaction A to object o table page row Shared eXclusive SharedGrantWait ! eXclusive Wait !Wait ! . . . Lock request of transaction B to object o - S-lock grants read access to object - X-lock grants write access to object - X-lock request after getting S-lock is called as lock promotion‏ Martti Laiho

  22. ..Typical anomalies 1. The Lost Update Problem C. J. Date: Lost Update - Applying the locking scheme: ”Tellers” transaction A transaction B account x: balance 1000 € “I will take 200 €” “I will take 500 €” S-lock S-lock 1. Read account x 3. balance = balance -200 5. Write account x 2. Read account x 4. balance = balance -500 6. Write account x Wait ! Wait ! X-lock? X-lock? time Martti Laiho

  23. Deadlock Wait T1 T2 A Cycle of Lock Waits Modern DBMS systems will detect the deadlock in some seconds (deadlock detection)‏ and solve the waiting cycle - selecting the victim - making automatic Rollback (not Oracle)‏ - send error message to the application => Application must react on the deadlock ! Wait Wait T1 T2 Wait Rollback Martti Laiho 1998

  24. ..Typical anomalies 1. The Lost Update Problem C. J. Date: Lost Update - Applying the locking scheme: ”Tellers” transaction A transaction B account x: balance 1000 € “I will take 200 €” “I will take 500 €” S-lock S-lock 1. Read account x 3. balance = balance -200 5. Write account x 2. Read account x 4. balance = balance -500 6. Write account x Wait ! Wait ! X-lock? X-lock? Deadlock detected Find a “victim” and which solves the problem Rollback ! Martti Laiho

  25. ..Typical anomalies 1. The Lost Update Problem C. J. Date: Lost Update - solved by locking scheme: ”Tellers” transaction A transaction B account x: balance 1000 € “I will take 200 €” “I will take 500 €” S-lock Retry the transaction of B ? Read account x balance = balance -200 Write account x Read account x balance = balance -500 Write account x X-lock Commit time Martti Laiho 1998 Martti Laiho

  26. Retryer Pattern Errors Restart? Exceptions Start Restart? multiprogramming - limit, timeout syntax error in dynamic SQL reoptimising - objects/privileges serializability - conflict - deadlock - timeout services of - DBMS - buffers, etc - OpSys - data communication - HW YES [reconnect] Set Transaction ... Select ... if .. Update if .. Commit if .. deadlock? timeout? livelock? .. not found ! integrity ! - Uniqueness - Referential - Check YES - pause Abort YES? Rollback Martti Laiho

  27. Non-Repeatable Read‏ C.J. Date transaction A transaction B 1. SELECT ... FROM table WHERE ... ; result set1 2. UPDATE table SET c = ... WHERE ... ; DELETE FROM table WHERE ... ;… COMMIT; xxx 3. SELECT ... FROM table WHERE ... ; result set2 4. COMMIT;

  28. Phantom Read‏ transaction A transaction B 1. SELECT ... FROM table WHERE ... ; result set1 • INSERT INTO table (..)VALUES ( ... ) ; • UPDATE … SET col = <matching value> • WHERE .. • COMMIT; 3. SELECT ... FROM table WHERE ... ; result set2 Phantom row(s) 4. COMMIT

  29. Isolation Levels of ISO SQL Lost Update NOT possible NOT possible NOT possible NOT possible Anomalies: Isolation Level: READ UNCOMMITTED READ COMMITTED REPEATABLE READ SERIALIZABLE Dirty Read Possible ! NOT possible NOT possible NOT possible Nonrepeatable Read Possible ! Possible ! NOT possible NOT possible Phantoms Possible ! Possible ! Possible ! NOT possible Isolationlevelscanbeexplainedbyobjects and duration in S-lockingpreventingonly the transactionitselfagainstcertainanomalies, butcan’tpreventconcurrenttransactionsfromdirtyreads, etc i.e. can’tprovidestrictisolationas definedbyHaerder and Reuter

  30. SQL-92: Isolation Levels Phenomena: Isolation Level: READ UNCOMMITTED READ COMMITTED REPEATABLE READ SERIALIZABLE Lost Update NOT possible NOT possible NOT possible NOT possible Dirty Read possible ! NOT possible NOT possible NOT possible NonRepeatable Read possible ! possible ! NOT possible NOT possible Phantoms possible ! possible ! possible ! NOT possible The Lost Update Problem(case 1) in context of a single transaction is considered solved! Martti Laiho

  31. Cursor Processing • Solves the paradigm mismatch between • Procedural Programming and • (”Relational”) SQL databases • Scrolling / Forward only • Sensitive / insensitive (snapshot) • Server-side / client-side cache • Optimistic concurrency • Scope: transaction / (holdable) multiple transactions • Options (hints) Martti Laiho

More Related