Transactions

There are basically three set of statements:

• DDL: Data Definition Language - Create and Alter statements fall in this category
• DML: Data Manipulation Language - Insert, Update and Delete statements fall in this category
• DCL: Data Control Library - Commit and Rollback statements fall in this category

DDL and DCL are permanent in nature and DML statements are actually treated as transactions. Transactions should follow atomicity i.e. All or none property i.e. if execution happens it will happen for all the statements in a transactions or else it would not happen for anyone.

In SQL server every command or statement(DML) is treated as one transaction.

So there are basically two types of transactions:

• Implicit: Where we do not need to add any keyword from our side to begin and end a transaction. SQL server itself adds it at the beginning and completion of each statement.

• Explicit:  Where we need to add "Begin Tran" at the beginning and 'commit or rollback" at the end of the statement or statements.

Example:

BEGIN TRANSACTION

Statement 1

Statement 2

       .

       .

       .

Statement n

COMMIT OR ROLLBACK

Commit is meant for completing the transaction while Rollback for reverting back the transaction

When we are talking about the transaction, you should know about the concept of primary key. It helps in uniquely identifying the rows in a table.

There are two attributes of a primary key:

1. Unique

2. Not null: No 2 nulls can exist in a table

As already discussed in previous topics, we are having two files for a database .mdf and .ldf. .mdf file can have both committed or uncommitted data at any point of time. Whatever committed data is there will be redone called as redo phase and whatever uncommitted data is there will be rolled back called as undo phase.

So, how it works?

Lets take an example, suppose you were executing a transaction which consists of 1000 statements and in the middle of the execution power goes or system shuts down for any reason. Now, when the system comes up recovery starts. For recovery to happen, it goes through 3 phases:

1. Analysis

2. Undo

3. Redo

So, when system comes up, first SQL server analyses what statements it needs to redo and what needs to be undo. After that, uncommitted transactions are rolled back i.e. undo and committed ones are committed i.e. redo. This is how recovery happens in SQL server.

Now, what happens internally?

Suppose you have a box i.e. system PC1 which is having instance I1 i.e. PC1\I1 and 2 GB RAM is installed. As soon as data comes to .mdf it is copied to RAM in the form of pages and are called as clean pages. As soon as we make changes in the page that came from .mdf to RAM, it is called as Dirty Page. Now, whatever transactions are there related to the data that came from RAM are written in .ldf first.

Now, the question arises, if we are having only 2 GB RAM and all the data is getting copied on RAM, why does not it gets full then?

So for this, we have two background processes:

1. Lazy writer: This background process works on server level. It waits for RAM to reach the threshold limit e.g. RAM threshold is set to 80%, it will wait till 80% RAM gets full. When the RAM reaches the threshold value, lazy writer copies the dirty pages from RAM to .mdf and removes those pages which have been copied to .mdf from RAM. It flushes buffer pool(execution plan, compiled data, data pages and memory objects). Lazy writer follows FIFO(First In First Out) or LRU(Least Recently Used).

2. Checkpoint: This background process works on DB level. It is supposed to run at regular intervals. It copies dirty pages from RAM to .mdf and does not removes any page. It flushes only data pages to disk.

WAL: WAL stands for Write Ahead Logging Mechanism. It means that before writing data to .mdf data file, it is written in .ldf.

LSN: LSN stands for Log Sequence Number. It is a unique no. that is attached to every transaction. Every transaction has its own LSN which is unique.

How to see transaction Log?

• You should know the dbid i.e. database ID. For that, you can run          select * from sys.sysdatabases
• When you know dbid, you can run                                                       dbcc log(ID,3) 

Here, second argument can be either 0, 1, 2 or 3 based on how much detailed result you want.

To know the transaction information, you can also run

select * from fn_dblog(null, null)

To know about a particular transaction: 

select * from fn_dblog(null, null) where [Transaction Name]= 'DELETE'