Transaction and Error Handling

1
Transaction and Error Handling

• Martin Bell
• SQL Server MVP

2
Transaction handling

• Why have transactions?
• Atomicity
• A transaction must be an atomic unit of work
  i.e. everything or nothing is performed.
• Consistency
• A transaction must leave all data in a consistent
  state, data integrity must be maintained.
• Isolation
• Modifications made by concurrent transactions
  must be isolated from the modifications made by
  any other concurrent transactions.
• Durability
• After a transaction has completed, its effects
  are permanently in place in the system. The
  modifications persist even in the event of a
  system failure.

3
Isolation Levels
4
Types of Transaction

• Implementation
• Auto commit
• Implicit (IMPLICIT_TRANSACTION)
• Explicit (BEGAN TRAN etc.)
• Scope
• Local
• Distributed

5
Transaction handling

• Where do you start your transaction?
• What if a transaction is already in progress?
• What happens when you commit/rollback?

6
Transaction handling

• Nested Transactions
• _at__at_TRANCOUNT

7
Method 1 Single Transaction

• CREATE PROCEDURE spr_singletransaction (
  _at_val_param varchar(5) )
• AS
• BEGIN
• SET NOCOUNT ON
• DECLARE _at_trn_cnt int
• SET _at_trn_cnt _at__at_TRANCOUNT
• IF _at_trn_cnt 0 BEGIN TRANSACTION
• INSERT INTO t1 ( val ) VALUES (_at_val_param)
• IF _at_trn_cnt 0 COMMIT TRANSACTION
• END

8
Method 2 Nested Transaction

• CREATE PROCEDURE spr_nestedtransaction (
  _at_val_param varchar(5) )
• AS
• BEGIN
• SET NOCOUNT ON
• DECLARE _at_trn_cnt int
• BEGIN TRANSACTION
• SET _at_trn_cnt _at__at_TRANCOUNT
• INSERT INTO t1 ( val ) VALUES (_at_val_param)
• IF _at_trn_cnt gt 0 COMMIT TRANSACTION
• END

9
Transactions in Triggers

• Rollback in triggers can cause problems!
• Statement batch is aborted after the trigger
• I recommend that you Raiserror and let procedural
  code handle rollback

10
Save Points

• What are save points?
• How do you use them?

Syntax SAVE TRAN SACTION savepoint_name
_at_savepoint_variable
11
Exception handling SQL2000

• SQL 2000 and earlier error handling was crude
• Relied on checking _at__at_ERROR after each statement -
  prone to programming errors
• Not all statements set _at__at_ERROR sometimes a
  procedure would be aborted immediately
• _at__at_ERROR may be reset to 0 by the next statement

12
When to check _at__at_ERROR

• After DML statement (Insert, Update, Delete )
• SELECT (when retrieving data!)
• EXECUTE
• COMMIT TRANSACTION
• CURSOR Statements
• TEXT manipulation

13
Example Procedure Aborting
CREATE PROCEDURE spr_simpleexample AS DECLARE
_at_stat int SET _at_stat 'A' SELECT _at__at_ERROR -- Never
gets executed! EXEC spr_simpleexample Msg 245,
Level 16, State 1, Procedure spr_simpleexample,
Line 3 Conversion failed when converting the
varchar value 'A' to data type int.
14
Exception handling SQL2005/8

• New Try/Catch blocks
• New error information procedures (only available
  in Catch block)
• ERROR_NUMBER()
• ERROR_MESSAGE()
• ERROR_SEVERITY()
• ERROR_STATE()
• ERROR_LINE()
• ERROR_PROCEDURE()

15
Example TRY/CATCH
CREATE PROCEDURE spr_addrowexample3 _at_id_param
int, _at_val_param varchar(5) AS BEGIN SET NOCOUNT
ON DECLARE _at_stat int BEGIN SET _at_stat
0 BEGIN TRY INSERT INTO dbo.t1 ( id, val
) VALUES ( _at_id_param, _at_val_param ) END
TRY BEGIN CATCH PRINT 'Error
Occurred No' CAST(ERROR_NUMBER() AS
VARCHAR(10)) ' Severity '
CAST(ERROR_SEVERITY() AS VARCHAR(10)) '
State ' CAST(ERROR_STATE() AS VARCHAR(10))
' Message ' ERROR_MESSAGE() SET
_at_stat ERROR_NUMBER() END CATCH RETURN
_at_stat END
16
Exception handling XACT_STAT

• XACT_STAT() function to return transaction state
• 1 The current request has an active user
  transaction. The request can perform any actions,
  including writing data and committing the
  transaction.
• 0 There is no active user transaction for the
  current request.
• -1 The current request has an active user
  transaction, but an error has occurred that has
  caused the transaction to be classified as an
  uncommittable transaction. The request cannot
  commit the transaction or roll back to a
  savepoint it can only request a full rollback of
  the transaction. The request cannot perform any
  write operations until it rolls back the
  transaction. The request can only perform read
  operations until it rolls back the transaction.
  After the transaction has been rolled back, the
  request can perform both read and write
  operations and can begin a new transaction.When a
  batch finishes running, the Database Engine will
  automatically roll back any active uncommittable
  transactions. If no error message was sent when
  the transaction entered an uncommittable state,
  when the batch finishes, an error message will be
  sent to the client application. This message
  indicates that an uncommittable transaction was
  detected and rolled back.

17
Exception handling XACT_STAT

• Allows better handling of transactions
• With save points you can rollback the work of a
  single stored procedure
• Allows retries e.g. deadlock handling

18
Exception handling XACT_STAT

• Use RAISERROR to re-throw an error
• Check error state and change if zero

19
Resources

• Home page http//www.microsoft.com/sql/default.msp
  x
• Technet http//www.microsoft.com/technet/prodtechn
  ol/sql/default.mspx
• Erland Sommarskog http//www.sommarskog.se/error-h
  andling-II.html