MS SQL Server :: Deadlocks In Profiler

Too Many Deadlocks
Hi,

I've got a deadlock problem. The log below has been generated. The problem is that during one day, I have more than 300 deadlocks like it. Before, the were not so many deadlocks.
During past year, the number of users has grow (from 100 before to 500 or 700 now)

*** Deadlock Detected ***
- Requested by: SPID 360 ECID 0 Mode "S"
- Held by: SPID 113 ECID 0 Mode "S"
Index: aaaaa_PK
Table: TABLE_1
Database: MYDB
== Lock: KEY: 22:325576198:1 (ff009ae5078d)
- Requested by: SPID 113 ECID 0 Mode "S"
- Held by: SPID 374 ECID 0 Mode "X"
Index: aaaaa_PK
Table: TABLE_1
Database: MYDB
== Lock: KEY: 22:325576198:1 (ff009ae5078d)
- Requested by: SPID 374 ECID 0 Mode "IX"
- Held by: SPID 360 ECID 0 Mode "S"
Table: TABLE_2
Database: MYDB
== Lock: PAG: 22:1:2428
== Deadlock Lock participant information:
Input Buf: S E L E C T the_rest_of_the_query
SPID: 360 ECID: 0 Statement Type: UNKNOWN TOKEN Line #: 1
Input Buf: s p _ e x e c u t e 8
Input Buf: s p _ c u r s o r 8À B 8 8f ç @ Table I
Input Buf: S E L E C T the_rest_of_the_query
SPID: 360 ECID: 0 Statement Type: SELECT Line #: 1
== Session participant information:
== Deadlock Detected at:
==> Process 360 chosen as deadlock victim

I have done :
- rebuild indexes on all tables (fillfactor 90)
- analysed memory activity

Could a lack of memory be at the origin of the problem ? Which counters in perfmon are significant for memory lack ?

Could the index fill factor could be at the origin of the problem ? At time, it is at 90 percent.

Config : Winnt4 Server, MS-SQL 7 SP4 , 2 GB of RAM , 2 x Xeon 700

Thanks for any help.

View Replies !

Deadlocks (I Think)
Hi folks,

I have an application built on top of a questionable DB design which requires overcomplicated selects. The application is experiencing deadlocks regularly, in some cases with only one concurrent user.

I set the trace flag 1204 but am not seeing anything in the Error.log and I initiated a trace in profiler which does not seem to show any deadlock.
Despite having recreated the problem which show my browser hanging indefinitely. When I run the following queries:

SELECT spid, waittime, lastwaittype, waitresource
FROM master..sysprocesses
WHERE waittime > 10000
AND spid > 50

SELECT spid, cmd, status, loginame, open_tran, datediff(s, last_batch, getdate ()) AS [WaitTime(s)]
FROM master..sysprocesses p
WHERE open_tran > 0
AND spid > 50
AND datediff (s, last_batch, getdate ()) > 30
ANd EXISTS (SELECT * FROM master..syslockinfo l
WHERE req_spid = p.spid AND rsc_type <> 2)

I get:

55860978LCK_M_XPAG: 13:1:2573

54AWAITING COMMANDsleeping sa 11499
55UPDATE sleeping sa 21499

respectively. Any help would be welcome.

Thanks in advance,
Don

View Replies !

Deadlocks
This is probably a stupid question, but...
Is there any way to totally avoid deadlocks. In some critical applications
we have removed transactions entirely, counting on other means to maintain
database consistency. We still get deadlocks in this area. These are mainly
inserts, and the only thing I can think is that updates to the indexes are
causing multiple page locks which result in deadlocks. Is this true?

Will deadlocks be eliminated in 7.0 with row level locking for this situation?
Or will index page splits still cause a possibility of deadlock contention?

ben

View Replies !

Deadlocks
Hi guys,

View Replies !

Deadlocks
Hi guys,
Does SQL Server 6.5 log deadlock errors automatically into the errorlog, or do I need to use traceflags ??

TIA
Ju.DBA

View Replies !

DeadLocks
I am getting the following dead lock error message writtent to the Error Log.

How do i interpret this...?

2002-07-10 11:49:52.88 spid3 Node:1
2002-07-10 11:49:52.88 spid3 KEY: 6:1531868524:1 (1e0040209980) CleanCnt:1 Mode: X Flags: 0x0
2002-07-10 11:49:52.88 spid3 Grant List::
2002-07-10 11:49:52.88 spid3 Owner:0x26429de0 Mode: X Flg:0x0 Ref:2 Life:02000000 SPID:62 ECID:0
2002-07-10 11:49:52.88 spid3 SPID: 62 ECID: 0 Statement Type: INSERT Line #: 67
2002-07-10 11:49:52.88 spid3 Input Buf: RPC Event: sp_Save;1
2002-07-10 11:49:52.88 spid3 Requested By:
2002-07-10 11:49:52.88 spid3 ResType:LockOwner Stype:'OR' Mode: Range-S-S SPID:58 ECID:0 Ec:(0x29f534f8) Value:0x2649f0c0 Cost:(0/0)
2002-07-10 11:49:52.88 spid3
2002-07-10 11:49:52.88 spid3 Node:2
2002-07-10 11:49:52.88 spid3 KEY: 6:1695345104:1 (ffffffffffff) CleanCnt:1 Mode: Range-S-U Flags: 0x0
2002-07-10 11:49:52.88 spid3 Grant List::
2002-07-10 11:49:52.88 spid3 Owner:0x26450f20 Mode: Range-S-U Flg:0x0 Ref:1 Life:02000000 SPID:58 ECID:0
2002-07-10 11:49:52.88 spid3 SPID: 58 ECID: 0 Statement Type: INSERT Line #: 250
2002-07-10 11:49:52.88 spid3 Input Buf: RPC Event: sp_IPAQManagerFetchFilterDetail;1
2002-07-10 11:49:52.88 spid3 Requested By:
2002-07-10 11:49:52.88 spid3 ResType:LockOwner Stype:'OR' Mode: Range-Insert-Null SPID:62 ECID:0 Ec:(0x3bb5f4f8) Value:0x2649e040 Cost:(0/2340)
2002-07-10 11:49:52.88 spid3 Victim Resource Owner:
2002-07-10 11:49:52.88 spid3 ResType:LockOwner Stype:'OR' Mode: Range-S-S SPID:58 ECID:0 Ec:(0x29f534f8) Value:0x2649f0c0 Cost:(0/0)

