June 20, 2014 at 2:42 pm
We have an application that is experiencing deadlock issues with a SQL Server 2008 Standard database.
The server database has a filtered merge replication publication. There are subscribers running SQL Server 2008 Express that issue a pull merge every 1 minute to keep a local database up to date. The publication filter uses a single table to identify the information that should go to specific subscribers. A record is written to that table which includes the subscriber’s name and a primary key value for the master record to which all foreign keyed slave table records will pulled locally.
All workstations, whether they are subscribers to the database, and those that do not have a local store, perform CRUD transactions directly to the server (published) database. If a connection is lost to the server, those computers that are subscribers will be able to continue their actions against their local store. Upon the server reconnection, the application will start performing transactions against the server directly.
Previously, all computers were subscribers performing all their CRUD transactions against the local store. Deadlocks were not an issue.
The deadlocks now typically involve inserts and deletes from the filter table and CRUD action against a table that has a foreign key to master table. The deadlocks are keylocks that the filter table creates.
To alleviate this, following things are done with some success:
1) Added a missing index on the subscriber’s name on the filter table - adding this index was a big help. Deadlocks are reduced now. But now this index is bottleneck in which all the replication queries from its triggers use a partition view which reference this index and deadlocks involve this key
2) Modified few other index to remove keylookups after looking at the execution plan for the insert and delete - this was done just to reduce IO and make transactions go faster.
The filter table is a big big bottleneck.
Has anyone experienced this type of deadlocking involving filtered replication? Any ideas/suggestions would be greatly appreciated.
June 20, 2014 at 9:50 pm
The deadlocks now typically involve inserts and deletes from the filter table and CRUD action against a table that has a foreign key to master table. The deadlocks are keylocks that the filter table creates.
Is dropping the master-child relationship as option ?
--------------------------------------------------------------------------------------------------
I am just an another naive wannabe DBA trying to learn SQL Server
June 23, 2014 at 8:06 am
Sachin Nandanwar (6/20/2014)
The deadlocks now typically involve inserts and deletes from the filter table and CRUD action against a table that has a foreign key to master table. The deadlocks are keylocks that the filter table creates.
Is dropping the master-child relationship as option ?
No. that is not where the deadlock is coming from. The deadlock comes between merge replication's filtered queries and user's transactions on that same filter table.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply