1 / 13

Transactions and Transaction Locking

Transactions and Transaction Locking. IS421. What Is a Transaction. A transaction is a series of operations that are performed as one logical unit of work. Transactions allow SQL Server to ensure a certain level of data integrity and data recoverability.

yama
Télécharger la présentation

Transactions and Transaction Locking

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 Transaction Locking IS421

  2. What Is a Transaction • A transaction is a series of operations that are performed as one logical unit of work. • Transactions allow SQL Server to ensure a certain level of data integrity and data recoverability. • The transaction log, which every database must have, keeps a record of all transactions that make any type of modification (insert, update, or delete) to the database. SQL Server uses this transaction log to recover data in case of errors or system failures.

  3. ACID • “ACID" is an acronym for "atomicity, consistency, isolation, and durability” • SQL Server ensures that either • all data modifications in a transaction are completed as a group if the transaction is successful • or that none of the modifications occur if the transaction is not successful— • in other words, SQL Server ensures the atomicity of your transactions.

  4. ACID - Consistency • SQL Server also ensures the consistency of your transactions. • Consistency means that all data remains in a consistent state after a transaction finishes, regardless of whether the transaction failed or was completed successfully. • Before a transaction begins, the database must be in a consistent state, • After a transaction occurs, the database must be in a consistent state—a new state if the transaction succeeded or, if the transaction failed, the same consistent state it was in before the transaction started.

  5. ACID - Isolation • Isolation means that the effects of each transaction are the same as if the transaction were the only one in the system; • In other words, modifications made by a transaction are isolated from modifications made by any other concurrent transaction. If a transaction fails, its modifications will have no effect because the changes will be rolled back. • SQL Server enables you to adjust the isolation level of your transactions. A transaction's isolation behavior depends on the isolation level you specify.

  6. Concurrent Transaction Behavior • Dirty read Reading uncommitted data. Transaction #1 Begin ----------- write -----------------rollback Transaction #2 ---------Begin ----------- read ----commit • Nonrepeatable read Inconsistent results obtained by repeated reads. Transaction #1 Begin --------------read---------------read ------------- commit Transaction #2 ---------Begin -------------- write ----commit The results of Trans #1’s two reads are not the same • Phantom read A read that occurs when a transaction attempts to retrieve a row that does not exist when the transaction begins but that is inserted by a second transaction before the first transaction finishes. Transaction #1 Begin --------read-------------- commit Transaction #2 ------Begin ----------- write ----commit

  7. ACID-Isolation Levels • SQL Server supports four levels of isolation. • A higher isolation level increases data accuracy, but it can reduce the number of concurrent transactions. • A lower isolation level will allow more concurrency but will result in reduced data accuracy. • The four levels of isolation, from lowest to highest, are: • Read uncommitted Lowest level of isolation. At this level, transactions are isolated just enough to ensure that physically corrupted data is not read. • Read committed Default level for SQL Server. At this level, reads are allowed only on committed data. (Committed data is data that has been made a permanent part of the database.) • Repeatable read Level at which repeated reads of the same row or rows within a transaction will achieve the same results. (Until a transaction is completed, no other transactions can modify the data.) • Serializable Highest level of isolation; transactions are completely isolated from each other. At this level, the results achieved by running concurrent transactions on a database are the same as if the transactions had been run serially (one at a time in some order).

  8. Isolation Level Behaviors

  9. ACID- Durability • The last ACID property is durability. Durability means that once a transaction is committed, the effects of the transaction remain permanently in the database, even in the event of a system failure. • The SQL Server transaction log and your database backups provide durability. If SQL Server, the operating system, or a component of the server fails, the database will automatically recover when SQL Server is restarted. • SQL Server uses the transaction log to replay the committed transactions that were affected by the system crash and to roll back any uncommitted transactions. • If a data drive fails and data is lost or corrupted, you can recover the database by using database backups and transaction log backups. If you plan your backups well, you should always be able to recover your system from a failure. Unfortunately, if your backup drives fail and you lose the backup that is needed to recover the system, you might not be able to recover your database.

  10. Transaction Modes • Three transaction modes: • Autocommit (default) • Each transaction consists of just one T-SQL statement • Explicit • used most often for programming applications and for stored procedures, triggers, and scripts (Begin Transaction – commit or rollbacl) • Implicit • In implicit mode, a transaction automatically begins whenever certain T-SQL statements, such as DELETE, are used and will continue until explicitly ended with a COMMIT or ROLLBACK statement. • If an ending statement is not specified, the transaction will be rolled back when the user disconnects.

  11. Explicit Transactions • BEGIN TRANSACTION • Marks the starting point of an explicit transaction for a connection. • COMMIT TRANSACTION or COMMIT WORK • Used to end a transaction successfully if no errors were encountered. All data modifications made in the transaction become a permanent part of the database. Resources held by the transaction are freed. • ROLLBACK TRANSACTION or ROLLBACK WORK • Used to erase a transaction in which errors are encountered. All data modified by the transaction is returned to the state it was in at the start of the transaction. Resources held by the transaction are freed.

  12. Transaction Sample CREATE PROCEDURE SP_ChangeSupplier @FromSID INT,@ToSID INT, @Result INT OUTPUT AS -- Declare and initialize a variable to hold @@ERROR. DECLARE @ErrorSave INT Select @ErrorSave = 0 -- Begin Transaction Begin Transaction Update Products Set SupplierID = @ToSID WHERE SupplierID =@FromSID -- Check if the update is successful IF (@@ERROR <> 0) Begin Select @ErrorSave = -1 Rollback Transaction -- rollback end else commit Transaction -- commit Select @result = @ErrorSave GO

  13. Calling the SP declare @r int select @r = 100 exec SP_ChangeSupplier 30, 37, @r output print '>>' + ltrim(str(@r)) + '<<'

More Related