CODEWITHSUNDEEP
databaselockingsynchronizationmicrosoft-sync-framework
Marty
Marty

Reputation: 7522

Sync Framework - Update/Update conflict in FK column causes locks in FK table

I am experiencing a very strange issue with custom conflict handling using the Sync Framework. Considering the following simple database structure:

CREATE TABLE [dbo].[Container](
    [Id] [uniqueidentifier] NOT NULL DEFAULT newid(),
    [LocationId] [uniqueidentifier] NULL REFERENCES [dbo].[Location],
    .....
)

CREATE TABLE [dbo].[Location](
    [Id] [uniqueidentifier] NOT NULL DEFAULT newid(),
    [Name] [varchar](100) NULL,
    .....
)

Now suppose I have a container in my database. On the client, I set its LocationId to one value. On the server, I set it to some other value. I attempt a sync, and as expected, I receive an ApplyChangeFailedEvent of type LocalUpdateRemoteUpdate.

As part of my conflict resolution process, I would like to display information about the conflict to the user, and have them select the correct location. To do this, I need to read the Location corresponding to the LocationId on both the client side and the server side, and this is where the problem comes in. My reads time out, because the sync framework seems to have locked the table. sp_lock2 shows that there is a lock of type 'X' on the Location table.

The kicker is that this only happens for some foreign key columns on some tables. Most of them are fine, but some of them cause the exclusive lock every time.

If anyone has any idea on what specifically causes the exclusive lock during conflict resolution, or on what I can do to avoid it or work around it, it would be greatly appreciated. If I can't reliably read from the database during conflict resolution, my entire conflict handling strategy is pretty much pointless.

UPDATE: Looks like I was slightly wrong. It's not locking the entire table, only the records involved in the conflict. I can do a SELECT * FROM Location WHERE ID = (id of record not involved in conflict), I just can't do a SELECT * FROM Location WHERE Id = (id of conflict record).

Upvotes: 1

Views: 637

Answers (1)

Marty
Marty

Reputation: 7522

I'm not exactly sure why, but I can get around the problem by performing my reads in a READ UNCOMMITTED transaction. Go figure.

Upvotes: 1

Related Questions