deAttach database: batch

Showing posts with label batch. Show all posts

Wednesday, March 21, 2012

Deadlock Issue when dropping/creating tables

We are getting deadlock errors (sporadically) on a batch job we've created.

This job runs against a SQL Server 2000 back-end.

The first step of the batch job is to run a DDL script to drop and create 4 tables that are used in the job. The tables are only used during this job and are not accessed by any other process or application.

The second step of the batch job is to make an OSQL call to run the stored procedures associated with the job.

The deadlocks occur during the first step in the job, during the drop/create table statements. A sample follows:

Msg 1205, Level 13, State 54, Server SQL\APP_PROD, Line 7
Transaction (Process ID 78) was deadlocked on lock resources with
another process and has been chosen as the deadlock victim. Rerun the
transaction.

I am no DBA but can't understand how we can be getting a deadlock while dropping and creating tables that are used by no other processes or applications.

Any thoughts or help would be greatly appreciated.

Quote:

Originally Posted by DWiggin

We are getting deadlock errors (sporadically) on a batch job we've created.

This job runs against a SQL Server 2000 back-end.

The second step of the batch job is to make an OSQL call to run the stored procedures associated with the job.

The deadlocks occur during the first step in the job, during the drop/create table statements. A sample follows:

I am no DBA but can't understand how we can be getting a deadlock while dropping and creating tables that are used by no other processes or applications.

Any thoughts or help would be greatly appreciated.

if you ran your stored proc and then run it again, you'll have problem since it's still being used by the first one. possible locks will happen. try to create a tempoary table with randomly-generated table names...

Sunday, March 11, 2012

Deadlock & probably other problem

Hi,

We are having sql server 7. But recently when we executing a batch of SQL in that some updates were there & those updates causes triggers needs to be fired. But batch gets teminated in between. We checked the error logs & found that Few other processes are running continously like SPid 14 etc which are opening & closing the datafiles for pubs, & northwind database. Why? Is this a server settings problem Or Virus?

For deadlock is nested triggeres becomes problem after 3-4 level deep on multiple tables?

ThanksThere are a number of things which can make deadlocks more frequent.
One is long running transactions. Triggers cause the transaction to extend for the length of the trigger and also make the actual update less likely to be efficient so can cause the problem - nested triggers even more so.

>> opening & closing the datafiles
Which version do you have? It sounds like these databases are set to autoclose which is a bit odd - I would have said maybe it's a checkpoint but spid 14 doesn't sound like a system spid. Do you have anything monitoring databases or maybe backup software.

Try running this sp
http://www.nigelrivett.net/sp_nrSpidByStatus.html
it will tell you what the last statement executed by a spid is.
You could also use the profiler to see what is happenning but that will also slow down the system.|||Hi
Thanks For Reply.
For Triggers i reduced the transactions time & batch size.

For opening closing datafile the content in log files were like this

2003-07-08 08:39:08.00 spid33 Starting up database 'Northwind'.
2003-07-08 08:39:08.00 spid33 Opening file d:\SQLData\DATA\northwnd.mdf.
2003-07-08 08:39:08.12 spid33 Opening file d:\SQLData\DATA\northwnd.ldf.
2003-07-08 08:39:08.39 spid33 Closing file d:\SQLData\DATA\northwnd.mdf.
2003-07-08 08:39:08.43 spid33 Closing file d:\SQLData\DATA\northwnd.ldf.
2003-07-08 08:39:08.45 spid33 Starting up database 'pubs'.
2003-07-08 08:39:08.45 spid33 Opening file d:\SQLData\DATA\pubs.mdf.
2003-07-08 08:39:08.46 spid33 Opening file d:\SQLData\DATA\pubs_log.ldf.
2003-07-08 08:39:08.62 spid33 Closing file d:\SQLData\DATA\pubs.mdf.
2003-07-08 08:39:08.65 spid33 Closing file d:\SQLData\DATA\pubs_log.ldf.

This happening continously. In this case the SPID is 33.

Thanks|||If you run the following command in Query Analyzer:

sp_dboption pubs

Does the output include AutoClose?|||Just set the databases to not autoclose and it should get rid of that.

Use profiler to log any accesses to those databases and you will see why it's happenning.

Sounds like there is some monitoring going on somewhere - maybe someone keeps clicking on things in enterprise manager or has a gui which tries to get info from the databases.

DeadLock - INSERT with foreign keys

