This is an example:

Example A:

CREATE TABLE Tabla (Codigo Int, Fecha Date);

CREATE UNIQUE NONCLUSTERED INDEX X_Prueba ON Tabla (Codigo) WITH (IGNORE_DUP_KEY=ON);

(In session 1)

BEGIN TRAN

INSERT Tabla VALUES (1,'01/01/2013')

(1 rows affected)

(In session 2)

BEGIN TRAN

INSERT Tabla VALUES (2,'02/01/2013')

Error: Exceeded timeout lock request.

Example B:

CREATE TABLE Tabla (Codigo Int, Fecha Date);

CREATE UNIQUE NONCLUSTERED INDEX X_Prueba ON Tabla (Codigo) WITH (IGNORE_DUP_KEY=OFF);

(In session 1)

BEGIN TRAN

INSERT Tabla VALUES (1,'01/01/2013')

(1 rows affected)

(In session 2)

BEGIN TRAN

INSERT Tabla VALUES (2,'02/01/2013')

(1 rows affected)

as shown in the example to change the option: IGNORE_DUP_KEY=ON into IGNORE_DUP_KEY=OFF pruduce not lock in session 2.

Thanks and regards,

Jose Antonio Muñoz