View Replies !

Deadlocks
We have an application that runs on both Oracle 8.1.7 and SQL Server 2000. Due to some poor design, we end up with a table being inserted/updated/selected by multiple processes, almost simutaniously and randomly. Obviously, this is a recipe for deadlocks. What is a bit strange is that we rarely see deadlocks on Oracle, but lots of deadlocks on SQL 2000. One explanation I heard is that Oracle tends to use row-level locks, while SQL server tends to use page-level locks. In adition, SQL server is inefficient in dealing with index updates (we do update some indexed columns quite often).

I am not very satisfied with the explanation. Can someone please shed some lights on this?

Thanks a lot.

View Replies !

Deadlocks
Is there any way to totally avoid deadlocks. In some critical applications
we have removed transactions entirely, counting on other means to maintain
database consistency. We still get deadlocks in this area. These are mainly
inserts, and the only thing I can think is that updates to the indexes are
causing multiple page locks which result in deadlocks. Is this true?

Will deadlocks be eliminated in 7.0 with row level locking for this situation?
Or will index page splits still cause a possibility of deadlock contention?

Thanks!
ben

View Replies !

Deadlocks
Ah yes, those dreaded deadlocks!!

Well I've removed the clustered indexes, I've created non-clustered indexes, I've tried to streamline the code, but I'm still getting deadlocks.

The worst thing about them is that I don't appear able to trap them at the client end. I'm using Visual Foxpro 6, and I get an ODBC error window with "Driver is probably out of resources..." and that's that.

Are there any known methods of trapping these ODBC errors?

TIA,
Simon
UK

View Replies !

DeadLocks
I have a merge replication with 3 subscribers and 1 publisher. The merge
replication is continuously. The merge process is regularly stopped due to
a deadlock and the merge replication is chosen as victim. I assume that
the conflicting transaction is the backup-process. I want to automatically
detect this and restart the synchronisation. How can I do this ?

Thanks, Jay

View Replies !

DeadLocks
Hi ,

I have a problem with a SP in 6.5. When i try to run a Stored Proc which is a simple select statement dumped into a temp table in a particular database, I lock other users who are tring to log into other databases some in tempdb database. When i try to kill the process the rollback takes almost 45 mins or so..till then no one can log on to the server.

The SP works fine when no one is logged into the Great Plains server. One more thing i observed is that, the SP when run results on a deadlock only when the owner is a user. If the owner is DBO it works fine.

Can anybody throw some light on this.

Thanks in Advance
Siv

View Replies !

Deadlocks
Hi,

we have a production inviremont that is running for about 10 months. Since a couple of weeks we are having problems with "Deadlocks".

This cant be due to an increase in data size on the tables that are having the issues because these are cleaned in the same transaction that populates them.

These tables are used to store temporary data that the production system needs to calculate the correct price for any given order. This transaction takes between 0.5 to 1 second to commit.

We are running on a dual processor machine with 1 Gb of RAM with SQL Server 7 - sp 3, Windows NT 4 sp 6, Microsoft Transaction Server.

In all our queries and stored procedures we use the optimizer hints (nolock) for select statements and (rowlock) for updates or deletes.

Any help and/or suggestions would be appriciated.

View Replies !

Deadlocks
hi,

We have a SQL 2005 transaction database server that suddenly started to issue deadlock errors last week on most of the databases on that server and a lot of timeout errors. Before that, that database server performed very well and timeouts were minimal to zero. I am not sure what changed for it to have these performance problems.

The only major change we did was to convert several varchar columns to nvarchar in several tables (as part of internationalization initiatives). We did not modify the procs from varchar to nvarchar though but would be doing that phase by phase.

There is also one proc in which we used the snapshot isolation level of sql server 2005. These are only 2 major changes done within the past 2 weeks. Would these be the cause for these deadlocks and timeouts on our web-based application?

Any ideas?

Thx
Sri

View Replies !

Deadlocks On A Web Page
Morning All,
Am getting the following error from a number of users and am sort of wondering where to start in terms of diagnosing the problem. If anyone could give me any pointers on where to start in diagnosing the issue I would be grateful.
"System.Data.SqlClient.SqlException: Transaction (Process ID 282) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction."

View Replies !

Monitoring Deadlocks
SQL 2000 SP3Howdy all.I saw some code out there somewhere that logged deadlock info includingSQL code that caused it as well as the user information.Any ideas ?Thanks,Craig

