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 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.

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...

deadlock errors 1204 and 1205 differences

I believe there are 2 error numbers related to deadlocks 1204 and 1205. What
are the differences and when may i see one over another ? Using SQL 2000Hi
Error 1204 is issued when there is a process can not take out a lock rather
than the process being deadlocked. This is a resource problem and the number
of locks can be increased using sp_configure.
See Books Online:
mk:@.MSITStore:C:\Program%20Files\Microsoft%20SQL%20Server\80\Tools\Books\trb
lsql.chm::/tr_reslsyserr_1_6gxg.htm
Error 1205 is issued when a deadlock is detected. Check out "Inside SQL
Server 2000" by Kalen Delany ISBN 0-7356-0998-5 on how to detect and solve
deadlock problems.
John
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:utZq8LR%23DHA.1632@.TK2MSFTNGP12.phx.gbl...
> I believe there are 2 error numbers related to deadlocks 1204 and 1205.
What
> are the differences and when may i see one over another ? Using SQL 2000
>

deadlock errors 1204 and 1205 differences

I believe there are 2 error numbers related to deadlocks 1204 and 1205. What
are the differences and when may i see one over another ? Using SQL 2000Hi
Error 1204 is issued when there is a process can not take out a lock rather
than the process being deadlocked. This is a resource problem and the number
of locks can be increased using sp_configure.
See Books Online:
mk:@.MSITStore:C:\Program%20Files\Microso
ft%20SQL%20Server\80\Tools\Books\trb
lsql.chm::/tr_reslsyserr_1_6gxg.htm
Error 1205 is issued when a deadlock is detected. Check out "Inside SQL
Server 2000" by Kalen Delany ISBN 0-7356-0998-5 on how to detect and solve
deadlock problems.
John
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:utZq8LR%23DHA.1632@.TK2MSFTNGP12.phx.gbl...
> I believe there are 2 error numbers related to deadlocks 1204 and 1205.
What
> are the differences and when may i see one over another ? Using SQL 2000
>

deadlock errors

I'm setting up a push merge replication, after the initial merge agent completes without errors I move the subscriber from a wired connection to a wireless connection. At this point I get an error…

The schema script …Program Files\Microsoft SQL Server\MSSQL\REPLDATA\unc\[table]_93.sch' could not be propagated to the subscriber.

(Source: Merge Replication Provider (Agent); Error number: -2147201001)

Cannot drop the table '[table]' because it is being used for replication.

(Source: dfdmfvltgh09 (Data source); Error number: 3724)

After this error the initial merge agent kicks off again trying to apply the initial snapshot where I then get the following deadlock errors.

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

(Source: dfdmfvltgh09 (Data source); Error number: 1205)

The process could not deliver the snapshot to the Subscriber.

(Source: Merge Replication Provider (Agent); Error number: -2147201001)

All are getting set up on sql 2000 sp4 on a push.We are trying to set up 10 units that all are pointing to the same dlink router model dwl-2100AP.5 are working the other 5 are coming up with this error.Anybody have any ideas what could be causing this.I don’t mind the getting cut off due to poor network connectivity, I really want to stop the initial merge agent from kicking off after it’s already completed.

Thank you in advanced,

Pauly C

Let's tackle the snapshot failure first. Are you saying merge is trying to apply the snapshot twice, once on the wired network and once on the wireless network? Are these two networks on the same domain?

|||

Yes, the snapshot completes without errors, and replication works, changes get propergated back and forth between the subscriber and publisher, then when they move the subscriber to a wireless connection the first error occurs and the initial merge agent starts again.After applying some of the snapshot it gets the deadlock error. and it keeps trying to apply the initial merge agaent over and over.

|||And yes they are on the same domain. Somebody told me they were able to ping a machine that wasn't connected and actually got results. I did not see this with my own eyes so I'm not sure how true it is.

deadlock error

I am getting quite a few deadlock errors where both sessions are
trying to execute sp_execsql according to the the trace information in
the error log (see below). The database is being asscessed by an
application written in .NET, as well as a few people using Query
Analyzer. This seems to be happening relative randomly - can't pin it
to any specific circumstances. Any thoughts would be appreciated.

RID: 8:1:617:37 CleanCnt:1 Mode: X Flags: 0x2
Grant List 1::
Owner:0x3738dbe0 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:55
ECID:0
SPID: 55 ECID: 0 Statement Type: CONDITIONAL Line #: 47
Input Buf: RPC Event: sp_executesql;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:52 ECID:0 Ec:(0x4AC4D570)
Value:0x23297b80 Cost:(0/12C)

Node:2
RID: 8:1:267:91 CleanCnt:1 Mode: X Flags: 0x2
Grant List 0::
Owner:0x3efae340 Mode: X Flg:0x0 Ref:0 Life:02000000 SPID:52
ECID:0
SPID: 52 ECID: 0 Statement Type: CONDITIONAL Line #: 115
Input Buf: RPC Event: sp_executesql;1
Requested By:
ResType:LockOwner Stype:'OR' Mode: S SPID:55 ECID:0 Ec:(0x483FB570)
Value:0x37c0e060 Cost:(0/138)
Victim Resource Owner:
ResType:LockOwner Stype:'OR' Mode: S SPID:52 ECID:0 Ec:(0x4AC4D570)
Value:0x23297b80 Cost:(0/12C)[posted and mailed, please reply in news]

Scot Schneider (sschneider@.ebags.com) writes:
> I am getting quite a few deadlock errors where both sessions are
> trying to execute sp_execsql according to the the trace information in
> the error log (see below). The database is being asscessed by an
> application written in .NET, as well as a few people using Query
> Analyzer. This seems to be happening relative randomly - can't pin it
> to any specific circumstances. Any thoughts would be appreciated.

Both SqlClient and OleDb Client calls sp_executesql to run parameterized
statements, so if your application is doing this a lot, this could be
about anything.

The deadlock itself seems to be due to both processes holding a shared
locks, and both process wants an exclusive lock on the resource on whic
the other process have a shared lock.

There are two things in this deadlock trace that I find a little funny:

> SPID: 52 ECID: 0 Statement Type: CONDITIONAL Line #: 115

If you app is submitting dynamic SQL statements, line 115 is a pretty
high line number. Could it be that your app is actually using stored
procedures, but is using CommandType Text rather than StoredProcedure?
Changing this could give you somewhat better performance and somewhat
more informative deadlock traces.

> RID: 8:1:617:37 CleanCnt:1 Mode: X Flags: 0x2

Both locks are on tables without clustered indexes. This may be fully
conscious decsion, but the recommendation is to always have a
clustered index on your tables. There is no guarantee that the dealocks
goes away if you add a clustered index, but it could happen. At least
with a clustered index, it is easier to find the tables involved in
the deadlocl.

To dig out which tables that are involved in this dead lock you would
do:

SELECT db_name(8) -- gives you the database name.

DBCC TRACEON (3604, 1)
DBCC PAGE(8,1,617)
This gives you the header information for this page.

In the middle of this output, in the leftmost column is m_objId. This
is the object of the table. Copy and paste the value of m_objID, and run
SELECT object_name() for that value.

The value 37 is the row number, I would guess within that page.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

DBNull Error

Am getting errors on this syntax:

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

Row.myColumn = DBNull.Value

Value of type 'System.DBNull' cannot be converted to type 'String'

Any ideas? Just want to set the myColumn to NULL.

Thanks

Nevermind.

Row.myColumn = Nothing