1 / 32

How transactions work

How transactions work. A transaction groups a set of Transact-SQL statements so that they are treated as a unit. Either all statements in the group are executed or no statements are executed .

mab
Download Presentation

How transactions work

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. How transactionswork A transaction groups a set of Transact-SQL statements so that they aretreated as a unit. Either all statements in the group are executed or nostatements are executed. Server automatically manages all data modification commands,including single-step change requests, as transactions. By default, eachinsert, update, and delete statement is considered a single transaction.

  2. How transactionswork You can use the following commands to create transactions: begin transaction – marks the beginning of the transaction block. Thesyntax is: begin {transaction | tran} [transaction_name] transaction_name is the name assigned to the transaction. It must conformto the rules for identifiers. Use transaction names only on the outermostpair of nested begin/commit or begin/rollback statements.

  3. TransactionGoals: ACID Atomic Transaction cannot be subdivided All parts must succeed or none may succeed Consistent All applicable constraints that are true before the transaction starts are also true after the transaction completes (example: referential integrity must be preserved) Isolated Changes resulting from the transaction are not visible to other users until the transaction completes Durable Changes resulting from the transaction are permanent and can't be undone (rolled back)

  4. TransactionProcessingIssues Problems arise when multiple users attempt to read from and write to the same records simultaneously Problems: Lost updates Dirty reads Nonrepeatable reads Phantom reads

  5. LostUpdates 10:00 PM: Both partners open code file on server and store it on local machines 4:17 AM: Partner 2, in a fit of guilt, looks at local program code, changes a few variable names and breaks what little was working, uploads it to server, then goes home. 3:03 AM: Partner 1 finishes coding, uploads file to server, and goes home, satisfied that program is working. Partner 2 is playing video games. Multiple transactions read a record and then update it. All updates except the last one are "lost". Example: 8:00 AM: Professor looks at non-working code, thinks bad thoughts about team, and assigns low grade. Solution:Don't allow record to be updated with another uncommitted update pending

  6. DirtyReads "Dirty block": data block that contains uncommitted data "Dirty read": competing transaction reads a record that contains uncommitted data Problem occurs if uncommitted data is rolled back after the read

  7. 7:45 AM: Partner 2 hands in assignment that contains non-working extra-credit code, not knowing it doesn't work. 10:00 PM: Partners finish project. Partner 1 wants to try to work a little longer and try to get extra credit points, although he's not sure he can do it. Partner 1 saves a backup copy of the working project but doesn't tell Partner 2 where it is. DirtyReads 8:00 AM: Professor grades non-working code, thinks bad thoughts about team, assigns low grade. 3:03 AM: Partner 1 works on extra credit but can't get it working. Goes home in a Mt. Dew-induced haze, planning to turn in original (backup) copy. Solution: Updates should not be visible to other connections until they are committed

  8. Nonrepeatable Read Occurs when a transaction reads the same record multiple times and gets different data each time Caused by other transactions updating the record between the reads

  9. PhantomReads Occurs when a query in a transaction reads a group of records multiple times and sees different records Caused by other transactions simultaneously inserting, updating, or deleting records so they now satisfy the query search condition.

  10. Auto CommitTransactions By default, each Transact-SQL command is its own transaction. These are known as automatic (orautocommit) transactions. They are begun and committed by the server automatically. You can think of an automatic transaction as a Transact-SQL statement that's ensconcedbetween a BEGIN TRAN and a COMMIT TRAN. If the statement succeeds, it's committed. If not, it's rolledback.

  11. User-DefinedTransactions User-defined transactions are the chief means of managing transactions in SQL Server applications. A userdefinedtransaction is user-defined in that you control when it begins and when it ends. The BEGIN TRAN,COMMIT TRAN, and ROLLBACK TRAN commands are used to control user-defined transactions. Here's anexample:

  12. User-DefinedTransactions SELECTTOP 5 title_id,stor_idFROM sales ORDERBYtitle_id,stor_id BEGINTRAN DELETEsales SELECTTOP 5 title_id,stor_idFROM sales ORDERBYtitle_id,stor_id GO ROLLBACKTRAN SELECTTOP 5 title_id,stor_idFROM sales ORDERBYtitle_id,stor_id

  13. title_idstor_id -------- ------- BU1032 6380 BU1032 8042 BU1032 8042 BU1111 8042 BU2075 7896 (5 row(s) affected) User-DefinedTransactions title_idstor_id -------- ------- BU1032 6380 BU1032 8042 BU1032 8042 BU1111 8042 BU2075 7896 (5 row(s) affected) (25 row(s) affected) title_idstor_id -------- ------- (0 row(s) affected)

  14. TransactionIsolationLevels SQL Server supports four transaction isolation levels. Atransaction's isolation levelcontrols how it affects, and is affected by, other transactions. The trade-off is always one of data consistencyvs. concurrency. Selecting a more restrictive TIL increases data consistency at the expense of accessibility. Selecting a less restrictive TIL increases concurrency at the expense of data consistency. The trick is tobalance these opposing interests so that the needs of your application are met. Use the SET TRANSACTION ISOLATION LEVEL command to set a transaction's isolation level. Valid TILsinclude READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, and SERIALIZABLE.

  15. TransactionIsolationLevels READ UNCOMMITTED: It is the least restrictive of SQL Server's four TILs. It permits dirty reads (reads ofuncommitted changes by other transactions) and nonrepeatable reads (data that changes between readsduring a transaction). To see how READ UNCOMMITTED permits dirty and nonrepeatable reads, run the following queries simultaneously:

  16. Query 2 SETTRANSACTIONISOLATIONLEVELREADUNCOMMITTED PRINT'Now you see it…' SELECTTOP 5 title_id,qtyFROM sales WHEREqty=0 ORDERBYtitle_id,stor_id IF@@ROWCOUNT>0 BEGIN WAITFORDELAY'00:00:05' PRINT'…now you don''t' SELECTTOP 5 title_id,qtyFROM sales WHEREqty=0 ORDERBYtitle_id,stor_id END TransactionIsolationLevels READ UNCOMMITTED: EXAMPLE: -- Query 1 SELECTTOP 5 title_id,qtyFROM sales ORDERBYtitle_id,stor_id BEGINTRAN UPDATEsalesSETqty=0 SELECTTOP 5 title_id,qtyFROM sales ORDERBYtitle_id,stor_id WAITFORDELAY'00:00:05' ROLLBACKTRAN SELECTTOP 5 title_id,qtyFROM sales ORDERBYtitle_id,stor_id

  17. While the first query is running (you have five seconds), fire off the second one, and you'll see that it's able toaccess the uncommitted data modifications of the first query. It then waits for the first transaction to finish andattempts to read the same data again. Since the modifications were rolled back, the data has vanished,leaving the second query with a nonrepeatable read. TransactionIsolationLevels READ UNCOMMITTEDEXAMPLE: Now you see it… title_idqty -------- ------ BU1032 0 BU1032 0 BU1032 0 BU1111 0 BU2075 0 (5 row(s) affected) …now you don't title_idqty -------- ------

  18. TransactionIsolationLevels READ COMMITTED: READ COMMITTED is SQL Server's default TIL, so if you don't specify otherwise, you'll get READCOMMITTED. READ COMMITTED avoids dirty reads by initiating share locks on accessed data but permitschanges to underlying data during the transaction, possibly resulting in nonrepeatable reads and/or phantomdata. To see how this works, run the following queries simultaneously:

  19. TransactionIsolationLevels READ COMMITTED: EXAMPLE: - Query 1 SETTRANSACTIONISOLATIONLEVELREADCOMMITTED BEGINTRAN PRINT'Now you see it…' SELECTTOP 5 title_id,qtyFROM sales ORDERBYtitle_id,stor_id WAITFORDELAY'00:00:05' PRINT'…now you don''t' SELECTTOP 5 title_id,qtyFROM sales ORDERBYtitle_id,stor_id GO ROLLBACKTRAN -- Query 2 SETTRANSACTIONISOLATIONLEVELREADCOMMITTED UPDATE sales SETqty=6 WHEREqty=5

  20. As in the previous example, start the first query, then quickly run the second one simultaneously (you have seconds). In this example, the value of the qty column in the first row of the sales table changes between reads during the first query—a classic no repeatable read. TransactionIsolationLevels READ COMMITTED EXAMPLE: title_idqty -------- ------ BU1032 5 BU1032 10 BU1032 30 BU1111 25 BU2075 35 …now you don't title_idqty -------- ------ BU1032 6 BU1032 10 BU1032 30 BU1111 25 BU2075 35

  21. TransactionIsolationLevels REPEATABLE READ: REPEATABLE READ initiates locks to prevent other users from changing the data a transaction accesses but doesn't prevent new rows from being inserted, possibly resulting in phantom rows appearing between reads during the transaction. Here's an example (as with the other examples, start the first query; then run the second one simultaneously—you have five seconds to start the second query):

  22. TransactionIsolationLevels REPEATABLE READ: EXAMPLE: -- Query 1 SETTRANSACTIONISOLATIONLEVELREPEATABLEREAD BEGINTRAN PRINT'Nothing up my sleeve…' SELECTTOP 5 title_id,qtyFROM sales ORDERBYqty WAITFORDELAY'00:00:05' PRINT'…exceptthisrabbit' SELECTTOP 5 title_id,qtyFROM sales ORDERBYqty GO ROLLBACKTRAN -- Query 2 SETTRANSACTIONISOLATIONLEVELREPEATABLEREAD INSERT sales VALUES (6380,9999999,GETDATE(),2,'USG-Whenever','PS2091')

  23. As you can see, a new row appears between the first and second reads of the sales table, even though REPEATABLE READ has been specified. Though REPEATABLE READ prevents changes to data it has already accessed, it doesn't prevent the addition of new data, thus introducing the possibility of phantom rows. TransactionIsolationLevels REPEATABLE READ EXAMPLE: Nothing up my sleeve… title_idqty -------- ------ PS2091 3 BU1032 5 PS2091 10 MC2222 10 BU1032 10 …except this rabbit title_idqty -------- ------ PS2091 2 PS2091 3 BU1032 5 PS2091 10

  24. TransactionIsolationLevels SERIALIZABLE: SERIALIZABLE prevents dirty reads and phantom rows by placing a range lock on the data it accesses. It isthe most restrictive of SQL Server's four TILs. It's equivalent to using the HOLDLOCK hint with every table atransaction references. Here's an example (delete the row you added in the previous example before runningthis code):

  25. TransactionIsolationLevels SERIALIZABLE EXAMPLE: SETTRANSACTIONISOLATIONLEVELSERIALIZABLE BEGINTRAN PRINT'Nothing up my sleeve…' SELECTTOP 5 title_id,qtyFROM sales ORDERBYqty WAITFORDELAY'00:00:05' PRINT'…or in my hat' SELECTTOP 5 title_id,qtyFROM sales ORDERBYqty ROLLBACKTRAN -- Query 2 BEGINTRAN SETTRANSACTIONISOLATIONLEVELSERIALIZABLE -- This INSERT will be delayed until the first transaction completes INSERT sales VALUES (6380,9999999,GETDATE(),2,'USG-Whenever','PS2091') ROLLBACKTRAN

  26. In this example, the locks initiated by the SERIALIZABLE isolation level prevent the second query from running until after the first one finishes. While this provides airtight data consistency, it does so at a cost of greatlyreducedconcurrency. TransactionIsolationLevels SERIALIZABLE EXAMPLE: Nothing up my sleeve… title_idqty -------- ------ PS2091 3 BU1032 5 PS2091 10 MC2222 10 BU1032 10 …or in my hat title_idqty -------- ------ PS2091 3 BU1032 5 PS2091 10 MC2222 10 BU1032 10

  27. Nested Transactions Transact-SQL allows you to nest transaction operations by issuing nested BEGIN TRAN commands. The@@TRANCOUNT automatic variable can be queried to determine the level of nesting—0 indicates no nesting,1 indicates nesting one level deep, and so forth. Batches and stored procedures that are nesting sensitiveshould query @@TRANCOUNT when first executed and respond accordingly.

  28. Nested Transactions Here's an example that illustrates some of nested transactions: SELECT'Before BEGIN TRAN',@@TRANCOUNT BEGINTRAN SELECT'After BEGIN TRAN',@@TRANCOUNT DELETEsales BEGINTRANnested SELECT'After BEGIN TRAN nested',@@TRANCOUNT DELETEtitleauthor COMMITTRAN nested -- Does nothing except decrement @@TRANCOUNT SELECT'After COMMIT TRAN nested',@@TRANCOUNT GO-- When possible, it's a good idea to place ROLLBACK TRAN in a separate batch -- to prevent batch errors from leaving open transactions ROLLBACKTRAN SELECT'After ROLLBACK TRAN',@@TRANCOUNT SELECTTOP 5 au_idFROMtitleauthor

  29. Nested Transactions In this example, we see that despite the nested COMMIT TRAN, the outer ROLLBACK still reverses theeffects of the DELETE titleauthor command. ----------------- ---------- Before BEGIN TRAN 0 ---------------- ---------- After BEGIN TRAN 1 ----------------------- ---------- After BEGIN TRAN nested 2 ------------------------ ---------- After COMMIT TRAN nested 1 ------------------- ---------- After ROLLBACK TRAN 0 au_id ----------- 213-46-8915 409-56-7008 267-41-2394 724-80-9391 213-46-8915

  30. SAVE TRAN and Save Points You can control how much work ROLLBACK reverses via the SAVE TRAN command. SAVE TRAN creates asave point to which you can roll back if you wish. Syntactically, you just pass the name of the save point to theROLLBACK TRAN command. Here's an example:

  31. SAVE TRAN and Save Points SELECT'Before BEGIN TRAN main',@@TRANCOUNT BEGINTRAN main SELECT'After BEGIN TRAN main',@@TRANCOUNT DELETEsales SAVETRAN sales -- Mark a save point SELECT'After SAVE TRAN sales',@@TRANCOUNT-- @@TRANCOUNT is unchanged BEGINTRANnested SELECT'After BEGIN TRAN nested',@@TRANCOUNT DELETEtitleauthor SAVETRANtitleauthor-- Mark a save point SELECT'After SAVE TRAN titleauthor',@@TRANCOUNT-- @@TRANCOUNT isunchanged ROLLBACKTRANsales SELECT'After ROLLBACK TRAN sales',@@TRANCOUNT-- @@TRANCOUNT is unchanged SELECTTOP 5 au_idFROMtitleauthor IF@@TRANCOUNT>0 BEGIN ROLLBACKTRAN SELECT'After ROLLBACK TRAN',@@TRANCOUNT END SELECTTOP 5 au_idFROMtitleauthor

  32. au_id ----------- 213-46-8915 409-56-7008 267-41-2394 724-80-9391 213-46-8915 ------------------- ---------- After ROLLBACK TRAN 0 au_id ----------- 213-46-8915 409-56-7008 267-41-2394 724-80-9391 213-46-8915 SAVE TRAN and Save Points ---------------------- ---------- Before BEGIN TRAN main 0 --------------------- ---------- After BEGIN TRAN main 1 --------------------- ---------- After SAVE TRAN sales 1 ----------------------- ---------- After BEGIN TRAN nested 2 --------------------------- ---------- After SAVE TRAN titleauthor 2 ------------------------- ---------- After ROLLBACK TRAN sales 2

More Related