View Replies !

Deadlocks And Use Of Nolock
I am getting lot of deadlocks in my application. As it is very complexti avoid deadlocks at this stage of application we have done few stepsto lessen the impact.We have added retries after deadlock is capturted.We have added select * from TABLE with (nolock) wherever possible.But interestingly second step is not working. I have few simple selectstatements where i am using nolock criteria still I am gettingdeadlock victim error. Any idead why it happening. I thought as soonas I put nolock in the query it will ignore all the locks.My sp isCREATE procedure sp_Check_denomination@supply_till_idint,@product_codechar(4),@iso_currency_codechar(3),@denominationmoneyasdeclare @product_id numeric(5)select @product_id = product_id from product with (nolock) whereproduct_code = @product_codeif exists (select *from transaction_inventory TI with (nolock),product_ccy_denom PCD with (nolock)where TI.supply_till_id = @supply_till_idand TI.product_id = @product_idand TI.iso_currency_code= @iso_currency_codeand TI.denomination = @denominationand TI.product_id = PCD.product_idand TI.iso_currency_code = PCD.iso_currency_codeand TI.denomination = PCD.denominationand PCD.product_id=@product_idand PCD.denomination = @denominationand PCD.iso_currency_code=@iso_currency_codeand PCD.tradeable = 1)beginreturn(1)endelsebeginreturn(0)endGO

View Replies !

Deadlocks Workaround?
Hi All,I have read about deadlocks here on Google and I was surprised to readthat an update and a select on the same table could get into adeadlock because of the table's index. The update and the selectaccess the index in opposite orders, thereby causing the deadlock.This sounds to me as a bug in SQL Server!My question is: Could you avoid this by reading the table with a'select * from X(updlock)' before updating it? I mean: Would thisresult in the update transaction setting a lock on the index rowsbefore accessing the data rows?Merry Christmas!/Fredrik Möller

View Replies !

Deadlocks In Sql Server
HiI'm having a problem with deadlocks in a table in SQL server whentrying to update it through Biztalk 2004. There is no problem when Iuse the same Biztalk solution to update a similar dummy table, butwhen I try updating the original table in the production database,some transactions are updated successfully whereas others become thevictim of the deadlock (Transaction (Process ID 185) was deadlocked onlock resources with another process and has been chosen as thedeadlock victim. Rerun the transaction). The table that is updated isalso being used by another application that just selects rows from it.As a workaround, I have used recursion in the code that updates thetable. The function is put through a recursive loop whenever thedeadlock exception(#1205) is caught. It keeps on trying to update thetable until the updation is successful or another exception (not thedeadlock one) is caught. i.e.Bool Update_IVR(string amount, string customer_id){Try{//updation code}}Catch (exception ex){If( ex.message == deadlock message){Bool succ =Update_IVR (amount, customer_id) //recursionReturn succ;}Else //error handling code}After introducing this code, the problem did not occur for the next13000 transactions. Then I got the error again four times along with atimeout error (Timeout expired. The timeout period elapsed prior tocompletion of the operation or the server is not responding). Howeverfor the next 17000 transactions (to date) this error has not showedup.ThanksHasan

View Replies !

