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

Event Wiring - Can lead to Memory Leaks

Hi,

Here I’ll be covering the how the Event Wiring can lead to a big memory leak. I’m using ANTS Profiler to come up to this case study.

I have never noticed that the Event Wiring can lead to a big Memory leak problem.

Notice the below code – Leading to lot of Memory leak

public delegate void MyDelegate();

public partial class TemplatePage : System.Web.UI.Page

{

protected void Page_Load(object sender, EventArgs e)

{

MyEventClass.MyEvent += new MyDelegate(MyEventClass_MyEvent); // Notice the code is not kept in if(!IsPostBack)

}

void MyEventClass_MyEvent()

{}

}

public class MyEventClass

{

public static event MyDelegate MyEvent;

}

In order to test the code, run ANTS Profiler and refresh the web page 30 times. Now if you see the report, you’ll find there are MyDelegate object which are still live.

Approach – 1

In order to find the root cause of this memory leak, make a small change in the MyEventClass as below.

protected void Page_Load(object sender, EventArgs e)

{

MyEventClass ev = new MyEventClass();

ev.MyEvent += new MyDelegate(MyEventClass_MyEvent);

}

public class MyEventClass

{

public event MyDelegate MyEvent; // Static is removed

}

In order to test the code, run ANTS Profiler and refresh the web page 30 times. Now if you see the report, you’ll find there are no MyDelegate object which are still live.

What is the solution -

- The answer to this particular question is that the event decalred in MyEventClass is static.

- Thus event bidning in Page_Load will survive as long as the web application process continues to run.

- The net effect is that the MyStaticClass is having a new EventHandler i.e MyDelegate attached at every page load.

- The solution is to create MyEvent as a public class, rather than static. Thus new MyStaticClass will be created at every page load, and disposed of when the page has finished loading.

Approach – 2

- There are places where you might not be in the situation to change the Event class code as we did above. For e.g. for the system defined events there is very less provision for the code change.

- For e.g

SiteMap.SiteMapResolve += new SiteMapResolveEventHandler(SiteMapHandler.SiteMap_SiteMapResolve);

- In such cases best is to avoid the event binding multiple times, and make it just once.

To do this it can be done in various ways. For e.g

- Using !IsPostBack

- Session_Start in Global.asax – In this case you need to define a Static method in some class which will work as the Event Handler

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

MyEventClass.MyEvent += new MyDelegate(MyEventClass_MyEvent);

}

}

void Session_Start(object sender, EventArgs e)

{

SiteMap.SiteMapResolve += new SiteMapResolveEventHandler(MyClass.SiteMap_SiteMapResolve);

}

In order to test the code, run ANTS Profiler and refresh the web page 30 times. Now if you see the report, you’ll find there are no MyDelegate object which are still live.

One more advantage over the first solution is even it saved multiple object creation and hence the GC cycles.

Reference - http://www.red-gate.com/Products/ANTS_Profiler/technical_papers/finding_memory_leaks.htm

Thanks & Regards,

Arun Manglick || Senior Tech Lead

Gotcha - Session Start & Sesssion_End Event

Hi,

Below covers the gotcha with SessionStart and SessionEnd.

Session Start & Sesssion_End Event

Ø Start event fires only for the one time when the very first page is requested in the browser. i.e it does not fire for the any number of next or same page request.

Ø Session End event fires when Session is timeouted out or LogOut fired using below code.

Ø Once End fires, Start event refires when again a very first page is requested in the browser.

System.Web.Security.FormsAuthentication.SignOut();

Session.Abandon();

Gotcha –

Ø Once the Start event is fired on the very first page request, if there is some link in that page, which opens another page in a new browser window, then it does not lead to fire Session Start.

Ø Now on this new page which is a new window - If you fire logout, then the Session End fires. This end event ends the session for the parent window (which opened the seperate window) as well.

Ø Once the Start event is fired on the very first page request. Now if you copy the url and paste in another browser window – This leads to to fire another Session Start.

Ø Now on this new page which is a new window - If you fire logout, then the Session End fires. But this end event ends the session of only the new window and not of the parent window (which opened the seperate window) as well.

In an all the Session State would be same for two separate Browser widnows, provided the new window has been opened from the parent widnow code. This is the Gotcha

But Session State would be different for two separate Browser widnows, provided the new window is opened by opening a new browser instance. This is the Gotcha

Thanks & Regards,

Arun Manglick || Senior Tech Lead