Transactions

1
Transactions
by Vern Rabe
2
Transaction Management

• Most of the following discussion of transaction
  management is specific to MS SQL Server
• But, the principles can be applied to virtually
  any DBMS

3
Transactions

• Transactions by definition are a logical unit of
  work
• A logical unit of work is a SQL operation or a
  set of SQL statements executed against a database
• A transaction can have two outcomes
• When it completes successfully, it is "committed"
  or "saved"
• When a transaction fails, it is "rolled back" or
  "undone"

4
Transactions

• Another definition is a single recoverable unit
  of work that executes either
• Completely
• Not at all
• A transaction can be anything from a single DML
  command to a series of commands (multiple inserts
  or deletes)

5
Outcomes

• After a transaction is committed, it can not be
  undone
• When a transaction is rolled back, all
  modifications of the transaction are undone

6
Outcomes (contd)

• Partial execution of a transaction is not allowed
• delete authors can only have two possible
  outcomes
• All rows are deleted (committed) or
• None of the rows are deleted (rolled back)

7
Need for Transactions

• Transactions are the result of business rules
  being applied to the database world.
• It is paramount in transaction development to
  make transactions as small as business rules
  allow, but not the slightest bit smaller

8
Need for Transactions

• These rules state that an operation either
  completes successfully or none of the operations
  can be applied
• In the following scenario, we will consider a
  bank teller machine

9
Need for Transactions

• The business function we are trying to apply is
  the transfer of funds from a savings to a
  checking account
• The amount debited from the savings account must
  be added to the checking account
• Both the debit and the credit must occur or
  neither must occur

10
Need for Transactions

• Here are the possible problems in transferring
  1000
• Partial transfer
• Money is debited, but not credited, e.g., if
  there is a power outage
• Another operation against your account could
  conflict with the transfer

11
Need for Transactions

• Here are the possible problems in transferring
  1000 (contd)
• Another operation could see invalid data
• The debit does not work, but the money is
  credited. A check for an amount greater than
  should be in the checking account is processed
  and approved
• Another operation could see data at the wrong
  time

12
Implementing

• Transaction management is implemented to cover
  the following issues
• Protect data from software, hardware, or power
  failure
• Provide access to multiple user
• Prevent simultaneous read and write of the same
  data by multiple users

13
Implementing

• Transaction control is implemented via three
  methods
• Locking
• Transaction control statements
• Error management

14
Data Storage

• How data is physically stored by SQL Server is
  beyond the scope of this class
• However, there is one principle that must be
  understood in order to continue with the next
  topic

15
Data Storage

• A table's data is stored in a series of pages
  called data pages
• SQL Server handles this page allocation
  internally and also "knows" where to find the
  particular data via a set of internal structures

16
Locking

• Locking is automatically handled by SQL Server
  via a process called the Lock Manager
• As reads or writes are performed, the lock
  manager places a lock on that resource
• This ensures that simultaneous transactions do
  not interfere with each other

17
Locking

• This also ensures that simultaneous transactions
  do not interfere with each other
• Without this locking, you may get data
  inconsistency in a multi-user environment
• The locking mechanism also reduces availability
  of data

18
Locking

• All locking decisions are handled by SQL Server
  (Almost hints)
• There are three levels of locking
• Row, page and table
• Row level locks are least restrictive, and the
  most common (default?) type
• Page locks lock an 8K page of data

19
Table Locks

• A table lock is the most restrictive lock
• As its name implies, its a lock that covers the
  entire table

20
Table Locks

• A table lock is implemented via a means called
  escalation (or hint)
• If a user is going to access a significant
  percentage of a table
• an update with no where clause
• SQL Server may escalate a row lock or a page lock
  to a table lock

21
Locking

• Obviously there can only be one table lock
• So, it would seem that you want to avoid this if
  at all possible. Well, maybe. Acquiring locks is
  memory and CPU intensive, so acquiring a table
  lock in some cases will provide better
  performance than individual row level locks

22
3 Types of Locks

• Shared
• Applied with a SELECT statement
• Multiple transactions can lock a shared resource
• No transactions can change the resource
• Usually released as soon as the resource is read
  (select has completed)

23
3 Types of Locks

• Exclusive
• Only one transaction can lock a resource
• Incompatible with all other locks
• Other transactions must wait until the lock is
  released
• Exists for the duration of the transaction

24
3 Types of Locks

• Update
• Allows shared, but will not allow exclusive or
  other update locks
• Usually becomes an exclusive lock when the
  resource is ready to be modified
• Is an internal lock to help avoid deadlocks
• Exists for the duration of the transaction

25
Lock Interactions

• Can another process
• Command Lock Select Modify
• select title_id shared yes no
• from titles
• delete titles exclusive no no
• where price gt 25
• insert into titles exclusive no no
• Values()
• update titles update, yes no
• set type gen then exclusive no no
• where type 'bus'
• Locking is affected by
• Indexes
• Transactions
• Isolation Levels

26
Isolation Levels

• ANSI standard defines four isolation levels
• READ UNCOMMITTED allows dirty reads (You can see
  data that has been changed, but not necessarily
  committed, by another user)
• READ COMMITTED (default) prevents dirty reads
• REPEATABLE READ prevents non-repeatable reads
• SERIALIZABLE prevents phantom reads

27
Isolation Levels

• The higher the isolation level, the higher the
  consistency
• The higher the isolation level, the lower the
  concurrency

28
Isolation Levels

• All higher levels include all of the restrictions
  of the lower levels
