Locking Issues When Updating Remote Table

Hi everyone

I have a Stored Procedure mySP that updates a table in a linked server.
The same table is also being updated by other processes in the remote server

Usually mySP updates 10-20 records at a time, and that works fine.

BEGIN TRANSACTION

......

......


UPDATE [REMOTE_SERVER].[DB].dbo.[REMOTE_TABLE] SET VALUE = 2

WHERE ID IN (SELECT ID FROM LOCAL_TABLE)

COMMIT

The problem begins when mySP tries to update 100+ records, it gets a time out probably due to locking issues
(indexes are properly set in both remote and local tables )

I read some articles where they suggest to break the transaction in several batches of a smaller size to avoid locking issues.

Something like:

BEGIN TRANSACTION

.....

.....


DECLARE @Rows int

DECLARE @Batchsize int

SET @Batchsize = 10

SET @Rows = 1

WHILE (@Rows > 0 )

BEGIN

UPDATE [REMOTE_SERVER].[DB].dbo.[REMOTE_TABLE] SET VALUE = 2

WHERE ID IN (SELECT TOP (@Batchsize) ID FROM LOCAL_TABLE)

DELETE TOP (@Batchsize) FROM LOCAL_TABLE

SET @Rows = @@ROWCOUNT

END

COMMIT


But it seems to make no difference.

Any ideas/suggestions?

Thank you