1 / 29

Transactions

Transactions. What are transactions. Often called a Unit of Work (UOW) It is a block of code that must succeed or fail as a single unit. ACID Test. Actions must pass the ACID test to be classified as a transaction Atomicity Consistency Isolation Durability. Atomicity.

athena-juarez
Download Presentation

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. Transactions

  2. What are transactions • Often called a Unit of Work (UOW) • It is a block of code that must succeed or fail as a single unit

  3. ACID Test • Actions must pass the ACID test to be classified as a transaction • Atomicity • Consistency • Isolation • Durability

  4. Atomicity • All data modifications must be both accepted and inserted or none of the modifications will be performed

  5. Consistency • Once data has been successfully applied or rolled back, all the data must remain in a consistent state

  6. Isolation • Any modification in one transaction must be isolated from any modifications in any other transaction. • A transaction must see the data from another transaction in its original state, or its completed state

  7. Durability • Once a transaction has finished, all its data modifications are in place and can only be changed by another transaction

  8. How are they defined • Started by a BEGIN TRANSACTION • Completed by a • COMMIT • ROLLBACK TRANSACTION

  9. Commit • Makes any changes done to a database permanent

  10. Rollback Transaction • A ROLLBACK TRANSACTION undoes any changes made to the database since the BEGIN TRANSACTION • It leaves the database as if nothing had occurred

  11. Program Responsibility • It is generally the program’s responsibility to determine if a COMMIT or ROLLBACK should occur • This can be determined by program logic • It can be for business rules reasons • It can be for error conditions

  12. Error Conditions • @@ERROR • XACT_ABORT

  13. @@ERROR • @@ERROR is a system variable that contains the return code for a SQL statement • A return code of 0 signifies a successful statement • A return code < 0 signifies an error • A return code > 0 signifies an informational message

  14. Example of @@ERROR BEGIN TRANSACTION INSERT c VALUES ('X') IF (@@ERROR <> 0) GOTO on_error INSERT b VALUES ('X') -- Fails reference IF (@@ERROR <> 0) GOTO on_error COMMIT TRANSACTION RETURN(0) on_error; ROLLBACK TRANSACTION

  15. XACT_ABORT • Also used for error checking • Terminates the batch without a COMMIT • Does an implicit ROLLBACK

  16. Example of XACT_ABORT SET XACT_ABORT ON BEGIN TRANSACTION INSERT c VALUES ('X') INSERT b VALUES ('X') -- Fails reference COMMIT TRANSACTION

  17. Notes about XACT_ABORT • It accomplishes the same thing, but obviously doing your own checking gives you better control over how the batch behaves. • In a professional environment, it is generally best to check @@ERROR and write code to handle the error. Additional code should be added to record the error condition for debugging purposes.

  18. Transaction Isolation Level • Controls the kind of locking that is done on a row when it is being read in the database • Read Uncommitted • Read Committed • Repeatable Read • Serializable

  19. Read Uncommitted Implements dirty read, or isolation level 0 locking, which means that no shared locks are issued and no exclusive locks are honored. When this option is set, it is possible to read uncommitted or dirty data; values in the data can be changed and rows can appear or disappear in the data set before the end of the transaction. This option has the same effect as setting NOLOCK on all tables in all SELECT statements in a transaction. This is the least restrictive of the four isolation levels.

  20. Read Committed Specifies that shared locks are held while the data is being read to avoid dirty reads, but the data can be changed before the end of the transaction, resulting in nonrepeatablereadsor phantom data. This option is the SQL Server default.

  21. Repeatable Read Locks are placed on all data that is used in a query, preventing other users from updating the data, but new phantom rows can be inserted into the data set by another user and are included in later reads in the current transaction. Because concurrency is lower than the default isolation level, use this option only when necessary.

  22. Serializable Places a range lock on the data set, preventing other users from updating or inserting rows into the data set until the transaction is complete. This is the most restrictive of the four isolation levels. Because concurrency is lower, use this option only when necessary. This option has the same effect as setting HOLDLOCK on all tables in all SELECT statements in a transaction.

  23. Setting Isolation Levels SET TRANSACTION ISOLATION LEVEL { READ COMMITTED | READ UNCOMMITTED | REPEATABLE READ | SERIALIZABLE }

  24. Isolation Levels • Defaults to Read Committed • For almost all programs this will be sufficient

  25. Transact-SQL Batches

  26. Building Test Tables • Go to my web page and get the file ‘student class.sql’ • Run this script in YOUR database ‘CCUSERXX’ • This will build the Student Class database. We will use the database often going forward

  27. Batch File to Load Data insert into tblLocation (building_id, room_id, room_capacity) values ('HOV', 209, 10); insert into tblLocation (building_id, room_id, room_capacity) values ('HOV', 103, 25); Commit transaction -- build location -- just do a series of inserts, nothing fancy Use ccuserxx Begin transaction insert into tblLocation (building_id, room_id, room_capacity) values ('CRA', 513, 25); insert into tblLocation (building_id, room_id, room_capacity) values ('SCI', 111, 20);

  28. -- build location -- just do a series of inserts, nothing fancy Use ccuserxx Delete from tblLocation Begin transaction insert into tblLocation (building_id, room_id, room_capacity) values ('CRA', 513, 25); insert into tblLocation (building_id, room_id, room_capacity) values ('SCI', 111, 20); insert into tblLocation (building_id, room_id, room_capacity) values ('HOV', 209, 10); -- note we are about try and insert a duplicate record insert into tblLocation (building_id, room_id, room_capacity) values ('HOV', 209, 10); Commit transaction Select * from tblLocation Error Conditions In Batch Files

  29. -- build location -- just do a series of inserts, nothing fancy Use ccuserxx Delete From tblLocation; Begin transaction insert into tblLocation (building_id, room_id, room_capacity) values ('CRA', 513, 25); IF (@@ERROR <> 0) GOTO on_error insert into tblLocation (building_id, room_id, room_capacity) values ('SCI', 111, 20); IF (@@ERROR <> 0) GOTO on_error insert into tblLocation (building_id, room_id, room_capacity) values ('HOV', 209, 10); IF (@@ERROR <> 0) GOTO on_error insert into tblLocation (building_id, room_id, room_capacity) values ('HOV', 209, 10); IF (@@ERROR <> 0) GOTO on_error Commit transaction Goto finished On_error: Rollback transaction Finished: Select * from tblLocation Batch File with Error Handling

More Related