Some witty finding on Nested Transaction II: Partial Rollback with Savepoint

Read Prev Article: Some witty finding on Nested Transaction with Rollback and Commit

Here in this article we will see how we can rollback a portion of transaction keeping other part as it is for commit. For revelation we are again taking the same EMP table, let see first, how many records are present in it.

  

select * from emp

emp_id      ename      dept_id

----------- ---------- -----------

1           neha       1

2           richa      1

3           sidhika    2

4           Sneha      1

6           Kavita     3

(5 row(s) affected)

Now I am writing a block which is nested by two transaction, in the inner transaction after inserting record with emp_id 8, I m trying to rollback the inner transaction while keeping the first (outer) transaction as it is for commit.

set nocount on

go

begin tran

      insert into emp values(7,'Sanjana',2)

      begin tran

            insert into emp values(8,'Monica',2)

            rollback tran

commit tran

Msg 3902, Level 16, State 1, Line 6

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

select * from emp

emp_id      ename      dept_id

----------- ---------- -----------

1           neha       1

2           richa      1

3           sidhika    2

4           Sneha      1

6           Kavita     3

(5 row(s) affected)

Here we saw, the first call to ROLLBACK TRAN in inner transaction rollback both the transaction the outer and inner one. So there must be no insert into the table emp. We can also check this using @@trancount.

Let’s print @@trancount in the same script.

set nocount on

go

begin tran

      insert into emp values(7,'Sanjana',2)

      print 'Trancount : ' + cast(@@trancount as varchar)

      begin tran

            insert into emp values(8,'Monica',2)

            print 'Trancount : ' + cast(@@trancount as varchar)

            rollback tran

      print'Trancount : ' + cast(@@trancount as varchar)

commit tran

Trancount : 1

Trancount : 2

Trancount : 0

Msg 3902, Level 16, State 1, Line 9

The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.

So after the rollback fired, both the opened transaction closed and we get @@trancount as 0.

Now let’s define the transaction with name so that we can explicitly mention which transaction to rollback

set nocount on

go

begin tran   outerTran

      insert into emp values(7,'Sanjana',2)

      print 'Trancount : ' + cast(@@trancount as varchar)

      begin tran InnerTran

            insert into emp values(8,'Monica',2)

            print 'Trancount : ' + cast(@@trancount as varchar)

            rollback tran  InnerTran

      print'Trancount : ' + cast(@@trancount as varchar)

commit tran outerTran

Trancount : 1

Trancount : 2

Msg 6401, Level 16, State 1, Line 7

Cannot roll back InnerTran. No transaction or savepoint of that name was found.

Trancount : 2

select * from emp

emp_id      ename      dept_id

----------- ---------- -----------

1           neha       1

2           richa      1

3           sidhika    2

4           Sneha      1

6           Kavita     3

7           Sanjana    2

8           Monica     2

So we can rollback on inner transaction has no effect and the out transaction committed both the insert in spite of having rollback on second transaction.

I am deleting employee with emp_id 7 and 8

delete emp where emp_id=7 or emp_id=8

Here I am using Savepoint, this will allow to rollback some part of transaction leaving other part as it is for commit. One more thing, one can’t use savepoint without transaction. Let’s see, how we are using savepoint in our earlier code..

set nocount on

go

begin tran   outerTran

      insert into emp values(7,'Sanjana',2)

      print 'Trancount : ' + cast(@@trancount as varchar)

      begin tran InnerTran

            save tran ManishPoint

            insert into emp values(8,'Monica',2)

            print 'Trancount : ' + cast(@@trancount as varchar)

            rollback tran  ManishPoint

      print'Trancount : ' + cast(@@trancount as varchar)

commit tran outerTran

select * from emp

emp_id      ename      dept_id

----------- ---------- -----------

1           neha       1

2           richa      1

3           sidhika    2

4           Sneha      1

6           Kavita     3

7           Sanjana    2 

Now you can see the inner transaction which was trying to insert employee with id 8 get rollbacked and leaving other part of transaction as it is to commit.

Watch your thought, they became logic

Watch your logic, they became code

Watch your code, they became program

Watch your program, crop up new thought

SDLC…cont..

Post Reference: Vikram Aristocratic Elfin Share

Some witty finding on Nested Transaction with Rollback and Commit

When we have transaction block defined inside transaction block, we call it as Nested transaction, and here in this article we will see how Rollback and Commit works with Nested transaction. Let’s start with Rollback

   

Nested Transaction with Rollback

We are creating a table with a single column for our demo.

