Re: Transactional replication reliabiliy

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Thanks for the feedback. Given your scenario, snapshot processing definitely
(sync_method = 'database snapshot' or not) is *not* suitable as generating a
snapshot incurs significant system resource contention even if there aren't
any deadlocks or lock contention. That said, the snapshot agent does attempt
to mutate the lock acquisition sequence upon encountering a deadlock so for
a system with more or less consisent access patterns across different
tables, the snapshot agent should eventually grab the table locks in a way
that avoids deadlocks with any concurrent processes. I am guessing that
there are probably too many "hot" tables in your system at any given time
for the snapshot agent to converge to a deadlock-free lock acquisition
sequence in a timely manner. Theoretically, you can try breaking up your
publication into multiple publications with smaller number of articles to
minimize the chance of deadlocks during snapshot generation.

-Raymond

"andsm" <andsm@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:156C8C6A-61A4-45A6-A364-08DE9746B6CA@xxxxxxxxxxxxxxxx
It is very simple - on the system snapshot agent will be unable to get
shared table locks because it constantly will be deadlocked. - The system
have several thousands users at peak time and several hundreds users
during
time with minimal activity, high writing activity, OLTP, finance
processing
system.

And I not see what can be done here - since in case if any transaction,
due
to the locks, will be delayed - affected users will be very unhappy and
will
start to complain to customer support because they may lose $$ due to the
delays.

"Raymond Mak [MSFT]" wrote:

As a sort of academic exercise, if the snapshot agent has trouble
acquiring
shared table locks when sync_method = 'database snapshot', I am a bit
curious to know what is going on in the system that is blocking the
snapshot
agent. I know it is always theoretically possible to have such situations
but it is better to wrap my head around a concrete example (or perhaps do
something about it).

-Raymond

"Raymond Mak [MSFT]" <rmak@xxxxxxxxxxxxxxxxxxxx> wrote in message
news:OdQJDZYeHHA.4188@xxxxxxxxxxxxxxxxxxxxxxx
Regarding 2, the shared locks will only be held for as long as it takes
to
create the database snapshot which should be a minimal amount of time.
Of
course, if that is still unacceptable, 'initialize with backup' is the
way
to go.

-Raymond
"andsm" <andsm@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:6C6E47B9-A989-4000-899F-7CAEBF9BA9C6@xxxxxxxxxxxxxxxx
Locks which I see during creation of transactional publication and
genaration
of snapshot with @sync_option = 'database snapshot', on SQL2k5 SP2 EE:

1. During publication creation - it place Sch_M locks on all tables
selected
for replication, during execution of sp_addarticle. Bad, but not
much -
since
it lock only one object at time.

2. During snapshot generation, with option 'database snapshot' -
It place S locks on all tables which selected for replication. And its
really bad and it means downtime for system, which should work 24x6.
Are
any
ways to reduce locking and remove the downtime? If I will use
initialization
from backup, what will be with locks?

"Paul Ibison" wrote:

OK - I assumed you were talking exclusively about when the snapshot
is
taken. The schema modification lock taken when sp_addarticle is
executed
is
presumably taken to mark the object as replicated so I wouldn't
expect
much
can be done about it, but it'll be a very short-term one.
During initialization for concurrent snapshot generation the shared
locks
exist only for a few seconds - if this is too much have a look at
"database
snapshot" for the @sync_method.
Cheers,
Paul Ibison SQL Server MVP,
www.replicationanswers.com










.

Relevant Pages

  • Re: Transactional replication reliabiliy
    ... It is very simple - on the system snapshot agent will be unable to get ... shared table locks because it constantly will be deadlocked. ... create the database snapshot which should be a minimal amount of time. ...
    (microsoft.public.sqlserver.replication)
  • Re: Transactional replication reliabiliy
    ... the shared locks will only be held for as long as it takes to ... create the database snapshot which should be a minimal amount of time. ...
    (microsoft.public.sqlserver.replication)
  • Re: Transactional replication reliabiliy
    ... As a sort of academic exercise, if the snapshot agent has trouble acquiring ... create the database snapshot which should be a minimal amount of time. ... During publication creation - it place Sch_M locks on all tables ...
    (microsoft.public.sqlserver.replication)
  • Re: Locking problems when initializing the subscriptions
    ... There is an option to not lock the tables during transaction replication ... It will take longer to generate the snapshot, ... > Is there any way you can do a no sync subscription? ... >> In my system locks are held for over a minute. ...
    (microsoft.public.sqlserver.replication)
  • Re: Subscription Impact on Production Server
    ... For the snapshot, there are locks taken out on the ... For synchronization, in transactional, the distribution agent will read the ...
    (microsoft.public.sqlserver.replication)