Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







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 Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
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 !   View Related
Sql2000 Replication
hello,also wanted to confirm which versions of sql2000 can do replication.Would be nice to have fault-tolerance via replication so as to have ahot-standby.Oskar

View Replies !   View Related
SQL2000, SQL 7 And Replication
I have SQL 7 publishers, a SQL7 Distributor and I want to create a SQL2000 subscriber. Can this be done? Or do I have to make a SQL2000 Distributor too? Any thoughts would be appreciated.

View Replies !   View Related
Replication Between SQL7 And SQL2000?
Guys, we want to use the Replication features (or something similar) tobackup our primary SQL database, the primary DB is using SQL 7 and oursecondary server is SQL 2000 - is this possible?p.s. I know it would be more suited to use the SQL2000 server as theprimary, but thats a later plan...Appreciate your helpCheers, @sh

View Replies !   View Related
Replication SQL2000 To DB2 V8.1 Express
Hi,
Any body successfully implemented Replication from SQL2000 to DB2 V8.1 Express?
FYI, I successfully implemented replication from SQL2000 to DB2 UDB V7.2 thru Microsoft OLE DB driver last year, but the same method I use hit error when replicating to DB2 V8.1 Express. I can succeffully create the link server from SQL2000 to DB2 V8.1, but when I list DB2 tables from SQL Enterprise Server (GUI), it give me

Error 7330-Could not fetch a row from OLE DB provider 'UNKNOWN'.
OLE DB error trace[OLE/DB Provider 'UNKNOWN' IRowset::GetNextRows returned 0x80040e14]

View Replies !   View Related
Replication SQL2000 To MySQl
Hello Guys,

I'm trying to configure a Transactional Replication from SQL 2000 to MySql using MyODBC.

The problem:

When I try to add a new Subscriber, I receive the following message:

'ODBCMYSQL' could not be enabled as a subscriber because the ODBC driver for this data source does not provide sufficient functionality. Contact the vendor to obtain an updated ODBC Driver.

Error 2132: [SQL-DMO]The DSN is not a valid SQL Server Subscriber

View Replies !   View Related
Replication Access / SQL2000
Hi,

I made an application in access 2000 and I use the internet replication.

I got the replication manager installed on the same server with SQL2000 server.

What I Would like to do is replication between SQL2000 and Access database.

Is it possible to make replication between Access database and SQLServer, and is it possible to make it work in bi-directionnal mode ? cause change can occur in both database (Sql and access).

Thanks for the help

Richard

View Replies !   View Related
Replication Between SQL2000 And SQL2005
I have a distributor setup on SQL Server 2005 (9.0.3042) and am trying to create a publication on SQL Server 2000 (8.0.2040) which receives the following error in my production environment:
 

Msg 8526, Level 16, State 2, Procedure sp_addpublication, Line 802

Cannot go remote while the session is enlisted in a distributed transaction that has an active savepoint.
 
The interesting part of this equation is that I was able to get this to work without error in my DEV (development) environment and well as my QAT (test) environment.  This end result was that my distributor was SQL 2005, my publisher was SQL 2000 and my pull subscriber was SQL 2005.  I have been diligently comparing our production environment to my other environment and have yet to find differences.
 
Has anyone else seen an error similiar to this?  Any insight would be appreciated.
 
Thanks in advance,
 
Max

View Replies !   View Related
Replication From SQL2000 To SQL2005
I want to replicate from SQL2000 to SQL2005. I had configured distributor, publisher in SQL2000 and also subscriber in SQL2005. But so far no replication was done on SQL2005. I wonder SQL2000 (SP2) and SQL2005 replication will it work? Is it true that I should install Service Pack 3 and above in order to replicate my database to SQL2005.
 
Thanks
Wint

View Replies !   View Related
SQL2000 And MSDE Desktop Replication
Greetings all,

I am planning a sales automation app and needing to synchronize data to salesmen whose machines are not always online. What we've had in mind was to use our SQL Server 2000 and its' built in replication functions and possibly replicate to the MSDE SQL desktop engine on the salesmens' machines. What would be the best replication type for that? Transactional, Snapshot or Merge? Has anyone done anything similar to this? The data will originate on an AS/400 Midrange iSeries box and be replicated to the SQL 2000 box using a product such as Vision Solutions' Sybiator. From there around 15 tables will be replicated to the MSDE, each with an average of 5,000 records, though a few with 70-80k. I'm not worried about collecting data back from the salesman MSDE at this point. This is a one way replication from our SQL to the subscribers.. ANy advice or warning would be appreciated. (If someone would just like to say it's a dumb idea, that's great, too!)

Bill

View Replies !   View Related
Replication Between Sql2000 And Access 2000
Hi,

It is possible to do a bi-directionnal replication between access 2000 database and SQL server (Access and SQL server are on the same server)

