Stimulating DEADLOCK in SQL Server

Here in this article we are trying to stimulate Deadlock situation, we are trying to create two table table1 and table2 for the same purpose

create table table1

(id int identity(1,1),

col2 int)

create table table2

(id int identity(1,1),

col2 int)

Command(s) completed successfully.

Now lets insert few records in both the table.

insert into table1

select 10 union all

select 20 union all

select 30

insert into table2

select 100 union all

select 200 union all

select 300

No w open a session by opening a new query window in SSMS, I will call it session1 and try to execute the below code, here we opened a transaction then updating table1 col2 where id =1

begin tran

       update table1

       set col2 = col2 + 10

       where id = 2

Now open another query window (second session) in SSMS and execute the below statement, here we are trying to update both the table the second table will wait for first transaction to unlock table1 for its update. And it goes on execution for infinite time.

begin tran

       update table2

       set col2 = col2 + 100

       where id = 2

       update table1

       set col2=col2 + 10

       where id = 2

commit tran

Now again come back to session1 window and execute the below code

update table2

set col2 = col2 + 100

where id = 2

now as you run this above statement on session1, SQL Server sense deadlock since the above code wait for table2 to get unlocked by session2 and same way session2 waits for session1 for table1 to unlock and DEADLOCK occurs, in this case SQL server chosen session1 as victim and abort session1 with a message

Msg 1205, Level 13, State 45, Line 7

Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

And session 2 will complete automatically.

There are no nights for SQL Developers, if so please give me address  J

Post Reference: Vikram Aristocratic Elfin Share

Truncate can be Rollback in same session

When you are surrounding TRUNCATE in transaction block you can rollback Truncated table in the current session.

If you close the session where Truncate is surrounded by transaction block, you cannot rollback the table unlike DELETE.

Here we have a table with only one record

select * from temp2

id          c_address  zip

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

1           delhi      326595

(1 row(s) affected)

Below we are opening a transaction block and truncating the table later we are doing rollback, to check the intermediate result we have put select statement in between.

Lets execute the transaction block and see the result of rollback on truncate.                                                                                                 

begin transaction   

       select * from temp2

       truncate table temp2

       select * from temp2

rollback tran

      

id          c_address  zip

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

1           delhi      326595

(1 row(s) affected)

id          c_address  zip

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

(0 row(s) affected)

So here we saw before truncate we had one record and after truncate we had no records.

Now the next statement which we fired is rollback, here we need to check whether truncate statement gets rollback or not.

Lets fire the select query to see what affect it made on truncate after rollback

select * from temp2

id          c_address  zip

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

1           delhi      326595

Ah! Truncate has no affect when it is followed by rollback in transaction block like Delete Command.

Conclusion: Truncated record can be rollback in the same session.

Few minute spent with you my SQL Server, rejuvenate for hours  :)

Post Reference: Vikram Aristocratic Elfin Share

Nested Transaction III: Killing open transaction

Previous Article on Transaction:

Some witty finding on Nested Transaction with Rollback and Commit

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

We will see how to kill open transaction in a particular session, here below I m writing a block which will create two open transaction

For writing this script, I opened a session, just a new sql window, and executed the below script  

begin tran firstTran

      insert into TAB_NestedTransaction_Demo values(1)

    

      begin tran secondTran

            insert into TAB_NestedTransaction_Demo values(2)

Command(s) completed successfully.

With the help of DBCC OPENTRAN command, we can check the session and the transaction detail of open transaction. Here we firing DBCC command to check the session and transaction

dbcc opentran           

Transaction information for database 'Sparsh'.

Oldest active transaction:

    SPID (server process ID): 53

    UID (user ID) : -1

    Name          : outerTran

    LSN           : (1366:4126:1)

    Start time    : Jan  9 2014  8:51:38:750PM

    SID           : 0xa578602ded7cd44c96d1358f246a1c79

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

So from the output of DBCC we can see that the open transaction “outerTran” exist in the session 53.

So now to kill this transaction we need to kill the session but you cannot kill a session begin inside the same session environment, so to kill a particular session, you need to open a new session and fire KILL command with session ID.

So here we opened a  new SQL window and fire the kill command with session id 53 where the transaction are in active state.

kill 53

Command(s) completed successfully

After killing the session if we fire DBCC OPENTRAN, we can see that there are no active transaction alive.

dbcc opentran

No active open transactions.

Every code is genius at least once in his life. The real geniuses are programmers who keep those codes bright everlastingly.

Post Reference: Vikram Aristocratic Elfin Share

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