Deadlocked by Deadlocks

Deadlocked by Deadlocks

Post by D Hol » Wed, 18 Jul 2001 02:57:31



I'm new to SQL and even newer to replication so this is really flustrating -
I have set up a merge publication and have successfully set up two push
subscriptions, the only problem is that the replication keeps getting
stopped by Deadlocks (Error 1205).  How do I stop these and can anyone tell
me what these are getting caused by?????
 
 
 

Deadlocked by Deadlocks

Post by TSVK » Wed, 18 Jul 2001 03:49:02


The first step would be stagger the schedule of merge agents and limit the
number of concurrent merges if possible.


Quote:> I'm new to SQL and even newer to replication so this is really
flustrating -
> I have set up a merge publication and have successfully set up two push
> subscriptions, the only problem is that the replication keeps getting
> stopped by Deadlocks (Error 1205).  How do I stop these and can anyone
tell
> me what these are getting caused by?????


 
 
 

Deadlocked by Deadlocks

Post by Daisy Hol » Wed, 18 Jul 2001 04:43:49


I do have the merge subscriptions as concurrent subscriptions because I need the data to be as up to date as possible (and currently transactional replication does not work because of Distributed tranaction error, but this is another problem).  So I turned to Merge - so now what?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Deadlocked by Deadlocks

Post by Christopher John Cutt » Wed, 18 Jul 2001 07:55:21


I too had this problem, but have effectively solved the problem by not
running the agents continuously.  I created a job that cycles through each
job in  the REPL-Merge category, starting them and waiting for completion
before starting the next.  latency is minimal and deadlocks non existent.  I
suspect there is some overhead in repeatedly starting these jobs
(sp_start_job), as opposed to allowing them to run continuously, but not
enough to cause me concern.


I do have the merge subscriptions as concurrent subscriptions because I need
the data to be as up to date as possible (and currently transactional
replication does not work because of Distributed tranaction error, but this
is another problem).  So I turned to Merge - so now what?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Deadlocked by Deadlocks

Post by Charle » Wed, 18 Jul 2001 22:28:26


Daisy,

Can you describe the application you are using replication for? There maybe
other options like log shipping that may make more sense.

Charles Johnson


I do have the merge subscriptions as concurrent subscriptions because I need
the data to be as up to date as possible (and currently transactional
replication does not work because of Distributed tranaction error, but this
is another problem).  So I turned to Merge - so now what?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

 
 
 

Deadlocked by Deadlocks

Post by D Hol » Thu, 19 Jul 2001 23:52:07


Ok, that sounds like a good idea, about how long does it take for all of the
data to be replicated?  It is almost vital that the data be replicated
within less than a minute.

Thanks for the idea, I'll look into it!



> I too had this problem, but have effectively solved the problem by not
> running the agents continuously.  I created a job that cycles through each
> job in  the REPL-Merge category, starting them and waiting for completion
> before starting the next.  latency is minimal and deadlocks non existent.
I
> suspect there is some overhead in repeatedly starting these jobs
> (sp_start_job), as opposed to allowing them to run continuously, but not
> enough to cause me concern.



> I do have the merge subscriptions as concurrent subscriptions because I
need
> the data to be as up to date as possible (and currently transactional
> replication does not work because of Distributed tranaction error, but
this
> is another problem).  So I turned to Merge - so now what?

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

 
 
 

Deadlocked by Deadlocks

Post by D Hol » Thu, 19 Jul 2001 23:59:43


I am using the replication to share tables for a shipping/receiving program
between three locations.  (One enters the orders and the other two locations
complete the orders and receive orders)  It is vital that the data be as
current as possible (ie an order is placed and the other two location need
to see it immediately).  We were previously using .mdb linked tables accross
the network, but the speed was highly lacking.
I originally planned to use Transactional with immediate-update replication,
but have since given up that plan due to "technical difficulties" that
cannot seem to be resolved.

I have heard of log shipping but have absolutely no clue about it other than
the name.  Do you think that log shipping would be a better option? Or do I
just have to live with the deadlocks and spend my days making sure the
replication doesn't stop?


> Daisy,

> Can you describe the application you are using replication for? There
maybe
> other options like log shipping that may make more sense.

> Charles Johnson



> I do have the merge subscriptions as concurrent subscriptions because I
need
> the data to be as up to date as possible (and currently transactional
> replication does not work because of Distributed tranaction error, but
this
> is another problem).  So I turned to Merge - so now what?

> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

 
 
 

Deadlocked by Deadlocks

Post by Charle » Sat, 21 Jul 2001 04:19:00


D.

Log shipping will not work in your application, there is too much latency.
Your linked database approach sounds like the way you may want to proceed.
Take a look at linked servers. You could code the stored procedures or use
triggers to move the data.

Hope this helps.
--Charles Johnson


> I am using the replication to share tables for a shipping/receiving
program
> between three locations.  (One enters the orders and the other two
locations
> complete the orders and receive orders)  It is vital that the data be as
> current as possible (ie an order is placed and the other two location need
> to see it immediately).  We were previously using .mdb linked tables
accross
> the network, but the speed was highly lacking.
> I originally planned to use Transactional with immediate-update
replication,
> but have since given up that plan due to "technical difficulties" that
> cannot seem to be resolved.

> I have heard of log shipping but have absolutely no clue about it other
than
> the name.  Do you think that log shipping would be a better option? Or do
I
> just have to live with the deadlocks and spend my days making sure the
> replication doesn't stop?



> > Daisy,

> > Can you describe the application you are using replication for? There
> maybe
> > other options like log shipping that may make more sense.

> > Charles Johnson



> > I do have the merge subscriptions as concurrent subscriptions because I
> need
> > the data to be as up to date as possible (and currently transactional
> > replication does not work because of Distributed tranaction error, but
> this
> > is another problem).  So I turned to Merge - so now what?

> > *** Sent via Developersdex http://www.developersdex.com ***
> > Don't just participate in USENET...get rewarded for it!

 
 
 

1. Deadlock situtation (deadlock victim) - help needed!!

We have Sybase SQL Server 4.9.2 (HP-UX) where a deadlock situation is
occurring, but the only client output is that the process was chosen
as a deadlock victim (w/error number 1205).  Are there some trace
flags we can use (or other debugging steps) to determine what
processes were running, what table locks were in effect, or other
useful information at the time when the deadlock occurred?

Most of the DB activity is Read Only with limited writes at the end
of the program run. We have mulitple instances of this program
running in parallel and have no problems until now. We also have
Replication server running - but this shouldn't be a problem.

Any help will be greatly appreciated.

Regards,
Bryan Althaus

2. Difference Between a UNIQUE CONTRAINT and UNIQUE INDEX

3. Your server command (process id 14) was deadlocked with another process and has been chosen as deadlock victim. Re-run your command

4. Filtering dates using TO_DATE(). Is there an alternative.

5. Deadlock on update

6. SQL 2000 in a cluster environment

7. deadlock and timeout

8. Question for American Users

9. fixing deadlocking even when only single SPID

10. Deadlock involving single select with a join

11. bcp --> deadlock

12. deadlocking

13. Deadlock