Lost Deadlocks
We've found deadlocks in the trace file that were not captured by ourPowerbuilder application. Some deadlocks are trapped or, at least,reported to the user as a db error, and others are completely silent.We've also seen evidence of strange data that would be explained byunprocessed deadlocks, although we've not yet proven that theunreported deadlocks are killing updates to the db.Putting a raiserror into various parts of the same code (and codereview) appears to prove that we are error checking after each dbupdate. That is, it looks like we're checking, and a raiserror alwaysbubbles up to the app.Can anyone shed some light on a.) How this could happen and b.) WhatShould We Do?Some of one of the traces below (with minor anotations.ThanksDeadlock encountered .... Printing deadlock information2004-11-11 10:33:57.77 spid42004-11-11 10:33:57.77 spid4 Wait-for graph2004-11-11 10:33:57.77 spid42004-11-11 10:33:57.77 spid4 Node:12004-11-11 10:33:57.77 spid4 TAB: 6:1739869265 (cbo1023p) []CleanCnt:2 Mode: X Flags: 0x02004-11-11 10:33:57.77 spid4 Wait List:2004-11-11 10:33:57.77 spid4 Owner:0x60e085e0 Mode: ISFlg:0x0 Ref:1 Life:00000000 SPID:88 ECID:02004-11-11 10:33:57.77 spid4 SPID: 88 ECID: 0 Statement Type:SELECT Line #: 1232004-11-11 10:33:57.77 spid4 Input Buf: Language Event: selectcbord.cbo1000p_item.longname as itemname,cbord.cbo4002p_itemevent.eventdate,cbord.cbo4002p_itemevent.eventstatus,cbord.cbo4002p_itemevent.unitid,cbord.cbo4004p_eventlist.itembin_intid,cbord.cbo4004p_eventlist.itemu2004-11-11 10:33:57.77 spid4 Requested By:2004-11-11 10:33:57.77 spid4 ResType:LockOwner Stype:'OR' Mode:IS SPID:84 ECID:0 Ec:(0x4F9B3A00) Value:0x4a0e9400 Cost:(0/0)2004-11-11 10:33:57.77 spid42004-11-11 10:33:57.77 spid4 Node:22004-11-11 10:33:57.77 spid4 TAB: 6:1739869265 (cbo1023p) []CleanCnt:2 Mode: X Flags: 0x02004-11-11 10:33:57.77 spid4 Grant List 2::2004-11-11 10:33:57.77 spid4 Owner:0x4de9a8a0 Mode: XFlg:0x0 Ref:742 Life:02000000 SPID:121 ECID:02004-11-11 10:33:57.77 spid4 SPID: 121 ECID: 0 Statement Type:UPDATE Line #: 142004-11-11 10:33:57.77 spid4 Input Buf: RPC Event:cbord.p_pur002_replacecost;12004-11-11 10:33:57.77 spid4 Requested By:2004-11-11 10:33:57.77 spid4 ResType:LockOwner Stype:'OR' Mode:IS SPID:88 ECID:0 Ec:(0x4F259A70) Value:0x60e085e0 Cost:(0/0)2004-11-11 10:33:57.77 spid42004-11-11 10:33:57.77 spid4 Node:32004-11-11 10:33:57.77 spid4 KEY: 6:2134298663 (cbo1000p_item):1(96006e2bf95f) CleanCnt:1 Mode: U Flags: 0x02004-11-11 10:33:57.77 spid4 Grant List 2::2004-11-11 10:33:57.77 spid4 Grant List 3::2004-11-11 10:33:57.77 spid4 Owner:0x4dc088a0 Mode: SFlg:0x0 Ref:1 Life:00000000 SPID:84 ECID:02004-11-11 10:33:57.77 spid4 SPID: 84 ECID: 0 Statement Type:CONDITIONAL Line #: 632004-11-11 10:33:57.77 spid4 Input Buf: Language Event: selectcbord.cbo1000p_item.longname as itemname,cbord.cbo4002p_itemevent.eventdate,cbord.cbo4002p_itemevent.eventstatus,cbord.cbo4002p_itemevent.unitid,cbord.cbo4004p_eventlist.itembin_intid,cbord.cbo4004p_eventlist.itemu2004-11-11 10:33:57.77 spid4 Requested By:2004-11-11 10:33:57.77 spid4 ResType:LockOwner Stype:'OR' Mode:X SPID:121 ECID:0 Ec:(0x5F719A70) Value:0x48286aa0 Cost:(0/B9654)2004-11-11 10:33:57.77 spid4 Victim Resource Owner:2004-11-11 10:33:57.77 spid4 ResType:LockOwner Stype:'OR' Mode:IS SPID:88 ECID:0 Ec:(0x4F259A70) Value:0x60e085e0 Cost:(0/0)2004-11-11 10:34:02.77 spid4

View Replies !

How To Deal With Deadlocks ...
In a procedure that is called fairly frequently I access another server which from time to time decides that my procedure is "deadlock victim", and kills my process. What I would like to do is to check if this occurs, wait a second or two to allow SQL server to deal with the dealock, and try again until I get my statements run. Now this is impossible (to my knowledge) without using a separate process (i.e using a dll or something to make the call). Is there a way you could do this in standard T-SQL?

Btw, 1205 is the code returned by TSQL when a process is judged to be a deadlock victim.

To examplify my problem, consider this pseudo-code which illustrates the above way of thinking:

create procedure ProcName

as
declare
@ReturnCode int,
@Parameter1 varchar(10),
@Parameter2 varchar(10)

select @Parameter1 = SomeThing, @Parameter2 = SomeOtherThing

set @ReturnCode = 1205
while @ReturnCode = 1205
begin

exec @ReturnCode = master..<DLL-NameGoesHere> @Parameter1, @Parameter2
Waitfor delay '00:00:01'
end

View Replies !

Troubleshooting Deadlocks
Lock:Deadlock Chain
Exchange
1
16325
2006-04-21 09:20:18.560
Parallel query worker thread was involved in a deadlock
0

Is the process I am running deadlocking with Exchange Server?

The data above is from Profiler.

Thanks

Lystra

View Replies !

How To Avoid Deadlocks
I am conducting stress testing for my website and keep getting deadlocks with the following message when one process is adding about 100 records per second and another process is trying to access the data:

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

What do I need to do in my stored procedures to avoid this? I only have ONE stored prcoedure that locks a row while incrementing an ID value. I am not doing any other locks, so is this a SQL Server system lock?

Any advise would be much appreciated. Thanks!

View Replies !

! SQL Server 6.5 Deadlocks !
I need some help in reducing deadlocks in 6.5 I have tested with `Insert Row Locking` turned off and it reduced the number of deadlocks. What i need to know is if removing the foreign key relationships on tables reduces/eliminates Deadlocks. If any of you have any info on this please let me know.

Thanks
Kalyan

View Replies !

Deadlocks-Urgent
My database has been in production for 10 months with no deadlock problems. Three weeks ago I had to restore the database and I am now experiencing several deadlocks. SQL server is suppose to handle this and kill a process to resolve the deadlock but it is not. The deadlock occures on a stored procedure which is a simple select on a table with 187 records. DBCC ran with no problems. Any ideas what might be causing this? Any why is SQL not handling? Your help is greatly appreciated!

View Replies !

Simulating Deadlocks
Hi everybody! I'm running a batch process in Visual Fox Pro which does some INSERTs and UPDATEs in an MSSQL 7.0 db via ADO. I sometimes get a 1205 error ("you were chosen as Deadlock victim"), so I plan to catch this error and retry the operation until success is achieved. Can this behaviour be programmed in a T-SQL stored procedure? If not, how can I simulate a Deadlock event easily (ie: from Query Analizer)? Is it possible to do a raiseerror or similar?
Thanks in advance!!

Alan Cyment
Buenos Aires
Argentina

View Replies !

Deadlocks In SQL7
Hi,
I just wanted to know is there anyway of capturing the Deadlock events in the trace whereby it gives me details like as to which stmt got invovled in the deadlock.

I tried using the Deadlock Events in the SQLprofiler, but the utmost information that I get is objectID, databaseID and the application name.
But my application might access this object in several places. So, it would be great if it can show the stmt/procedure invovled in the deadlock.

I cant add the SP:StmtCompleted/Starting in the events along with the deadlock events, because it might start showing all the stmts which are currently running.

In short, if there is a deadlock, I would like to know, the stmt, procedure name, objectid, database name/id & the users invovled in the deadlock.
IS THERE ANYWAY OUT ???

View Replies !

Blocks And Deadlocks

View Replies !

Blocks And Deadlocks
I know blocks and Deadlocks are different but how related are they? Seems like when I get reports of deadlocks I always have blocks and the blocks grow as time passes.

View Replies !

Ado And Sql Server Deadlocks
i have an issue regarding the following error message:

[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID ##) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

this is caused on a couple of ASP pages, which are using:

Code:

recordset.open "sql statement", connection, adOpenKeyset, adLockOptimistic

etc etc

recordset.addnew

etc

recordset.update

This is happening fairly frequently, which can't be a good thing, so would it be worth changing the lock or cursor types to a different one, in particular, adLockPessismistic?

Any pointers or comments would be very welcome.

This is happening on a windows server os, sql server 2000 using classic asp (.asp) pages, connecting to sql server using ado within the asp pages.

View Replies !

Replication Deadlocks
Hi All,

View Replies !

Deadlocks During UPDATE
I'm working on a system now where the workflow is as follows:

1. A user uploads a batch of some fairly large number of items (1000, say) to a holding table.
2. We run an SP against the holding table to test those items; some get flagged as valid, some as invalid.
3. We run a subsequent SP that inserts the valid items into the main table and then deletes them from the holding table.

In our concurrency testing, we're encountering deadlocks, and I'm struggling to figure out why. In step 2, for instance, the SP code looks like this:

BEGIN TRANSACTION
UPDATE HoldingTable SET
   Valid = CASE WHEN <condition1> THEN 0 WHEN <condition2> THEN 0 ... ELSE 1 END,
   Reason = CASE WHEN <condition1> THEN 'reason1' WHEN <condition2> THEN 'reason2' ... ELSE 'valid' END

   WHERE BatchID = @BatchID
COMMIT

Each contending process has its own batch of items, marked with an identifying batch ID. And they deadlock.

Our first thought was that the contending processes are deadlocking over pages containing rows from both batches. This seemed unlikely, but we thought, what the heck, let's create a clustered index on BatchID, PK, which should keep batch 1's rows out of the same pages that batch 2's rows are in. That had no discernable effect: we still got deadlocks.

Our next thought was that the number of rows involved is large enough that both processes are escalating from page locks to table locks during the update. It seems to us that this would create a deadlock.

Before I go into this in much more detail: are we on the right track here? Is it reasonable to be encountering deadlocks when two processes perform an UPDATE against different rows in the same table?

View Replies !

How To Resolve Deadlocks?
Hi,

We are facing Deadlock issues, when we try to execute a procedure for

different parameters simultaneously,

Example:

proc_name '20070630','755','Y','html'

proc_name '20070630','681','Y','html'

------

When we are executing the above procedure simultaneously..we are getting

the Deadlock error..

We included 'With(Rowlock)'.But still we are getting deadlocks.

Here is the trace file for your reference.. We could not find the KEY

parameter in this Trace file and we are not able to find the object name

which is creating the Deadlock.

------

2007-07-16 13:59:49.70 logon Login failed for user 'JUnitCustomer'.

2007-07-16 14:00:11.01 logon Login failed for user 'JUnitCustomer'.

2007-07-16 14:00:11.06 logon Login failed for user 'JUnitCustomer'.

2007-07-16 14:00:11.17 logon Login failed for user 'JUnitCustomer'.

2007-07-16 14:00:11.21 logon Login failed for user 'JUnitCustomer'.

2007-07-16 14:00:39.90 spid4 ResType:LockOwner Stype:'OR' Mode: IX

SPID:78 ECID:0 Ec0x6B133538) Value:0x6a

2007-07-16 14:00:39.90 spid4 Victim Resource Owner:

2007-07-16 14:00:39.90 spid4 ResType:LockOwner Stype:'OR' Mode: IX

SPID:78 ECID:0 Ec0x6B133538) Value:0x6a