create table TAB_NestedTransaction_Demo

(col1 int)

Command(s) completed successfully.

Now lets start with our transaction block, here we are creating a transaction block named “firstTran”, inside it we are first inserting a record in newly created table “TAB_NestedTransaction_Demo” thereafter again we are creating one more transaction block named “secondTran” in inside the second transaction block we are inserting one more record in “TAB_NestedTransaction_Demo” table. Then we are rollbacking the second transaction and checking the transaction count (number of active transaction)

begin tran firstTran

      insert into TAB_NestedTransaction_Demo values(1)

     

      begin tran secondTran

            insert into TAB_NestedTransaction_Demo values(2)           

      rollback tran secondTran

      select @@TRANCOUNT     

Msg 6401, Level 16, State 1, Line 6

Cannot roll back secondTran. No transaction or savepoint of that name was found.

No of Active Tran

-----------------

2

The above script throw an error while trying to rollback the inner transaction “secondTran”. And we can see the transaction count comes out as 2. That means none of the transaction gets rollbacked even after explicitly rollbacking innner transaction “secondTran”.Lets now execute the below lines and see the finding

rollback tran firstTran

go

select @@TRANCOUNT as [No of Active Tran]

No of Active Tran

-----------------

0

These two lines successfully gets executed and we can see transaction count comes out to be zero now.

Conclusion : from the above example we can see that  SQL server will allow you to rollback only the outer transaction and rollback of outer transaction will rollback all nested transactions and hence making the transaction count to zero.

Nested  Transaction with Commit

Lets now check out nested transaction with COMMIT, here we can see in the below code block we open first transaction firstTran then we inserted a record in TAB_NestedTransaction_Demo then we are opening one more transation inside the outer one and inserting one more record to the same table, thereafter we are committed second transaction. Then we rollback first transaction.

   

begin tran firstTran

      insert into TAB_NestedTransaction_Demo values(1)

      begin tran secondTran

            select @@TRANCOUNT [No of Active Tran from Inner]

            insert into TAB_NestedTransaction_Demo values(2)

      commit tran secondTran

      select @@TRANCOUNT [No of Active Tran from Outer]

rollback

[No of Active Tran from Inner]

-----------

2

[No of Active Tran from Outer]

-----------

1

Now lets query TAB_NestedTransaction_Demo.

select * from TAB_NestedTransaction_Demo

col1

-----------

(0 row(s) affected)

To our sock what we saw there is no records which get inserted in the table even after committing the inner transaction after inserting record into the table.

One more finding which we can see is, after committing the inner transaction, the transaction count reduced to one. Which says that the inner transaction is committed, but when doing select query on table, after executing last rollback statement, we found no records in the table?

Conclusion:  There is no effect in committing the inner transaction in Nested Transaction. And you cannot rollback inner transaction.  

Ask a programmer about his experience of living with code, he will say love, grace and beautiful…   

Post Reference: Vikram Aristocratic Elfin Share

Plan Caching VI - Forced Parameterization in Adhoc Queries

Read Previous Post:

• Plan Caching I - Adhoc Query Caching (SQL Server 2008)
• Plan Caching II - Careful consideration needed, if you want to use cached Adhoc Query Plan
• Plan Caching III - Compiled Plan Stub in Adhoc Query caching with _optimize for ad hoc workloads_ set to 1
• Plan Caching IV - Simple Parameterization in Adhoc Query Processing
• Plan Caching V - Disadvantage of Simple Parameterization

In the last article we saw that the datatype of constant is selected on fly depending upon the constant value by SQL Server, due to which the cached Prepared Parameterized plan is not been used by a similar query with constant little different.

Just check this, below example.  

dbcc FREEPROCCACHE

DBCC DROPCLEANBUFFERS

Go

select * from emp where emp_id = 1

go

Then executing this sys.dm_exec_cached_plans query

select usecounts, cacheobjtype, objtype, [text]

from sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

where usecounts > 0

AND

[text] not like '%dm_exec_cached_plans%' and [text] not like  '%dm_exec_sql_text%'

order by usecounts desc

go

usecounts cacheobjtype  objtype  text

--------  ------------- -------- --------------------------------------------

1         Compiled Plan Prepared (@1 tinyint)SELECT * FROM emp WHERE emp_id=@1

1         Compiled Plan Adhoc    select * from emp where emp_id = 1

From the output we can see that the constant used in query where predict is being treated as tinyint. So the datatype is of the constant is being selected on fly by SQL Server while making a prepared Parameterized cache plan.

