Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Tablas con índice y opción IGNORE_DUP_KEY=ON problemas en los bloqueos al insertar en la tabla


Tablas con índice y opción IGNORE_DUP_KEY=ON problemas en los bloqueos al insertar en la tabla

Author
Message
joseantonio.m
joseantonio.m
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 11
Hola,

Tengo una aplicación en SQL Server 2008 con varias tablas que tienen índices únicos con la opción IGNORE_DUP_KEY=ON que me permite insertar masivamente datos desde otras fuentes sin provocar errores de duplicados del índice. Sin embargo esta opción del índice me da problemas a la hora de realizar inserciones en una tabla desde diferentes transacciones que hacen los diferentes usuarios. Por ejemplo un usuario agrega un registro a una tabla en cuestión dentro de una transacción y hasta que no se termina la transacción otros usuarios no pueden insertar registros en esa tabla por que se encuentra bloqueada. ¿Como puedo solucionar este problema?

Saludos,

José Antonio Muñoz
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8582 Visits: 18157
Antes que nada, bienvenido a este sitio.
Te recomiendo que escribas en inglés ya que así recibirás más ayuda y no te quedarás con la ayuda de sólo unos cuantos que hablamos español.
Ahora, estás hablando de 2 problemas distintos, la opción IGNORE_DUP_KEY=ON que evita que toda una operación de insert falle. Sin embargo, no estoy seguro de que se relacione con el otro detalle que comentas.
Cuando se realiza una instrucción de lectura o escritura sobre una tabla, esta (o parte de esta) queda bloqueada para mantener la integridad de las bases de datos. Para evitar problemas, te recomiendo que leas acerca de los TRANSACTION ISOLATION LEVELS para que veas si te puede ayudar a solucionar tu problema.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
LutzM
LutzM
SSCertifiable
SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)SSCertifiable (7K reputation)

Group: General Forum Members
Points: 7011 Visits: 13559
Ich habe keine Ahnung, wovon du sprichst, Luis!
(I have no idea what you're talking about, Luis!) :-D



Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)SSCrazy Eights (8.6K reputation)

Group: General Forum Members
Points: 8582 Visits: 18157
LutzM (11/12/2013)
Ich habe keine Ahnung, wovon du sprichst, Luis!
(I have no idea what you're talking about, Luis!) :-D

I'm just trying to help :-D
I just hope people won't rely just on me.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
joseantonio.m
joseantonio.m
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 11
Hola Luis, gracias por tu respuesta,

pero quiero comentarte que no son problemas distintos, antes de solicitar ayuda en el foro he analizado todo lo que me comentas. He probado las diferentes configuraciones de TRANSACTION ISOLATION LEVELS pero el problema persiste. En cuanto a la opción IGNORE_DUP_KEY en la creación de índices no es por una intuición mía el hecho de que está provocando el problema en el bloqueo de la tabla en cuestión, si no por que lo he analizado haciendo diversas pruebas: he creado una tabla con un índice único clustered con la opción IGNORE_DUP_KEY=OFF, y funciona perfectamente las inserciones de registros en diferentes transacciones, no se producen bloqueos. Sin embargo la misma tabla la creo con el mismo índice y con la opción IGNORE_DUP_KEY=ON y a partir de ahí se reproducen los problemas que he comentado al principio.

Hi Luis, thanks for your answer,

but I want to tell that there are different problems, before asking for help in the forum I analyzed everything I comets. I've tried different configurations TRANSACTION ISOLATION LEVELS but the problem persists. As for the option IGNORE_DUP_KEY indexing is not my intuition that is causing the problem in locking the table in question, if not I analyzed by various tests: I created a table with a single index with the option clustered IGNORE_DUP_KEY = OFF, and works perfectly insertions in different transaction records, no blockages occur. But I think the same table with the same index and IGNORE_DUP_KEY = ON option and thereafter causes the problems I mentioned at the beginning.
joseantonio.m
joseantonio.m
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 11
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
Sean Pearce
Sean Pearce
Ten Centuries
Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)Ten Centuries (1.2K reputation)

Group: General Forum Members
Points: 1170 Visits: 3432
Hi,

Your example shows that the insert with IGNORE_DUP_KEY = ON produces a much more complicated query plan because it needs to filter out the duplicates before performing the insert. This also introduces an extra Key Range Lock as seen in sp_lock.







The SQL Guy @ blogspot

@SeanPearceSQL

About Me
Attachments
sp_lock.png (57 views, 13.00 KB)
ex plans.png (55 views, 20.00 KB)
joseantonio.m
joseantonio.m
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 11
What that means? What no solution? Do I have to set the option IGNORE_DUP_KEY=OFF to work well locks when inserting? Is not there another solution? Crying

thanks and regards
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search