2007-07-16 14:00:39.90 spid4 Requested By:

2007-07-16 14:00:39.90 spid4 Grant List 2::

2007-07-16 14:00:39.90 spid4 Input Buf: Language Event:

proc_create_fsa_export '20070630','755','Y','html'

2007-07-16 14:00:39.90 spid4 SPID: 74 ECID: 0 Statement Type: UPDATE

Line #: 252

2007-07-16 14:00:39.90 spid4 Owner:0x219ed520 Mode: S Flg:0x0 Ref:0

Life:00000001 SPID:74 ECID:0

2007-07-16 14:00:39.90 spid4 Grant List 0::

2007-07-16 14:00:39.90 spid4 PAG: 10:1:3325 CleanCnt:2 Mode: SIU Flags:

0x2

2007-07-16 14:00:39.90 spid4 Node:2

2007-07-16 14:00:39.90 spid4

2007-07-16 14:00:39.90 spid4 ResType:LockOwner Stype:'OR' Mode: IX

SPID:74 ECID:0 Ec0x7BB75538) Value:0x54

2007-07-16 14:00:39.90 spid4 Requested By:

2007-07-16 14:00:39.90 spid4 Input Buf: Language Event:

proc_create_fsa_export '20070630','284','Y','html'

2007-07-16 14:00:39.90 spid4 SPID: 78 ECID: 0 Statement Type: UPDATE

