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

Changing the default behavior of DML statements (INSERT/UPDATE/DELETE)

Generally we are using DML statements like Insert for inserting data into database table likewise Update for updating existing data in database table, but it is also possible to change the default behavior INSERT/UPDATE/DELETE (DML) statements for example while inserting data using INSERT statement, the new record may applies as an update to the table or even applies as a delete to the table.

You can implement the changing behavior with INSTEAD OF TRIGGER. Let’s do some practical to achieve the changing behavior of DML statements.

use testDemo

go

if object_id('ChangingDMLBehavior') is not null

      drop table ChangingDMLBehavior

go

     

We were checking the existence of 'ChangingDMLBehavior' table object.

Now lets create a table 'ChangingDMLBehavior'

create table ChangingDMLBehavior

(id int not null identity(1,1),

name varchar(10))

Command(s) completed successfully.

Table is now ready; it’s time to write an INSTEAD OF TRIGGER to stimulate the changing behavior of INSERT statement.

 create trigger trg_DMLBehavior on ChangingDMLBehavior

instead of insert

as

begin

      select * from inserted

      select * from deleted

      return

end

go

Command(s) completed successfully.

Now since Trigger is in place, now it’s time to try out INSERT statement on 'ChangingDMLBehavior' and check out the behavior of INSERT Statement.

 insert into ChangingDMLBehavior(name) values('Binny')

Now querying the table to find the inserted records, to our shock we found no records.

select * from ChangingDMLBehavior

id          name

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

(0 row(s) affected)

This is because a record is inserted to table 'ChangingDMLBehavior' with INSERT command, but nothing happens to the table due to the instead of trigger. This is because the trigger does not tell SQL Server to perform any data manipulation operation.

Let’s do some modification of INSTEAD OF TRIGGER to allow INSERT OPTION on table.

alter trigger trg_DMLBehavior on ChangingDMLBehavior

instead of insert

as

begin

      set nocount on

      insert into ChangingDMLBehavior(name)

            select name from inserted

      return

end

go

Command(s) completed successfully.

Now we can fire INSERT statement on 'ChangingDMLBehavior' table and can expect insertion of record to the table. Lets insert one record into 'ChangingDMLBehavior'.

insert into ChangingDMLBehavior(name) values('Prachi')

(1 row(s) affected)

Querying the table we get

select * from ChangingDMLBehavior

id          name

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

1           Prachi

(1 row(s) affected)

Instead of triggers are useful when you want some setting kind of table in database to hold the defined values.

When you are in love with your code you will stop dreaming code with closed eyes because reality better than your dreams

Post Reference: Vikram Aristocratic Elfin Share

Inserting data into a table that has just one column which is identity type???

If you have a table with one column, which is promoted with an identity column and you were told to insert rows into the table, how you will be performing the command?

Let’s stimulate the case by taking a table OneColumnTable with only one column id

create table OneColumnTable

(id int identity(1,1))

Now try to insert record in this table using insert statement

insert into OneColumnTable values(1)

//OUTPUT

Msg 8101, Level 16, State 1, Line 1

An explicit value for the identity column in table 'OneColumnTable' can only be specified when a column list is used and IDENTITY_INSERT is ON.

So it is not allowing to insert record in the table, but then you got a task to insert data into this table, how to do the insertion, lets fire the below query

insert into OneColumnTable default values

(1 row(s) affected)

select * from OneColumnTable

id

-----------

1

(1 row(s) affected

Note: If you want to continue inserting identity values, then you can make use of default value clause

And, If you want to insert explicit values then you can write

set identity_insert oneColumnTable on

insert into OneColumnTable(id) values(5);

set identity_insert oneColumnTable on

(1 row(s) affected)

select * from OneColumnTable

id

-----------

1

5

(2 row(s) affected)

That’s how you can insert values in identity column. J

Put out of your mind, it’s coding; very straightforward J   

Post Reference: Vikram Aristocratic Elfin Share

Multiple Value Inserts Using a Single INSERT Statement

With the introduction of row constructor in SQL 2008, you can now insert multiple values using a single INSERT statement.

With the row constructor, you can now insert multiple values using a single INSERT statement. In SQL Server 2000 or SQL Server 2005, to insert multiple rows to a table, you would have need of one INSERT statement for each record.   To exemplify, to insert the multiple records to EmpTest Table, you would do something like the following

insert into EmpTest values('sakshi',2)

insert into EmpTest values('Aishwarya',3)

insert into EmpTest values('Arti',2)

(1 row(s) affected)

(1 row(s) affected)

(1 row(s) affected)

The individual insert into... values statement can now be cut down with the row constructor.  The script above will now look as follows:

insert into EmpTest values('Vibha',1),

                        ('Sandhya',3),

                        ('Aurpita',1)

(3 row(s) affected)

SELECT with VALUES Clauses in the INSERT Statement Row Constructor

It allow a combination of specifying a list of values using the VALUES clause and getting data from another table using the SELECT statement

Note: Subqueries are allowed as long as the subquery returns only a single column and a single row

insert into EmpTest values('Paavai',2),

                          ('Edwina',3),

((select o.name from OneRowTable o where o.id =1),

                          (select '3'))

select * from EmpTest

emp_id      emp_name   dept_id

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

1           sakshi     2

2           Aishwarya  3

3           Arti       2

4           Vibha      1

5           Sandhya    3

6           Aurpita    1

7           Paavai     2

8           Edwina     3

9           vikram     3

(9 row(s) affected)

Enclose grand code

Post Reference: Vikram Aristocratic Elfin Share