Hi,

In my database I have 2 tables - Batch & Device and a stored procedure - AddBatchDevice which accepts a XML input as follows –

And inserts one record in Batch Table and N number of records as specified in the Device table.

<Size/>

<HwId/>

</Device>

<HwId/>

</Device>

</Devices>

</BatchDevice>

When I call the stored procedure from multiple instances - its getting into deadlock (KeyLock) on the INSERT Statement (for Device table).

If the foreign key constraint between Batch and Device table is deleted – the deadlock doesn’t happen but I cannot remove the foreign key constraint.

Please let me know if there are other ways to resolve this deadlock.

Steps for repro:

1) Create a test Database in the server

2) Run the code snippet Create.sql on the test database (it will create 2 tables and one stored procedure)

3) Open two query windows for the test Database and execute the command listed in code snippet deadlock.sql simultaneously.

Code Snippet - Create.sql

Code Snippet

CREATE TABLE [dbo].[Batch](
[Id] [int] IDENTITY(1,1) NOT NULL,
[BatchGuid] [uniqueidentifier] NOT NULL,
[Size] [int] NOT NULL
CONSTRAINT [PK_Batch] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY],
CONSTRAINT [ukBatchGuid_Batch] UNIQUE NONCLUSTERED
(
[BatchGuid] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Device](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[Hwid] [bigint] NOT NULL,
[BatchId] [int] NOT NULL,
CONSTRAINT [PK_Device] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
CREATE NONCLUSTERED INDEX [IX_Device_BatchID] ON [dbo].[Device]
(
[BatchId] ASC
)WITH (PAD_INDEX = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY]

ALTER TABLE [dbo].[Device] WITH CHECK ADD CONSTRAINT [FK_Device_Batch] FOREIGN KEY([BatchId])
REFERENCES [dbo].[Batch] ([Id])
GO
ALTER TABLE [dbo].[Device] CHECK CONSTRAINT [FK_Device_Batch]
GO

CREATE PROCEDURE [dbo].[AddBatchDevice]
@.batchDeviceXML XML
AS
BEGIN

DECLARE @.batchId INT
DECLARE @.batchSize INT
DECLARE @.err INT
DECLARE @.rowCount INT
DECLARE @.itc INT

SET @.rowCount = 0
SET @.err = 1
SET @.itc = @.@.TRANCOUNT

SET NOCOUNT ON;

BEGIN TRY
IF (@.itc = 0) BEGIN TRANSACTION;

-- Populate the Batch Table

INSERT INTO dbo.Batch(BatchGuid, Size)
SELECT
BatchDetails.value('BatchGuid[1]','UNIQUEIDENTIFIER'),
BatchDetails.value('Size[1]','INT')
FROM @.batchDeviceXML.nodes('/BatchDevice') as R(BatchDetails)

SELECT @.err = @.@.ERROR, @.rowCount = @.@.ROWCOUNT, @.batchId = SCOPE_IDENTITY()

-- Check if the Batch got added.
IF (@.rowCount = 0) OR (@.err <> 0)
BEGIN
SET @.err = 13
GOTO ROLLBACKTRAN;
END

-- Retrieve Batch Size for the current Batch
SELECT @.batchSize = Size FROM dbo.Batch WHERE Id = @.batchId;

-- Populate the Device table
INSERT INTO dbo.Device (HwId, BatchId)
SELECT x.value('./HwId[1]','BIGINT') AS HwId,
@.batchId
FROM @.batchDeviceXML.nodes('/BatchDevice/Devices/Device') as R(x)

SELECT @.err = @.@.ERROR, @.rowCount = @.@.ROWCOUNT

-- Check if the BatchSize is same as number of Device Added.
IF (@.batchSize <> @.rowCount)
BEGIN
SET @.err = 6
GOTO ROLLBACKTRAN;
END

COMMITTRAN:
IF (XACT_STATE() = 1) and (@.itc = 0) COMMIT TRAN;
GOTO RETURNPOINT;

ROLLBACKTRAN:
IF (@.itc = 0) AND (@.@.TRANCOUNT > 0) and (XACT_STATE() <> 0) ROLLBACK TRAN;

RETURNPOINT:
RETURN @.err;

END TRY
BEGIN CATCH
-- rollback if the transaction is active and start from the proc
-- @.itc is the the initial transaction count when it enters the proc,
-- XACT_STATE() is zero when the transaction inactive
IF (@.itc = 0) AND (@.@.TRANCOUNT > 0) AND (XACT_STATE() <> 0)
BEGIN
ROLLBACK TRANSACTION;
END
-- Display error Message
-- EXEC [dbo].[sp_RethrowError];
PRINT ERROR_MESSAGE()

END CATCH

RETURN @.err
END
GO

Code Snippet: Deadlock

Code Snippet

DECLARE @.p1 XML
DECLARE @.guid NVARCHAR(50)

SET @.guid = CONVERT(NVARCHAR(50),newid())

SET @.p1=convert(xml,N'<BatchDevice><BatchGuid>' + @.guid + N'</BatchGuid><Size>100</Size><Devices><Device><HwId>725186448764602445</HwId></Device><Device><HwId>725133527699733597</HwId></Device><Device><HwId>725163621214188850</HwId></Device><Device><HwId>725360424970446632</HwId></Device><Device><HwId>725155285022984412</HwId></Device><Device><HwId>725344818310067191</HwId></Device><Device><HwId>725088231823400156</HwId></Device><Device><HwId>725358312743308913</HwId></Device><Device><HwId>725188965066112420</HwId></Device><Device><HwId>725236470409711434</HwId></Device><Device><HwId>725214869542733619</HwId></Device><Device><HwId>725269236196070583</HwId></Device><Device><HwId>725245337788308830</HwId></Device><Device><HwId>725286270478665392</HwId></Device><Device><HwId>725122001939204270</HwId></Device><Device><HwId>725264967238497755</HwId></Device><Device><HwId>725278832846587913</HwId></Device><Device><HwId>725320615075719837</HwId></Device><Device><HwId>725304873858777678</HwId></Device>

<Device><HwId>725330167025361822</HwId></Device></Devices></BatchDevice>')

EXEC dbo.[AddBatchDevice] @.BatchDeviceXML=@.p1

Go 1000

From looking at the code the deadlock should be coming from the non-clustered index on the device table. What I have seen in the past is that sql server will take a more agressive lock, for instance a page lock instead of a row lock. If you run a sql profile trace and capture the locks being acquired you will likely see that a higher lock is being taken. My suggestions as a quick fix is to use the with clause on the insert statement providing the rowlock directive. The syntax is INSERT INTO TableA WITH (ROWLOCK) ...

HTH.

-Chris

|||

I tried adding WITH (ROWLOCK) for the insert statement - stilll deadlocks are occuring.

Please let me know if you have any other suggestions.

Thanks,
Loonysan

|||

You need to set the isolation level to SNAPSHOT. In order to do so you must first set the ALLOW_SNAPSHOT_ISOLATION database option to ON. Here is the command to do this:

Code Snippet

ALTER DATABASE MyDBName SET ALLOW_SNAPSHOT_ISOLATION ON

Next, add this statement at the beginning of your procedure:

Code Snippet

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

I hope this solves your problem.

Best regards,

Sami Samir

|||

I looked at the execution plan of the insert statement into the device table and the foreign key lookup was doing a scan and a merge join. This was what was causing the issue. I have seen this before when the datatypes do not match, for example int to bigint, but that is not the case with your code. I believe it has something to do with the xml function that is causing the problems. I modified the code to parse the xml and put the results in a table variable and then do the insert and I did not receive any deadlocks. Let me know if you have the same results on your side. Below is the code snippet. Thanks.

-Chris

-- Populate the Device table

declare @.myTable TABLE (hwID bigint,BatchID int);

INSERT INTO @.myTable (HwId, BatchId)

SELECT x.value('./HwId[1]','BIGINT') AS HwId,

@.batchId

FROM @.batchDeviceXML.nodes('/BatchDevice/Devices/Device') as R(x)

INSERT INTO dbo.Device (HwId, BatchId)

SELECT hwID,BatchID

FROM @.myTable;

|||

Hi Chris,

Your logic works

Thanks a lot.

- Loonysan

Thursday, March 8, 2012

Deadlock

Hi there!
In my database client application (C#) I sometimes get an exception telling
me, that my application (which runs a some kind ob batch job) was selected
as deadlock victim and the active transaction was terminated.
No I want to get mor information about, which other application causes the
deadlock.
I thought about executing "sp_lock" just after I received the
deadlock-exception, but I think that this is too late, because my
transaction will be already terminated at this time, and ther won't be a
deadlock any longer.
Can anyone tell me, what would be the best way to receive information about
the active database-connections and their locks, BEFORE the deadlock
situation is cleared by the database-engine?
Is there some kind of trigger or sth. like that?
As the problem doesn't occur regularly, and it's only on the productive
machine, I absolutely don't want to have the profiler active!
Thanks in advance!
Max
Markus
Take a look at sp_who2 stored procedure on clolumn blkby (If I remember
well) . That tells you who is blocked and by whom.
Regarding to DEADLOCKS please visit at this site
http://www.sql-server-performance.com/deadlocks.asp
"Markus Emayr" <essmayr/at/racon-linz.at> wrote in message
news:%23GGruXUfFHA.1412@.TK2MSFTNGP09.phx.gbl...
> Hi there!
> In my database client application (C#) I sometimes get an exception
telling
> me, that my application (which runs a some kind ob batch job) was selected
> as deadlock victim and the active transaction was terminated.
> No I want to get mor information about, which other application causes the
> deadlock.
> I thought about executing "sp_lock" just after I received the
> deadlock-exception, but I think that this is too late, because my
> transaction will be already terminated at this time, and ther won't be a
> deadlock any longer.
> Can anyone tell me, what would be the best way to receive information
about
> the active database-connections and their locks, BEFORE the deadlock
> situation is cleared by the database-engine?
> Is there some kind of trigger or sth. like that?
> As the problem doesn't occur regularly, and it's only on the productive
> machine, I absolutely don't want to have the profiler active!
> Thanks in advance!
> Max
>
|||Use the trace flag 1024 to receive notifications about deadlocks.
DBCC TRACEON (1024, -1)
[]s
Luciano Caixeta Moreira
Meu blog: http://br.thespoke.net/MyBlog/Luti/MyBlog.aspx
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OMKxokUfFHA.1044@.tk2msftngp13.phx.gbl...
> Markus
> Take a look at sp_who2 stored procedure on clolumn blkby (If I remember
> well) . That tells you who is blocked and by whom.
> Regarding to DEADLOCKS please visit at this site
> http://www.sql-server-performance.com/deadlocks.asp
>
>
> "Markus Emayr" <essmayr/at/racon-linz.at> wrote in message
> news:%23GGruXUfFHA.1412@.TK2MSFTNGP09.phx.gbl...
> telling
> about
>

dead lock

Hi,
Yestoday I checked the SQL SERVER management information, it showed one
process blocked the others, the last TSQL command batch of the blocking
process was SET NOEXEC OFF, what's the reason that this process blocked
other processes, how to solve it?
Thanks
Hi,
I feel that the process mentioned by you may not be the probable case for
deadlock. COuld you activate the trace flag 1204 in server
to get more information on deadloak.
DBCC TRACEON(1204,-1)
This trace flag will put the information regarding the deadlock into sql
server error log for investigating the cause for deadlock.
Thanks
Hari
SQL Server MVP
"Taoge" <luxiaotao@.hotmail.com> wrote in message
news:u76Cq5MYFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Yestoday I checked the SQL SERVER management information, it showed one
> process blocked the others, the last TSQL command batch of the blocking
> process was SET NOEXEC OFF, what's the reason that this process blocked
> other processes, how to solve it?
> Thanks
>
|||Actually, 1204 only forces a full scan debug of the deadlock chain. 3604
sends output to the client and 3605 sends output to the server error log.
However, at some pointI believe SP3the 3605 flag was no longer required,
but it has gone back and forth a few times. So, I usually explicitly set
3605 as well.
Sincerely,
Anthony Thomas

"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O7iETYPYFHA.3584@.TK2MSFTNGP12.phx.gbl...
Hi,
I feel that the process mentioned by you may not be the probable case for
deadlock. COuld you activate the trace flag 1204 in server
to get more information on deadloak.
DBCC TRACEON(1204,-1)
This trace flag will put the information regarding the deadlock into sql
server error log for investigating the cause for deadlock.
Thanks
Hari
SQL Server MVP
"Taoge" <luxiaotao@.hotmail.com> wrote in message
news:u76Cq5MYFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Yestoday I checked the SQL SERVER management information, it showed one
> process blocked the others, the last TSQL command batch of the blocking
> process was SET NOEXEC OFF, what's the reason that this process blocked
> other processes, how to solve it?
> Thanks
>

dead lock

Hi,
Yestoday I checked the SQL SERVER management information, it showed one
process blocked the others, the last TSQL command batch of the blocking
process was SET NOEXEC OFF, what's the reason that this process blocked
other processes, how to solve it?
ThanksHi,
I feel that the process mentioned by you may not be the probable case for
deadlock. COuld you activate the trace flag 1204 in server
to get more information on deadloak.
DBCC TRACEON(1204,-1)
This trace flag will put the information regarding the deadlock into sql
server error log for investigating the cause for deadlock.
Thanks
Hari
SQL Server MVP
"Taoge" <luxiaotao@.hotmail.com> wrote in message
news:u76Cq5MYFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Yestoday I checked the SQL SERVER management information, it showed one
> process blocked the others, the last TSQL command batch of the blocking
> process was SET NOEXEC OFF, what's the reason that this process blocked
> other processes, how to solve it?
> Thanks
>|||Actually, 1204 only forces a full scan debug of the deadlock chain. 3604
sends output to the client and 3605 sends output to the server error log.
However, at some pointI believe SP3the 3605 flag was no longer required,
but it has gone back and forth a few times. So, I usually explicitly set
3605 as well.
Sincerely,
Anthony Thomas
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O7iETYPYFHA.3584@.TK2MSFTNGP12.phx.gbl...
Hi,
I feel that the process mentioned by you may not be the probable case for
deadlock. COuld you activate the trace flag 1204 in server
to get more information on deadloak.
DBCC TRACEON(1204,-1)
This trace flag will put the information regarding the deadlock into sql
server error log for investigating the cause for deadlock.
Thanks
Hari
SQL Server MVP
"Taoge" <luxiaotao@.hotmail.com> wrote in message
news:u76Cq5MYFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Yestoday I checked the SQL SERVER management information, it showed one
> process blocked the others, the last TSQL command batch of the blocking
> process was SET NOEXEC OFF, what's the reason that this process blocked
> other processes, how to solve it?
> Thanks
>

dead lock

Hi,
Yestoday I checked the SQL SERVER management information, it showed one
process blocked the others, the last TSQL command batch of the blocking
process was SET NOEXEC OFF, what's the reason that this process blocked
other processes, how to solve it?
ThanksHi,
I feel that the process mentioned by you may not be the probable case for
deadlock. COuld you activate the trace flag 1204 in server
to get more information on deadloak.
DBCC TRACEON(1204,-1)
This trace flag will put the information regarding the deadlock into sql
server error log for investigating the cause for deadlock.
Thanks
Hari
SQL Server MVP
"Taoge" <luxiaotao@.hotmail.com> wrote in message
news:u76Cq5MYFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Yestoday I checked the SQL SERVER management information, it showed one
> process blocked the others, the last TSQL command batch of the blocking
> process was SET NOEXEC OFF, what's the reason that this process blocked
> other processes, how to solve it?
> Thanks
>|||Actually, 1204 only forces a full scan debug of the deadlock chain. 3604
sends output to the client and 3605 sends output to the server error log.
However, at some point?I believe SP3?the 3605 flag was no longer required,
but it has gone back and forth a few times. So, I usually explicitly set
3605 as well.
Sincerely,
Anthony Thomas
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:O7iETYPYFHA.3584@.TK2MSFTNGP12.phx.gbl...
Hi,
I feel that the process mentioned by you may not be the probable case for
deadlock. COuld you activate the trace flag 1204 in server
to get more information on deadloak.
DBCC TRACEON(1204,-1)
This trace flag will put the information regarding the deadlock into sql
server error log for investigating the cause for deadlock.
Thanks
Hari
SQL Server MVP
"Taoge" <luxiaotao@.hotmail.com> wrote in message
news:u76Cq5MYFHA.1240@.TK2MSFTNGP14.phx.gbl...
> Hi,
> Yestoday I checked the SQL SERVER management information, it showed one
> process blocked the others, the last TSQL command batch of the blocking
> process was SET NOEXEC OFF, what's the reason that this process blocked
> other processes, how to solve it?
> Thanks
>

Wednesday, March 21, 2012

Deadlock Issue when dropping/creating tables

Sunday, March 11, 2012

Deadlock & probably other problem

DeadLock - INSERT with foreign keys

Thursday, March 8, 2012

Deadlock

dead lock

dead lock

dead lock

deAttach database

Blog Archive

About Me