Line #: 224

2007-07-16 14:00:39.90 spid4 Owner:0x1c109a00 Mode: S Flg:0x0 Ref:0

Life:00000001 SPID:78 ECID:0

2007-07-16 14:00:39.90 spid4 Grant List 2::

2007-07-16 14:00:39.90 spid4 Grant List 0::

2007-07-16 14:00:39.90 spid4 PAG: 10:1:45083 CleanCnt:2 Mode: SIU Flags:

0x2

2007-07-16 14:00:39.90 spid4 Node:1

2007-07-16 14:00:39.90 spid4

2007-07-16 14:00:39.90 spid4 Wait-for graph

2007-07-16 14:00:39.90 spid4

2007-07-16 14:00:39.90 spid4 ...

2007-07-16 14:00:41.29 logon Login failed for user 'JUnitCustomer'.

2007-07-16 14:00:41.32 logon Login failed for user 'JUnitCustomer'.

----------

Can any one of you please help to solve this issue.

View Replies !

Problems With SQL DeadLocks
I face alot of DeadLocks in my SQL Server. the server is SQL 2000 SP3 with Windows 2000 sp4. I have all kinds of locks and i really don't understand none of them. I tried to restart the server and it didn't help - the locks id and locks objects are still there - about 15 objects. what to do?

View Replies !

Deadlocks Possible With Transactions?
Hello guys,

I would like to know if deadlocks are possible with transactions?

Regards,
Fabian

my favorit hoster is ASPnix : www.aspnix.com !

View Replies !

Best Way To Prevent Deadlocks
I'm going thru my application log, and just seeing what errors are popping up. I have a relatively intense search feature, thats causing alot of deadlocks.

Exception type: SqlException
Exception message: Transaction (Process ID 105) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

In general, what's the best way to resolve this ?

Should I see if I can apply "WITH (NOLOCK)" to my data ?

Any suggestions are greatly appreciated !

thanks again!
mike123

View Replies !

Best Way To Reduce Deadlocks
I have a search function, which searches across many tables.

It's a pretty heavy SPROC, I'm wondering in general, what are the best way to reduce deadlocks ? Its used a fair bit, and altho I haven't noticed problems with it myself, there are definately a decent amount of deadlocks showing up in the logfiles.

I've always assumed this is something really difficult, and avoided it like the plague.

Any tips are much appreciated !

thanks,
mike123

View Replies !

Deadlocks -- Sql Server 2000
Any help with deadlocks ?
I keep getting deadlocks..and can't figure out why....
version of sql server: 8.00.2040 (SP4).
[Microsoft][ODBC SQL Server Driver][SQL Server]Transaction (Process ID 73) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

View Replies !

Deadlocks && BEGIN/END TRANSACTION
Greetings,I've been reading with interest the threads here on deadlocking, as I'mfinding my formerly happy app in a production environment suddenlydeadlocking left and right. It started around the time I decided towrap a series of UPDATE commands with BEGIN/END.The gist of it is I have a .NET app that can do some heavy reading (nowriting) from tblWOS. It can take a minute or so to read all the datainto the app, along with data from other tables.I also have a web app out on the floor where people can entertransactions which updates perhaps 5-20 records in tblWOS at a time.The issue comes when someone is loading data with the app, and someoneelse tries an update through the web app: deadlocks-ville on theapplication and/or the web app.Again, I believe it began around the time I wrapped those 5-20 recordupdates to tblWOS on the web app with BEGIN/END. The funny thing isthat the records involved are not the same ones, so I'm thinking somekind of table-level lock is going on.I've played with UPDLOCK in examples, but don't quite understand whatit's attempting to do. Since the web update is discrete and short, andit is NOT updating records that are getting loaded, I'd like theBEGIN/UPDATE/END web transaction to happen and not deadlock the loadingapplication.Any suggestions? I'd be most grateful.thanks, Leaf

View Replies !

Deadlocks Involving Parallelism
We're experiencing a large number of deadlocks since we began runningSQL Server 2000 Enterprise Edition SP3 on a Dell 6650 with hyperthreading intel processors. We don't have the same problem on Dell6650's w/o the hyper threading. If I turn off the parallel queryprocessing option the deadlocks stop. I've tried all of the suggestionsfrom the Microsoft Knowledge Base under the following link -http://support.microsoft.com/?kbid=837983The only suggestion that actually yielded results was turning offparallel query processing but I don't want to give up what should be aperformance advantage if it wasn't for the deadlocks. Query tuning andindex tuning hasn't helped. Any suggestions? I haven't applied SP4yet. I'm wondering if anyone has seen the same problem resolved withSP4.*** Sent via Developersdex http://www.developersdex.com ***

