/****************************************************************************/
/*                        http://aboutsqlserver.com                         */
/*                  Written by Dmitri V. Korotkevitch                       */
/*                        dk@aboutsqlserver.com                             */
/****************************************************************************/
/*     Range (RangeS-U) locks deadlock due to IGNORE_DUP_KEY index option   */
/****************************************************************************/

use tempdb
go

create table dbo.IgnoreDupKeysDeadlock
(
	CICol int not null,
	NCICol int not null
);

create unique clustered index IDX_IgnoreDupKeysDeadlock_CICol
on dbo.IgnoreDupKeysDeadlock(CICol);

create unique nonclustered index IDX_IgnoreDupKeysDeadlock_NCICol
on dbo.IgnoreDupKeysDeadlock(NCICol)
with (ignore_dup_key = on);

insert into dbo.IgnoreDupKeysDeadlock(CICol, NCICol)
values(0,0),(5,5),(10,10),(20,20);
go

-- SESSION 1 CODE
set transaction isolation level read uncommitted
begin tran
	/* STEP 1 */
	insert into dbo.IgnoreDupKeysDeadlock(CICol,NCICol)
	values(1,1);

	select request_session_id, resource_type, resource_description
		,resource_associated_entity_id, request_mode, request_type, request_status
	from sys.dm_tran_locks
	where request_session_id = @@SPID;

	-- Run Session 2 Code

	/* STEP 2 */
	insert into dbo.IgnoreDupKeysDeadlock(CICol,NCICol)
	values(11,11);
commit;
go

-- SESSION 2 CODE
set transaction isolation level read committed
begin tran
	insert into dbo.IgnoreDupKeysDeadlock(CICol,NCICol)
	values(12,12);

	select request_session_id, resource_type, resource_description
		,resource_associated_entity_id, request_mode, request_type, request_status
	from sys.dm_tran_locks
	where request_session_id = @@SPID;

	insert into dbo.IgnoreDupKeysDeadlock(CICol,NCICol)
	values(2,2);
commit;
go