• READ UNCOMMITTED
• No shared locks are issued and no existing update
  or exclusive locks are honored for reads
• READ COMMITTED
• Exclusive lock on resources being changed.
• Shared locks on resources being searched.
  Release locks after object is processed.

29
Isolation Levels

• REPEATABLE READ
• Exclusive lock on resource being changed. Lock is
  held until end of transaction
• Shared lock on resource being searched. Lock is
  held until end of transaction
• SERIALIZABLE
• Exclusive lock on ranges of data being changed
• Shared lock on ranges of data being searched
• Hold all locks until end of transaction

30
Isolation Levels

• The default isolation level for SQL Server is
  READ COMMITTED
• The default isolation level for the ANSI-92 and
  ANSI-99 standards is SERIALIZABLE

31
Controlling

• ISOLATION LEVELs can be controlled at the query
  or session level
• Query
• Select fname, lname from authors WITH READ
  UNCOMMITTED
• Session
• SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
• Current isolation level can be determined from
  DBCC USEROPTIONS

32
Deadlock

• A deadlock occurs when two processes are waiting
  for a resource and neither can advance because
  the other prevents it from getting the resource.
• SQL Server detects this and aborts one of the
  transactions
• Two basic types
• Cycle deadlocks
• Conversion deadlocks (lock escalation)

33
Deadlock

• SQL Server will detect a deadlock and chooses the
  user with the least amount of CPU time as the
  "victim"
• Even the "winner" will see a significant decrease
  in performance

34
Deadlock

• Applications need to program for the possibility
  of a deadlock (error 1205)
• If a deadlock occurs, the application should
  resubmit the transaction

35
Avoiding Deadlocks

• To minimize the possibility of a deadlock
• Have all transaction access the tables in the
  same order
• Avoid long running transactions make
  transactions small and commit as soon as possible
• NEVER require user input while you have an open
  transaction
• Consider using cursors instead on single DML
  statements that affect many rows. Yes, sometimes
  the evil cursor has value.

36
Transaction Control

• Provides the control required for managing
  transaction
• Enables the grouping of SQL commands in a
  transaction that meet business requirements
• Enables a programmer to influence SQL Server's
  locking strategy

37
Transaction Control

• Creates predictable effects when committing or
  rolling back transactions
• begin transaction and commit transaction mark the
  beginning and end of a transaction

38
Transaction Control

• Implicit transactions exist for all DML
• Explicit transactions are created in SQL with
  BEGIN TRAN

39
Three Transaction Control Statememts

• BEGIN TRAN
• Alerts SQL Server that a transaction is
  beginning. You can optionally name a
  transaction.
• COMMIT TRAN
• End the transaction and saves changes to the
  database, or reduces the transaction nest level
  by one

40
Rollback

• Undoes the changes either to the named savepoint
  or the beginning of the transaction. Execution
  continues with the next statement. One rollback
  will reverse multiple begin tran statements.
• Before a commit is issued, a transaction can be
  either partially rolled back to a savepoint or
  completely rolled back
• After a commit is issued, a transaction can not
  be rolled back

41
Error Processing

• You can monitor a transaction through two global
  variables
• _at__at_error detects errors during/after statements
  execute
• _at__at_trancount reports the transaction nesting level

42
Error Handling

• If you are using insert statements in a
  transaction, you should always check _at__at_error
• begin tran
• insert
• if _at__at_error ltgt 0
• begin
• if _at__at_trancount gt 0 rollback tran
• return
• end
• If _at__at_trancount gt 0 commit tran

43
_at__at_rowcount

• _at__at_rowcount will tell you how many rows were
  affected by a statement
• An insert, update, or delete may affect more than
  one row
• A select into a variable may not return any rows
  which could cause invalid results later in the
  transaction

44
_at__at_rowcount

• If you expect rows and _at__at_rowcount 0
• Issue a rollback tran
• Issue a return to abort the transaction
• Trancount
• _at__at_Trancount indicates the transaction nest level
• Incremented with each begin tran
• Decremented with each commit
• Set to zero with rollback

45
Reporting Errors

• If an error occurs, we want to return a user
  friendly message of what happened.
• This is accomplished by using raiserror
• Develop a numbering system for error messages
• 20001 - 21000 update errors
• 21001 - 22000 insert errors...

46
Reporting Errors

• Standardize you error output
• Add a new error message with sp_addmessage

47
Report Errors Example

• exec sp_addmessage 40000, "An error occurred
  while updating '1!' table with a publisher ID
  of '2!'."
• declare _at_error int,
• _at_rows int
• begin tran
• update publishers set pub_id 'a' where pub_id
  '0736'
• select _at_error _at__at_error, _at_rows _at__at_rowcount
• if _at_error ! 0
• begin
• rollback tran
• raiserror 40000,'publishers','0736'
• return
• end...
• commit tran

48
Report Errors Example

• (results from previous example)
• Msg 40000, Level 16, State 1
• Line11
• An error occurred while updating publishers table
  with publisher ID of 0736.

49
Optimistic Concurrency

• Optimistic concurrency control works on the
  assumption that resource conflicts between
  multiple users are unlikely, but possible.
• Only when attempting to change data are resources
  checked to determine if any conflicts have
  occurred.

50
Optimistic Concurrency

• Implemented via use of timestamp datatype
• Not to be confused with datetime
• Only one column of type timestamp allowed per
  table
• Direct updates of timestamp columns not allowed
• All DML automatically modifies timestamp column,
  in an increasing fashion

51
SQL Review

• SQL
• Structured Query Language