View Replies !

Transactions, Locks And Deadlocks
Hi ChapsCan someone point me in the right direction for a very in depthpractical treatment of this for sql2k.Seen a number of books that skirt around it, read some articles thattouch on it (including some of erlands) but I need more...Thanks for any tips!

View Replies !

Autonumbering Causing Deadlocks.
Gents,I have come into a system that uses a secondary table to generate (forwant of a better word) Identities.egcreate table myidents( name sysname not null, ident int not null)create procedure getnextident @table sysname, @ident int outputasbeginif not exists (select top 1 1 from myidents where name = @table)insert into myidents values (@table, 0)update myidentsset @ident = ident = ident + 1where name = @tableendnow, (ignoring for now the use of reserved words) the problem is thatthis is called frequently, from other procedures. Trouble is that thecalling procedures call it from within a transaction. We now have awickedly hot spot on this table, with frequent deadlocks.Is there any relatively quick fix for this? Some locking hints orwhatever.Or do we need to go and recode, moving this kind of thing outside thetransaction (which are all rather too long for my liking), and evencosidering using identity columns as a replacement?Thanks

View Replies !

Snapshot Replication Deadlocks
Is there anyway to prevent deadlocks during the snapshot replication?

I understand that you can minimize by maybe creating a couple different snapshots (mixing tables to minimize locking while snapshot is being created), but is there any other way?

Thank you

View Replies !

Revisiting Deadlocks And Blocking
ok, about a few months ago I brought up the issues we were having with deadlocks. My company is running a .net data entry application hitting against a sql 2000 box. Forget hardware since we have more then enough hardware to handle the load.

The issue we are seeing is we get a process that blocks a table, and the bulding up of block process that cause timeouts/deadlocks etc. A couple of things we see when this happens is that .net on the web server seems to open up more and more connections(it seems to double or triple the connections as the blocking begins), we start to see deadlocks and we see alot of timeouts in the app due to the blocking.

The blocking aways happen on an insert of an order or a customer. One thing I keep wondering about is how we handle the unique id for our tables. Our DBA had us put in the following:

Begin Trans
select max(id) + 1
insert....
commit trans

I've heard from a few here and in other forums this is the wrong way to go. I'm still get a gut feeling that this could start causing issues like what we are seeing. My problem is I'm not knowledgable enough to make a strong enough case with it.

What I need help is getting enough information to either point to how we manage ID's or at the very least rule them out. I've done searchs in google and some other search engines but there doesnt seem to be any good articles on how best to handle unique ids and show a good case for it. I do know I can use sql identity, or even guid with the newid() function but I need solid proof before my boss will move to one of these methods. Any links you guys can give or even your own write up will really help my case and hopefully help my headache.

Thanks Ahead of time

View Replies !

The General Nature Of Deadlocks

As a general rule most of the .net code I see with the transaction manager is something like

create transaction scope

do db stuff

commit or roleback in the catch block if a deadlock occurs

my question is does it make sense to retry the db operation if a deadock happens with some sort of spinwait before giving up?

View Replies !

SQL2000 Replication Deadlocks
We have an SQL2000 database server that uses merge, pull replication with about 70 clients around the country, each with their own publication. Each article in each publication is row filtered by a single condition. There are busy times of the day when all of these clients attempt to replicate within a 15-20 minute period. We have noticed the following error that is creating conflicts that are resulting in data not being transferred to the server and very long replication run times (error message is paraphrased):

The record was inserted at the client, but could not be inserted at the server. Transaction (Process ID XXX) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

Is there a way to lessen the effects of this error? How can I reduce the number of deadlocks that are occurring? We will need to be adding a great deal more clients to the system in the future; is there a way to grow the system without making the deadlock problem worse?

Any help will be appreciated!

Thanks,

Craig

View Replies !

Deadlocks Parelellism Log Output
Could someone interpret this for me or point me somewhere where I could interpret it.

I'm getting deadlocks every so often and devided to trace it. I also have the profiler logging but cant figure out or find any info on this log or how to find out what exactly is causing the deadlock.

