01 - Transactions

Hi,

Here we’ll cover below:

- Concepts of transactions and locking.

- Types of Locks & Lock compatibility

- Locking issues - Blocking (Long-term locking), and Deadlocking

Understanding Transactions –

Here we’ll cover below:

- ACID Test

- Implicit Transaction

- Explicit Transaction

- Transaction Mark

- Best Practices

ACID Test –

Atomitcity	- Either All or None
Consistency	- When completed, a transaction must leave all data in a consistent state.
Isolation	- Modifications made by concurrent transactions must be isolated from the modifications made by any other concurrent transactions. - A transaction either sees data in the state it was in before another concurrent transaction modified it, or it sees the data after the second transaction has completed, but it does not see an intermediate state. - This is referred to as serializability because it results in the ability to reload the starting data and replay a series of transactions to end up with the data in the same state it was in after the original transactions were performed.
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.

Implicit Transaction –

- Transactions can be explicit or implicit.

- Implicit transactions occur when the SQL Server session is in Implicit Transaction Mode.

- While a session is in implicit transaction mode, a new transaction is created automatically after any of the following statements are executed – SELECT, CREATE, UPDATE, DELETE, DROP, ALTER, INSERT, GRANT , REVOKE, TRUNCATE, FETCH.

- The transaction does not complete until a COMMIT or ROLLBACK statement is issued

- Implicit transaction mode is enabled when SET IMPLICIT_TRANSACTIONS ON is executed. The default is OFF.

- You can see which user options are enabled by running DBCC USEROPTIONS. If the SET ANSI_DEFAULTS or IMPLICIT_TRANSACTIONS options appear in the result set, then the option is ON

Pros/Cons –

- Avoid using implicit transactions if possible, as they make it easier for connections to leave uncommitted transactions, holding locks on resources and reducing concurrency.

- Implicit transactions are useful for ensuring that database users are sure of any changes they make to the database; the user must make a decision as to committing or rolling back their transaction(s).

Explicit Transaction –

- Explicit transactions are those that you define yourself.

- Explicit transactions use the following Transact-SQL commands and keywords

BEGIN TRANSACTION	- Sets the starting point
ROLLBACK TRANSACTION	- Restores original data modified by a transaction, to the state it was in at the start of the transaction. - Resources held by the transaction are freed.
COMMIT TRANSACTION	- Ends the transaction if no errors were encountered and makes changes permanent. - Resources held by the transaction are freed.
BEGIN DISTRIBUTED TRANSACTION	- Allows you to define the beginning of a distributed transaction to be managed by Microsoft Distributed Transaction Coordinator (MS DTC). - MS DTC must be running locally and remotely.
SAVE TRANSACTION	- Issues a savepoint within a transaction, which allows you to define a location to which a transaction can return if part of the transaction is cancelled. - A transaction must be rolled back or committed immediately after rolling back to a savepoint.
@@TRANCOUNT	- Returns the number of active transactions for the connection. - BEGIN TRANSACTION increments @@TRANCOUNT by 1, and ROLLBACK TRANSACTION and COMMIT TRANSACTION decrements @@TRANCOUNT by 1. - ROLLBACK TRANSACTION to a savepoint has no impact.

- Few Examples –

BEGIN TRANSACTION Insert Into Mytable Values( ..........) COMMIT TRANSACTION

BEGIN TRANSACTION Insert into mytable1 values( ..........) Insert into mytable2 values( ..........) SAVE TRANSACTION ValuesInserted Update table mytable1 set …………. ROLLBACK TRANSACTION ValuesInserted COMMIT TRANSACTION

Syntax

BEGIN TRAN [ SACTION ] [ transaction_name | @tran_name_variable

[ WITH MARK [ 'description' ] ] ]

- WITH MARK 'description' WITH MARK is used to mark a specific point in the transaction log.

- When used, a transaction log restoration can be recovered to the point prior to the MARK.

Error Handling –

- You can use @@Errors to roll back a transaction if errors occur within it.

- This prevents partial updates and having to SET XACT_ABORT ON.

BEGIN TRANSACTION Insert into mytable1 values( ..........) Insert into mytable2 values( ..........) If (@@Error <> 0) GOTO ErrorHandler COMMIT TRANSACTION ErrorHandler: ROLLBACK TRANSACTION

Transaction Mark –

- Using the WITH MARK command with the BEGIN TRANSACTION statement places a named mark in the transaction log, allowing you to restore your log to this point.

- The transaction must contain at least one data modification for the mark to be placed in the log.

- This enables you to have a complete full backup and transaction log backup before the transaction committed.

BEGIN TRAN bookorderupdate WITH MARK UPDATE BookRepository.dbo.Books SET dtReceived = GETDATE() COMMIT TRAN bookorderupdate

RESTORE DATABASE BookRepository FROM DISK= 'J:\MSSQL\Backup\bookrepo_jul_17.bak' WITH NORECOVERY RESTORE LOG BookRepository FROM DISK = 'J:\MSSQL\Backup\bookrepo_jul_17_3pm.trn' WITH RECOVERY, STOPATMARK=' bookorderupdate'

- If this update is a mistake, you can restore the data up to the mark point, as show in the right column above.

XACT_ABORT

- The database option SET XACT_ABORT affects how errors are handled within a transaction.

- When set ON, DML statements within a transaction that raise a run-time error cause the entire transaction to roll back and terminate.

- When OFF, only the DML statement that raised the error is rolled back, and the rest of the transaction continues. Keep this option ON to ensure data consistency.

Best Practices for using Transactions

- Keep transaction time short.

- Minimize resources locked by the transaction.

- Narrow down rows impacted by INSERT, UPDATE, and DELETE statements.

- Add Transact-SQL statements to a transaction where they are relevant to that transaction only.

- Do not open new transactions that require user feedback within the transaction. Open transactions can hold locks on resources, and user feedback can take an indefinite length of time to receive. Gather user feedback before issuing an explicit transaction.

- Check @@ERROR after issuing a DML (Data Manipulation Language) statement. If there was an error, you can roll back the transaction.

- If possible, do not open a transaction when browsing data.

- Use and understand the correct isolation levels. We will review isolation levels further on in this chapter.

Thanks & Regards,

Arun Manglick || Senior Tech Lead