Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Do row filters avoid full table locks? Expand / Collapse
Author
Message
Posted Friday, March 8, 2013 2:19 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, July 10, 2014 9:50 AM
Points: 12, Visits: 85
Hallo
in case a row filter exists on a published table in a snapshot replication, does the snapshot agent takes locks at rows level rather than at table level?
I'm not finding any documentation about that.

(In my specific case, I set up a snapshot replication, that sometimes fails since third party applications lock published tables for a long time.
I raised the snapshot agent timeout to 3600s, but it's not the definitive solution)

thanks in advance



Post #1428452
Posted Friday, March 8, 2013 12:27 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 7:59 PM
Points: 6,147, Visits: 7,203
As usual, it depends.

Replication uses locking like anything else. Even if you have a row filter on, unless that's a VERY small row filter (5000 rows or less), the possibility to want to upgrade to a table lock exists. Doesn't mean it will however, it will simply try.

However, if your 3rd party apps have exclusive locks on rows your replication needs, there's nothing your locking techniques are going to fix for that unless you start doing some madness like read uncommitted or something equally painful... which I'm not even sure you CAN do in replication. I certainly hope not.



- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables

Twitter: @AnyWayDBA
Post #1428733
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse