Following test case causes a deadlock in SQL Server but not in Oracle.

Process 1 selects the primary key column a record with an exact hit where clause. Using one index.
Process 2 selects the same record form a table in fifo like manner using row level locking and another index and blocks as expected.
Process 1 deletes the record by primary key.

With SQL Server this causes a deadlock.
With Oracle no deadlock occurs.

Is there a way to avoid the deadlock in SQL Server?

Below you’ll find more details about how to reproduce and compare the behavior.

SQL SERVER

SQL Server version 2019 ond Windows 10

Setup

create table deadlocktest (
  pk int, 
  id1 int, 
  id2 int, 
  x int, 
  y int, 
  seq int, 
  primary key nonclustered  (pk) );

create UNIQUE NONCLUSTERED index  idx_id1 on deadlocktest(id1, x, seq);
create UNIQUE NONCLUSTERED index  idx_id2 on deadlocktest(id2);
create UNIQUE NONCLUSTERED index  idx_seq on deadlocktest(seq);
  
insert into deadlocktest values (1, 10, 100, 1000, 10000, 1);
insert into deadlocktest values (2, 20, 200, 2000, 20000, 2);

Options READ_COMMITTED_SNAPSHOT and ALLOW_SNAPSHOT_ISOLATION are enabled.

Test case

Autocommit is off, transaction isolation level = READCOMMITTED.

T1: BEGIN TRANSACTION;
T2: BEGIN TRANSACTION;
T1: select top 1 pk, x from deadlocktest WITH (UPDLOCK ROWLOCK) where id1 = 10 and x = 1000 and seq = 1;
T2: select top 1 pk, x from deadlocktest WITH (UPDLOCK ROWLOCK) where id1 = 10 and x = 1000  order by seq;
    BLOCKED
T1: delete from deadlocktest where pk = 1;
T2: DEADLOCK ERROR

The same occurs if the select in T1 doesn’t use UPDLOCK ROWLOCK. It is only used in this test case to get control over the sequence of execution.

The deadlock graph is:

The left side (victim) is the T2 select statement, the right side is the T1 insert statement.

The query plans are (you’ll see different indexes are in use):

select top 1 pk, x from deadlocktest WITH (UPDLOCK ROWLOCK) where id1 = 10 and x = 1000  order by seq
  |--Top(TOP EXPRESSION:((1)))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
            |--Index Seek(OBJECT:([XGEN].[dbo].[deadlocktest].[idx_id1]), SEEK:([XGEN].[dbo].[deadlocktest].[id1]=(10) AND [XGEN].[dbo].[deadlocktest].[x]=(1000)) ORDERED FORWARD)
            |--RID Lookup(OBJECT:([XGEN].[dbo].[deadlocktest]), SEEK:([Bmk1000]=[Bmk1000]) LOOKUP ORDERED FORWARD)

select top 1 pk, x from deadlocktest WITH (UPDLOCK ROWLOCK) where id1 = 10 and x = 1000 and seq = 1
  |--Top(TOP EXPRESSION:((1)))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([Bmk1000]))
            |--Index Seek(OBJECT:([XGEN].[dbo].[deadlocktest].[idx_seq]), SEEK:([XGEN].[dbo].[deadlocktest].[seq]=(1)) ORDERED FORWARD)
            |--RID Lookup(OBJECT:([XGEN].[dbo].[deadlocktest]), SEEK:([Bmk1000]=[Bmk1000]),  WHERE:([XGEN].[dbo].[deadlocktest].[id1]=(10) AND [XGEN].[dbo].[deadlocktest].[x]=(1000)) LOOKUP ORDERED FORWARD)

delete from deadlocktest where pk = 1
  |--Table Delete(OBJECT:([XGEN].[dbo].[deadlocktest]), OBJECT:([XGEN].[dbo].[deadlocktest].[PK__deadlock__321403CE902EA0FE]), OBJECT:([XGEN].[dbo].[deadlocktest].[idx_id1]), OBJECT:([XGEN].[dbo].[deadlocktest].[idx_id2]), OBJECT:([XGEN].[dbo].[deadlocktest].[idx_seq]))
       |--Index Seek(OBJECT:([XGEN].[dbo].[deadlocktest].[PK__deadlock__321403CE902EA0FE]), SEEK:([XGEN].[dbo].[deadlocktest].[pk]=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD)

ORACLE

Oracle XE 18 on Windows 10

Setup

create table deadlocktest (
  pk int, 
  id1 int, 
  id2 int, 
  x int, 
  y int, 
  seq int, 
  primary key (pk) );
  
insert into deadlocktest values (1, 10, 100, 1000, 10000, 1);
insert into deadlocktest values (2, 20, 200, 2000, 20000, 2);

create UNIQUE index  idx_id1 on deadlocktest(id1, x, seq);
create UNIQUE index  idx_id2 on deadlocktest(id2);
create UNIQUE index  idx_seq on deadlocktest(seq);

Test case

Autocommit is off, transaction isolation level = READCOMMITTED.

T1: SET TRANSACTION READ WRITE;
T2: SET TRANSACTION READ WRITE;
T1: select pk, x  from deadlocktest where id1 = 10 and x = 1000 and seq = 1 for update;
T2: select pk, x from deadlocktest where id1 = 10 and x = 1000  order by seq for update;
    BLOCKED
T1: delete from deadlocktest where pk = 1;

No deadlock, T1 completes the delete statement, T2 keeps blocking until T1 ends.

Additonal Informtation

The select with locking in T1 is used for this test scenarion to get control over the concurrent transactions. In real application context this select is missing and the deadlock as shown above in the deadlock graph.