What is the better way to do it.

For info, i use the SQL for the web part but access is used for an application in vab/access. Data have to be synch all the time.

Thanks for the help

Richard

View Replies !   View Related
SQL2000 Server And MSDE Replication
 Hello.

I will construct client application with MSDE.

I face with a difficult question.

It's in sync with SQL2000 Server  and MSDE.

Client number is 5000.

I fall in a dilemma. Which do i use,  Replication or to make DB Sync application ?

jongwoo

 

 

 

 

View Replies !   View Related
SQL2000 Replication,modifying The Width Of A Column
Hi All,
Is there a way by which we can modify the width of a column of a table which is being replicated without touching the ongoing transactional replication? This is for MSSQL2000 Transactional Replication.

I know (and successfully tried) that we can add a column to a table and that gets propaged to the replicate database and indeed the added column gets reflected there. How to add a column? sp_repaddcolumn or Right Click on the Publication-Properties and it shows a button to Add a Column.

This is what I have tried for modifying the width of a column of a table participating in Transactional Replication from varchar(10) to varchar(100)

MH (source) -> MH1 (Replicate)

The column “col1” had width of varchar(10) and this was altered to varchar(100).


insert into MH..test_mh values(4,'abcdeabcdefff')

select * from MH1..test_mh

exec sp_dropsubscription @publication = N'MH', @article = N'test_mh', @subscriber = N'UKPBDRMTST2', @destination_db = N'MH1'
go

exec sp_droparticle @publication = N'MH', @article = N'test_mh'
go

alter table test_mh alter column col2 varchar(100) null OR

MH1..sp_help test_mh

exec sp_addarticle @publication = N'MH', @article = N'test_mh', @source_table = N'test_mh'
go

exec sp_addsubscription @publication = N'MH', @article = N'test_mh', @subscriber = N'UKPBDRMTST2' , @destination_db = N'MH1'
go


Needless to say, help would be apreciated -:)
~Mihir


However, adding a column was possible.

View Replies !   View Related
Merge Replication SQL2000 Error 2812
Hi !

When trying to start a Merge Replication agent I get the following Error message:

The process could not enumerate the changes at the subscriber. 2812

The snapshot agent works fine as far as I can see.

The replication is set up between a Win2000 / SQL7 / SP 4 and a Win2003 / SQL2000 / SP 3a machine. Sqlserveragent on both machines is run as a system account.

Any tip is welcome!

Thanks
VincentJS

View Replies !   View Related
SQL7/SQL2000 Replication. Auth Error
I'm attemting to replicate from a SQL7 box to SQL2000.

