Posts Tagged sql server

Understanding and detecting deadlocks in Sql Server

In order to understand deadlocks, let us first create tables and have some sample data.

/* Tables and data  - Start */
IF OBJECT_ID('Order') IS NOT NULL
BEGIN
DROP TABLE [Order]
END

IF OBJECT_ID('Customer') IS NOT NULL
BEGIN
DROP TABLE Customer
END

CREATE TABLE Customer
(
ID int IDENTITY(1,1) CONSTRAINT PK_cid PRIMARY KEY ,
Name varchar(20)
)

CREATE TABLE [Order]
(
ID int IDENTITY(1,1) CONSTRAINT PK_oid PRIMARY KEY ,
CustomerId int,
OrderDate datetime
)

ALTER TABLE [Order]
ADD FOREIGN KEY (CustomerID)
REFERENCES Customer(ID)

INSERT INTO Customer (Name)
SELECT 'xxx'
UNION ALL
SELECT 'yyy'
UNION ALL
SELECT 'zzz'

INSERT INTO [Order] (CustomerID, OrderDate)
SELECT 1, GetDate()
UNION ALL
SELECT 2, GetDate()
UNION ALL
SELECT 3, GetDate()
/* Tables and data  - End*/

Open Sql Profiler and use the “TSQL_Locks” template.

image

In the “Event selection” tab, make sure you have “Deadlock Graph” selected and the click “Run”.

image

In SSMS, open two new query windows and execute the below batches in those two separate windows at the same time (Hit F5 in one window, switch to the another and hit F5).

Batch 1

/*Transaction A updates record A in table A - Waits and then updates record B in table B*/
BEGIN TRANSACTION
UPDATE Customer SET Name = 'aaa' WHERE ID=1
WAITFOR DELAY '00:00:10' -- Waiting for 10 secs
UPDATE [Order] SET CustomerId = 1 WHERE ID = 2
COMMIT TRANSACTION

Batch 2

/*Transaction B updates record B in table B - Waits and then updates the updates record A in table A */
-- This causes deadlock
BEGIN TRANSACTION
UPDATE [Order] SET OrderDate = GetDate() WHERE Id = 2
WAITFOR DELAY '00:00:10' -- Waiting for 10 secs
UPDATE Customer SET Name = 'bbb' WHERE Id=1
COMMIT TRANSACTION

You will notice that the batch 1 transaction took affect but because batch 2 couldn’t as that was identified as deadlock victim.

image

Look at the profiler now.

image

Notice that the Batch 2 didn’t get committed (See the big blue cross on the left circle and the tool tip on it in the above screenshot from the profiler?). However the Batch 1 did go through and took effect (see below).

image

Note that If the above batches (separate transactions) were updating two different records at the same time, there wont be any deadlocks.

/* Transaction A updates record A in table A waits and the updates record B in table B*/
BEGIN TRANSACTION
UPDATE Customer SET Name = 'aaa' WHERE ID=2
PRINT 'Customer updated...'
WAITFOR DELAY '00:00:10' -- Waiting for 10 secs
UPDATE [Order] SET CustomerId = 1 WHERE ID = 2
PRINT 'Order updated...'
COMMIT TRANSACTION

/*  Should be executed on a separate query window
Transaction B updates record C in table A waits and the updates record C in table B */
BEGIN TRANSACTION
UPDATE [Order] SET OrderDate = GetDate() WHERE Id = 3
PRINT 'Order updated'
WAITFOR DELAY '00:00:10' -- Waiting for 5 secs
UPDATE Customer SET Name = 'bbb' WHERE Id=3
PRINT 'Customer updated'
COMMIT TRANSACTION

image

,

Leave a comment

SQL Server – Generate the data script (insert script) for existing data in tables

How to generate the data script for SQL server 2008 database tables

Right click on the database where your table exists > Tasks > Generate Scripts

image

Click Next on the below screenimage

Choose “Select specific database objects” radio button, expand the tables and choose the table for which the data script needs to be generated and click Next.image

On the below screen, click “Advanced” and then select “Data only” option from the “Types of data to script”.
By default “Schema only” option is selected. You can also select “Schema and data” option for both creating table and also for the insert scripts.image

Choose where to save the insert script (File/Query window/Clipboard) and click on Next.You can see the progress and will see the generated insert script.
image

IMPORTANT NOTES  –
A) If your table has large amount of data (like mine in this case having 5000K records, Use the “Save to file” option to save the script and It will generate the script file containing all the insert queries just fine. If you use “copy to clipboad” or a “New Query Window option”, the generation will fail with Out of Memory Exception.

B) ALWAYS enclose the insert scripts in transaction with TRY CATCH block before running them. You don’t want partial inserts on your table – do you?

 

How to generate the data script for SQL server 2005 database tables

Good thing is you can generate the data script of SQL server 2005 database objects from SQL server 2008 management studio. This is how you do it.

a) In the SQL Management studio, right click on the database and select “Task > Generate Scripts ”. A wizard will be launched.
b)  Select the database you want to generate the script from.
c) In the “Choose Script option” step of the Wizard, select the “Script data” as true:-

clip_image002

d) Proceed on to generate the script and It will generate the data script as well.

,

3 Comments

Random Thoughts

The World as I see it

Simple Programmer

Making The Complex Simple

Ionic Solutions

Random thoughts on software construction, design patterns and optimization.

Long (Way) Off

A tragic's view from the cricket hinterlands