Transactions and Transaction Locking

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 statea 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 1s 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
Behavior Allowed Behavior Allowed Behavior Allowed
Isolation Level Dirty Read No repeatable Read Phantom Read
Read uncommitted Yes Yes Yes
Read committed No Yes Yes
Repeatable read No No Yes
Snapshot No No No
Serializable No No No
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 _at_FromSID
  INT,_at_ToSID INT, _at_Result INT OUTPUT AS
• -- Declare and initialize a variable to hold
  _at__at_ERROR.
• DECLARE _at_ErrorSave INT
• Select _at_ErrorSave 0
• -- Begin Transaction
• Begin Transaction
• Update Products
• Set SupplierID _at_ToSID
• WHERE SupplierID _at_FromSID
• -- Check if the update is successful
• IF (_at__at_ERROR ltgt 0)
• Begin
• Select _at_ErrorSave -1
• Rollback Transaction -- rollback
• end
• else
• commit Transaction -- commit
• Select _at_result _at_ErrorSave
• GO

13
Calling the SP

• declare _at_r int
• select _at_r 100
• exec SP_ChangeSupplier 30, 37, _at_r output
• print 'gtgt' ltrim(str(_at_r)) 'ltlt'