Now let’s query the same select with contact value ranging out of tiny data type.

select * from emp where emp_id = 700

go

 

Then executing this sys.dm_exec_cached_plans query

select usecounts, cacheobjtype, objtype, [text]

from sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

where usecounts > 0

AND

[text] not like '%dm_exec_cached_plans%' and [text] not like  '%dm_exec_sql_text%'

order by usecounts desc

go

usecounts cacheobjtype  objtype  text

--------  ------------- -------- --------------------------------------------

1         Compiled Plan Prepared (@1 tinyint)SELECT * FROM emp WHERE emp_id=@1

1         Compiled Plan Adhoc    select * from emp where emp_id = 1

1         Compiled Plan Prepared (@1 smallint)SELECT * FROM emp WHERE emp_id=@1

1         Compiled Plan Adhoc    select * from emp where emp_id = 700

Here we can see from the output that it has not used the Prepared plan which gets generated when we executed query with predicate constant value as 1 and generate a new prepared plan and cached in memory; this time the constant is replaced by parameter and the type for the parameter is choosed as smallInt unlike tinyint in previous case.

So we get two prepared queries plan with two different parameter data type. So SQL Server is not making use of existing Parameterized cached plan.

The only way to force SQL Server to use the same data type  for both the queries is to enable PARAMETERIZED FORCED for the database.

dbcc FREEPROCCACHE

DBCC DROPCLEANBUFFERS

Go

alter database sparsh set parameterization forced;

go

Here we have set PARAMETERIZATION FORCED enabled. Once this is done SQL Server treats every kind constants as just a set of parameters.

Now since we have enabled PARAMETERIZATION FORCED. Lets run both the select query and query the dm_exec_cached_plans to check the output.

select * from emp where emp_id = 1

go

select * from emp where emp_id = 700

go

select usecounts, cacheobjtype, objtype, [text]

from sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

where usecounts > 0

AND

[text] not like '%dm_exec_cached_plans%' and [text] not like  '%dm_exec_sql_text%'

order by usecounts desc

go   

usecounts cacheobjtype  objtype  text

--------  ------------- -------- --------------------------------------------

2         Compiled Plan Prepared (@0 int)SELECT * FROM emp WHERE emp_id=@0

1         Compiled Plan Adhoc    select * from emp where emp_id = 1

1         Compiled Plan Adhoc    select * from emp where emp_id = 700

Here we can see that no new Prepared plan gets generated, instead the same prepared plan is referred by the second query with emp_id = 700. And the data type chosen by SQL Server for constant is INT which satisfy both the constant.

Though you need to be careful while setting this “parameterization forced” since setting this option “on” for the database, you are letting SQL Server assume and treat all constant as a same parameter, which may give poor performance. Alternatives are available, which I will soon post as an article.

If my girl friend judge my love to code is like putting them in a strait jacket and kicking them down a flight, then yes.. I have deep love to my code K   

Post Reference: Vikram Aristocratic Elfin Share

Plan Caching V - Disadvantage of Simple Parameterization

Read Previous Post:

• Plan Caching I - Adhoc Query Caching (SQL Server 2008)
• Plan Caching II - Careful consideration needed, if you want to use cached Adhoc Query Plan
• Plan Caching III - Compiled Plan Stub in Adhoc Query caching with _optimize for ad hoc workloads_ set to 1
• Plan Caching IV - Simple Parameterization in Adhoc Query Processing 

In   parameterization of Adhoc query (last article) we saw the a on the basis of constant it create parameterizated cached plan and store it in cache memory, but SQL Server makes its decision of the datatype of constant for example if you run this query after clearing cache and buffer

select * from emp where emp_id = 1

go

Then executing this sys.dm_exec_cached_plans query

select usecounts, cacheobjtype, objtype, [text]

from sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

where usecounts > 0

AND

[text] not like '%dm_exec_cached_plans%' and [text] not like  '%dm_exec_sql_text%'

order by usecounts desc

go

usecounts cacheobjtype  objtype  text

--------  ------------- -------- --------------------------------------------

1         Compiled Plan Prepared (@1 tinyint)SELECT * FROM emp WHERE emp_id=@1

1         Compiled Plan Adhoc    select * from emp where emp_id = 1

From the output we can see that the constant used in query where predict is being treated as tinyint. So the datatype is of the constant is being selected on fly by SQL Server while making a prepared Parameterized cache plan.

Now let’s query the same select with contact value ranging out of tiny data type.

select * from emp where emp_id = 700

