SQL Server testing deadlocks on distributed view

Question

I'm trying to test some deadlock cases on a distributed view. I set up three nodes with docker. All servers are registered as linked server and the distributed view is working fine.

Datanode1 has the table movie_33 and holds all movies up to the id 333
Datanode2 has the table movie_66 and holds alld movies from 334 up to 666
Datanode3 has the table movie_99 and holds all movies from 667 up to 999

The view connects all tables.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO

CREATE VIEW [dbo].[movie]
AS
    SELECT *
    FROM dbo.movie_33
    UNION ALL
    SELECT *
    FROM [172.16.1.3].Sakila.dbo.movie_66
    UNION ALL
    SELECT *
    FROM [172.16.1.4].Sakila.dbo.movie_99
GO

Now I want that one transaction is the victim. I do it with the following code:

Window 1:

--
-- Example on (distributed) transactions in SQL Server
--
-- Deadlock tracing requires administrative permissions (sysadmin)!
--
-- IDs for additional testing:
-- 321 & 123 are both on mysql1
-- 123 & 456 are on mysql1 and mysql2
-- 456 & 654 are both on mysql2
-- 456 & 789 are on mysql2 and mysql3

--
-- Query Window 1 [TA1]
--
use sakila

-- allow the entire transaction to be aborted, if a sub-transaction fails
set xact_abort on

-- enable tracing for deadlocks and output status
dbcc traceon (1204,-1) 
dbcc traceon (1222,-1) 
dbcc tracestatus(-1) 

-- we want TA1 to become the victim and TA2 to be successful
set deadlock_priority LOW
set transaction isolation level read committed

begin transaction

PRINT 'Start'
update dbo.movie set title='test1' where movie_id = 456 -- obtains lock for this row
-- allow other transaction to acquire locks
waitfor delay '00:00:10'
update  dbo.movie set title='test1' where movie_id = 789 -- results in deadlock with TA2

rollback

Window 2:

--
-- Query Window 2 [TA2] (execute immediately after Query Window 1)
--
use sakila

-- allow the entire transaction to be aborted, if a sub-transaction fails
set xact_abort on

-- enable tracing for deadlocks and output status
dbcc traceon (1204,-1) 
dbcc traceon (1222,-1) 
dbcc tracestatus(-1) 

-- we want TA1 to become the victim and TA2 to be successful
set deadlock_priority HIGH
set transaction isolation level read committed

begin transaction

update dbo.movie set title='test2' where movie_id = 789 -- obtains lock for this row
update dbo.movie set title='test2' where movie_id = 456 -- this row should be locked by TA1

-- we do not want to cause permanent changes to the database
rollback

So when I use ids which querying items on the same server. The deadlock test is working fine. (for example 321 & 123 are both on mysql1 or 456 & 654 are both on mysql2)

When using ids which querying items on different server i get the following error

Msg 7399, Level 16, State 1, Line 21
The OLE DB provider "MSOLEDBSQL" for linked server "172.16.1.3" reported an error. Execution terminated by the provider because a resource limit was reached.

Msg 7320, Level 16, State 2, Line 21
Cannot execute the query "UPDATE "Sakila"."dbo"."movie_66" set "title" = 'test2' WHERE "movie_id"=(456)" against OLE DB provider "MSOLEDBSQL" for linked server "172.16.1.3".

Can someone help me with the problem?

Thank you in advance

SQL Server testing deadlocks on distributed view

0 Answers