2006-04-13 09:47:43.39 spid4 Node:1
2006-04-13 09:47:43.39 spid4
2006-04-13 09:47:43.39 spid4
2006-04-13 09:47:43.39 spid4 -- next branch --
2006-04-13 09:47:43.39 spid4
2006-04-13 09:47:43.39 spid4 Waiting for parallel threads to enlist.
2006-04-13 09:47:43.39 spid4 Node:5
2006-04-13 09:47:43.39 spid4
2006-04-13 09:47:43.39 spid4 Producer: Xid Slot: 3, EC = 0x6cec6098, SPID: 67, ECID: 4, Blocking
2006-04-13 09:47:43.39 spid4 Producer: Xid Slot: 2, EC = 0x6c40c098, SPID: 67, ECID: 2, Blocking
2006-04-13 09:47:43.39 spid4 Producer: Xid Slot: 1, EC = 0x6bf8c098, SPID: 67, ECID: 1, Blocking
2006-04-13 09:47:43.39 spid4 Producer: Xid Slot: 0, EC = 0x68072098, SPID: 67, ECID: 3, Blocking
2006-04-13 09:47:43.39 spid4 Producer List::
2006-04-13 09:47:43.39 spid4 Consumer List::
2006-04-13 09:47:43.39 spid4 Coordinator: EC = 0x4a23b508, SPID: 67, ECID: 0, Not Blocking
2006-04-13 09:47:43.39 spid4 Port: 0x19db0100 Xid Slot: -1, EC: 0x4a23b508, ECID: 0 (Coordinator), Exchange
2006-04-13 09:47:43.39 spid4 Node:1
2006-04-13 09:47:43.39 spid4
2006-04-13 09:47:43.39 spid4
2006-04-13 09:47:43.39 spid4 -- next branch --
2006-04-13 09:47:43.39 spid4
2006-04-13 09:47:43.39 spid4 Waiting for parallel threads to enlist.
2006-04-13 09:47:43.39 spid4 Node:2
2006-04-13 09:47:43.39 spid4
2006-04-13 09:47:43.39 spid4 Producer: Xid Slot: 3, EC = 0x6cec6098, SPID: 67, ECID: 4, Blocking
2006-04-13 09:47:43.39 spid4 Producer: Xid Slot: 2, EC = 0x6c40c098, SPID: 67, ECID: 2, Blocking
2006-04-13 09:47:43.39 spid4 Producer: Xid Slot: 1, EC = 0x6bf8c098, SPID: 67, ECID: 1, Blocking
2006-04-13 09:47:43.39 spid4 Producer: Xid Slot: 0, EC = 0x68072098, SPID: 67, ECID: 3, Blocking
2006-04-13 09:47:43.39 spid4 Producer List::
2006-04-13 09:47:43.39 spid4 Consumer List::
2006-04-13 09:47:43.39 spid4 Coordinator: EC = 0x4a23b508, SPID: 67, ECID: 0, Not Blocking
2006-04-13 09:47:43.39 spid4 Input Buf: Language Event: EXEC Get_Next_Transcription '04/13/2006 09:47:38 AM'
2006-04-13 09:47:43.39 spid4 SPID: 67 ECID: 0 Statement Type: UPDATE Line #: 17
2006-04-13 09:47:43.39 spid4 Port: 0x19db0100 Xid Slot: -1, EC: 0x4a23b508, ECID: 0 (Coordinator), Exchange
2006-04-13 09:47:43.39 spid4 Node:1
2006-04-13 09:47:43.39 spid4
2006-04-13 09:47:43.39 spid4 Wait-for graph
2006-04-13 09:47:43.39 spid4
2006-04-13 09:47:43.39 spid4 ...
2006-04-13 09:47:43.39 spid4 ResType:PageSupp Stype:'OR' SPID:67 ECID:3 Ec:(0x68072098) Value:0x68072098 Cos
2006-04-13 09:47:43.39 spid4 Victim Resource Owner:
2006-04-13 09:47:43.39 spid4 Producer: Xid Slot: 3, EC = 0x6cec6098, SPID: 67, ECID: 4, Blocking
2006-04-13 09:47:43.39 spid4 Producer: Xid Slot: 2, EC = 0x6c40c098, SPID: 67, ECID: 2, Blocking
2006-04-13 09:47:43.39 spid4 Producer: Xid Slot: 1, EC = 0x6bf8c098, SPID: 67, ECID: 1, Blocking
2006-04-13 09:47:43.39 spid4 Producer: Xid Slot: 0, EC = 0x68072098, SPID: 67, ECID: 3, Blocking
2006-04-13 09:47:43.39 spid4 Producer List::
2006-04-13 09:47:43.39 spid4 Consumer List::
2006-04-13 09:47:43.39 spid4 Coordinator: EC = 0x4a23b508, SPID: 67, ECID: 0, Not Blocking
2006-04-13 09:47:43.39 spid4 Port: 0x19db0100 Xid Slot: -1, EC: 0x4a23b508, ECID: 0 (Coordinator), Exchange
2006-04-13 09:47:43.39 spid4 Node:1
2006-04-13 09:47:43.39 spid4
2006-04-13 09:47:43.39 spid4
2006-04-13 09:47:43.39 spid4 -- next branch --
2006-04-13 09:47:43.39 spid4
2006-04-13 09:47:43.39 spid4 Waiting for parallel threads to enlist.
2006-04-13 09:47:43.39 spid4 Node:8
2006-04-13 09:47:43.39 spid4
2006-04-13 09:47:43.39 spid4 Producer: Xid Slot: 3, EC = 0x6cec6098, SPID: 67, ECID: 4, Blocking
2006-04-13 09:47:43.39 spid4 Producer: Xid Slot: 2, EC = 0x6c40c098, SPID: 67, ECID: 2, Blocking
2006-04-13 09:47:43.39 spid4 Producer: Xid Slot: 1, EC = 0x6bf8c098, SPID: 67, ECID: 1, Blocking
2006-04-13 09:47:43.39 spid4 Producer: Xid Slot: 0, EC = 0x68072098, SPID: 67, ECID: 3, Blocking
2006-04-13 09:47:43.39 spid4 Producer List::
2006-04-13 09:47:43.39 spid4 Consumer List::
2006-04-13 09:47:43.39 spid4 Coordinator: EC = 0x4a23b508, SPID: 67, ECID: 0, Not Blocking
2006-04-13 09:47:43.39 spid4 Port: 0x19db0100 Xid Slot: -1, EC: 0x4a23b508, ECID: 0 (Coordinator), Exchange

View Replies !