go

  

Then executing this sys.dm_exec_cached_plans query

select usecounts, cacheobjtype, objtype, [text]

from sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

where usecounts > 0

AND

[text] not like '%dm_exec_cached_plans%' and [text] not like  '%dm_exec_sql_text%'

order by usecounts desc

go

usecounts cacheobjtype  objtype  text

--------  ------------- -------- --------------------------------------------

1         Compiled Plan Prepared (@1 smallint)SELECT * FROM emp WHERE emp_id=@1

1         Compiled Plan Adhoc    select * from emp where emp_id = 700

Here we can see from the output that the constant is replaced by parameter and the type for the parameter is choosed as smallInt unlike tinyint in previous case.

Now here we will again execute the first query and will see whether this prepared query plan which is cached up in memory will get referred.

select * from emp where emp_id = 1

go

lets check  sys.dm_exec_cached_plans query now

usecounts cacheobjtype  objtype  text

--------  ------------- -------- --------------------------------------------

1         Compiled Plan Prepared (@1 smallint)SELECT * FROM emp WHERE emp_id=@1

1         Compiled Plan Adhoc    select * from emp where emp_id = 700

1         Compiled Plan Prepared (@1 tinyint)SELECT * FROM emp WHERE emp_id=@1

1         Compiled Plan Adhoc    select * from emp where emp_id = 1

So the output of the sys.dm_exec_cached_plans query shows two adhoc query and two prepared queries where one prepared query has parameter of type smallint and another has parameter type as tinyint.

Conclusion: Because of the nature of SQL Server to automatically choose the data type of constant in the query make a parameterized cache plan, we are not able to reuse the same plan though the nature of 2^nd query is much similar to the first one.

So what is the option left.. Let’s check out in the next post same series ;)

Once my MBA friend pulled a knife on me. I asked her, the knife has butter on it, please don’t disturb, can’t u see I m writing code!!!… Why she is always angry with me…???

Post Reference: Vikram Aristocratic Elfin Share

Plan Caching IV - Simple Parameterization in Adhoc Query Processing

Read Previous Post:

• Plan Caching I - Adhoc Query Caching (SQL Server 2008)
• Plan Caching II - Careful consideration needed, if you want to use cached Adhoc Query Plan
• Plan Caching III - Compiled Plan Stub in Adhoc Query caching with _optimize for ad hoc workloads_ set to 1

Some time SQL Server consider some constant of Query as a Parameter, and when this happens, any query subsequently executed after that having a kind of similar template will use the same plan. We will check this by taking an example. Will try to execute sys.dm_exec_cached_plans to know the exact happening.

Now let’s set the 'optimize for ad hoc workloads' to 0

sp_CONFIGURE 'optimize for ad hoc workloads',0

RECONFIGURE

GO

Configuration option 'optimize for ad hoc workloads' changed from 0 to 0. Run the RECONFIGURE statement to install.

We need to free the Procedure cache and Buffer, so that we will get only our plan cached in the memory when we execute our set of query.

dbcc FREEPROCCACHE

DBCC DROPCLEANBUFFERS

Go

DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Now since our cache memory is free, let’s fire a SQL query and check the cache using DMV sys.dm_exec_cached_plans.

select * from emp where emp_id = 1

go

select * from emp where emp_id = 2

go

select * from emp where emp_id = 3

go

select usecounts, cacheobjtype, objtype, [text]

from sys.dm_exec_cached_plans

CROSS APPLY sys.dm_exec_sql_text(plan_handle)

where usecounts > 0

AND

[text] not like '%dm_exec_cached_plans%' and [text] not like  '%dm_exec_sql_text%'

order by usecounts desc

go

usecounts cacheobjtype  objtype  text

--------  ------------- -------- --------------------------------------------

3         Compiled Plan Prepared (@1 tinyint)SELECT * FROM emp WHERE emp_id=@1

1         Compiled Plan Adhoc    select * from emp where emp_id = 3

1         Compiled Plan Adhoc    select * from emp where emp_id = 2

1         Compiled Plan Adhoc    select * from emp where emp_id = 1

Here we can see that the plan is first compiled and cached in memory taking constant as parameter and when next time a query of similar kind came for execution, this plan which is cached in memory gets referenced by the executer.

And the rest three compiled plan is just stored to as an act of finding parameterized version of the query, if exact same query with same constant come for execution.

Code is a kind of color in programmers being, to aid & strikes the pond of contentment… 

Post Reference: Vikram Aristocratic Elfin Share