The snapshot works fine, but it fails when it tries to push the subscription to the SQL2000 box with a trust error (I don't recall the exact message). I noticed that it fails when Enterprise Mgr is configured as: "Tools/Replication/Configure Publishing, Subscribers and Distribution", Subscriber Tab, "Impersonating SQL Service Agent". It works if I change to "SA".

This is only the case when we replicate from SQL7 to SQL2K. SQL7 to SQL7 works fine with "Impersonating". Has anybody seen this behavior? Any workarounds, other than using SA?

View Replies !   View Related
SQL7/SQL2000 Replication. Auth Error
I'm attemting to replicate from a SQL7 box to SQL2000.

The snapshot works fine, but it fails when it tries to push the subscription to the SQL2000 box with a trust error (I don't recall the exact message). I noticed that it fails when Enterprise Mgr is configured as: "Tools/Replication/Configure Publishing, Subscribers and Distribution", Subscriber Tab, "Impersonating SQL Service Agent". It works if I change to "SA".

This is only the case when we replicate from SQL7 to SQL2K. SQL7 to SQL7 works fine with "Impersonating". Has anybody seen this behavior? Any workarounds, other than using SA?

View Replies !   View Related
Do I Really Need A Snapshot (to Initialize Transactional Replication, In SQL2000)?
I have a pretty big (350 gb) OLTP database that I want to replicate in its entirety.  I'm concerned about the impact of taking a snapshot of it (it is processing at some level pretty much 24x7).  I know on SQL2005 there is the option to initialize from backup, but unfortunately we won't be on 2005 in time.

I'm thinking of doing something like this:

Set up the distributor, publication, and subscription
Turn off distribution agent
Set the publisher to "sync with backup"
Backup the publisher, full then log
Truncate tables MSrepl_transactions and MSrepl_commands in the distribution db (I don't have any other replication going on)
Turn off "sync with backup"
Restore the full and tran log backups to new subscriber db
Create subscriber stored procs in subscriber
Start up distribution agent

I'm looking for opinions on whether it's worth going this route to avoid taking the snapshot.  Data integrity is the number one priority -- if I have to do a snapshot to ensure that, I will do it.

Thanks in advance!

Mike

View Replies !   View Related
Trans Replication With Updating Subscriber On Sql2000 (single Quote In The Data As Char Data Type)
Hi,

I am trying to setup Trans Replication with updating subscriber on sql2000. One column on few tables got data with single quote (').

How do I handle in this case? Did any one come across such case?

Can I Change default QUOTED IDENTIFIER from ' (single quote) to something else (@@@) on SQL2000?

If yes, how to do?

Thanks
mka

View Replies !   View Related
MSDN Installation Of SQL2000 Vs. Enterprise SQL2000
We have a 5 gig production DB that is has errors occurring during the log file backup. This is the error: BlkHeader from strip 0 At 2d1f200 ExpectedAt 2d49c00 Size 400 PrevSize 400. It corrupts the log file and our server is running really slow. Does anyone know if it could be that we are using MSDN instead of Enterprise?

View Replies !   View Related
MSDN SQL2000 To Shrink Wrap SQL2000
Should I uninstall MSDN SQL2000 and then install the shrink wrapped SQL2000, or is there any harm in installing the 'real' SQL2000 over the MSDN version?

View Replies !   View Related
Sql2000 && Sql2005, Want Localhost To Use Sql2000
 i have sql2000 & sql2005 on the same machine. I am unable to register my localhost in sql2000, get an access denied error. How can I make my localhost use sql2000 database?

View Replies !   View Related
Deadlocks
Our system is reasonably complex with a lot of non-trivial stored procedures. As the load on our DB increased we're now getting more and more deadlocks (10 per day or so from about a million stored proc executions).

We try to avoid transactions where we can, and we do attempt to optimse stored procs to steer clear of deadlock conditions, but with the sheer number of stored procedures we can't possibly avoid all deadlock conditions.

One solution I'm considering is to re-run stored procs that failed because of a deadlock. In the .net code we'll run the stored proc, check for a deadlock error and if one happened, wait 100ms and try again.

What do you guys think?

View Replies !   View Related
Deadlocks
Hi EverybodyI am new to sqlserver 2000.I know basics of locks.but i dont know how toresolve deadlock issues.I am cofusing by reading articles with 90%information and remaining 10% missing.Can any one help me which is the goodsite to learn and resolve deadlocks.Note: I create deadlock. when i try to trace deadlock using dbcc traceon(1205,3604,-1).In error log showing nothing about the deadlock.showing created traceon.........Any help would be appreciated.--Message posted via http://www.sqlmonster.com

View Replies !   View Related
Deadlocks, Why?
We have a problem with a table giving us deadlock issues and we can'tfigure out why.It's a table we write to fairly often perhaps 50 times a minute. Andalso do a select of 200 rows at a time from 4 servers every 5 minutes or so.We are only keeping 48 hours worth of rows in the table which averagesat 30000 a day on a busy day.This table has 1 PK and 2 FKs plus one TEXT column which does notparticipate in the WHERE clause.We are using binded variables.We have applied the latest patch to SQL2003 server running onWindows2003. The patch is supposed to resolve deadlock issues.Anyone have any advice on how to alleviate this problem.Thanks

View Replies !   View Related
Deadlocks
If an instance of SQL 2005 was in use and was using row versioning,under what circumstances would the below error occur?Transaction (Process ID 56) was deadlocked on lock resources withanother process and has been chosen as the deadlock victim. Rerun thetransactionWe used to get this sort of thing when a large copy process was runningunder a transaction, but all it was doing was reading the records andcreating brand new records yet would still lock the entire table. Oncewe enabled the row versioning, we stopped having this issue, but itseems that there are some circumstances in which it still happens, i.e.the above error.Any ideas how that might occur?

View Replies !   View Related
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 !   View Related
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 !   View Related
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 !   View Related
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 !   View Related
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 !   View Related
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 !   View Related
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 !   View Related
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 !   View Related
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 !   View Related
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 !   View Related
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 !   View Related
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 !   View Related
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 !   View Related
Deadlocks In Profiler
I'm trying to diagnose deadlocks in SQL Profiler. The deadlocks weregenerated by Loadrunner scripts (stress testing) simulating applicationSQL via an ODBC DSN connection.2 things are puzzling me in the SQL Profiler traces that I have logged1) There are a large number of Lock:Timeout events but the 'locktimeout' setting is the default 'wait forever' so I dont know what istiming out.2)When say 2 distinct SPIDs are in a Deadlock Chain, they are using thesame ClientProcessId at the time of deadlock. What is theClientProcessId and is it relevant to the deadlock?Thank you in advance for any replies.

View Replies !   View Related
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 !   View Related
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 !   View Related
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 !   View Related
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 !   View Related
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 !   View Related
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 !   View Related
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 !   View Related
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 !   View Related
! 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 !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved