Pregunta sobre diseño de PK

y además, según las pruebas de rendimiento que un compañero de SQM (mi
empresa), ha realizado hace unos días con distintas técnicas para la
generación de secuenciales, resulta que la técnica de tabla auxiliar de
secuencias es más eficiente que esta otra que podemos llamar de "MAX +1 con
bloqueos de aplicación". La diferencia de rendimiento es que la técnica MAX
+ 1 tarda un 50% más que la de tablas auxiliares. Las pruebas las hemos
realizado con tablas con pocos campos insertando 250.000 registros en 5
conexiones simultáneas. Suponemos que la diferencia de rendimiento reside
en dos hechos. Por un lado la tabla de secuencias es una tabla con pocos
registros y con muy pocos campos y por otra parte usar bloqueos de
aplicación debe de influir en cierta medida.

Segun eso entonces es mas eficiente tener una tabla auxiliar para las
próximas secuencias.

Muchas gracias


"Jesús López" escribió en el
mensaje news:

Se me olvidaba responder a qué ocurre cuando no prospera sp_getapplock.

Cuando una conexión tiene un bloqueo de aplicación exclusivo sobre un
recurso R, y otra conexión solicita (mediante sp_getapplock) un bloqueo
exclusivo sobre el mismo recurso R, la segunda conexión se queda esperando
(indefinidamente de forma predeterminada) a que la primera libere el
bloqueo, en ese momento la segunda conexión obtendrá el bloqueo y
proseguirá su ejecución.

Por ejemplo imagina esta secuencia:

Conexión 1: BEGIN TRANSACTION
Conexión 1: sp_getapplock 'El nombre que yo le quiera dar al recurso',
'Exclusive'

La conexión 1 tiene un bloqueo exclusivo sobre el recurso 'El nombre que
yo le quiera dar al recurso'

Conexión 1: PRINT 'Ya tengo el bloqueo'

Conexión 2: BEGIN TRANSACTION
Conexión 2: sp_getapplock 'El nombre que yo le quiera dar al recurso',
'Exclusive'

La conexión 2 no puede obtener el bloqueo porque ya lo tiene conexión 1,
así que se queda esperando a que se libere

Conexión 1: COMMIT

Al hacer COMMIT, ROLLBACK o sp_releaseapplock, se libera el bloqueo de
aplicación que hemos puesto, por tanto conexión 2 ya puede obtener el
bloqueo y continuar su ejecución

Conexión 2: PRINT 'Ya tengo el bloqueo'
Conexión 2: COMMIT

La conexión 2 libera el bloqueo de manera que otra conexión puede
obtenerlo.

Saludos:

Jesús López
www.solidq.com

Jesús López,

Vamos a conversar, Yo respeto profesionalmente a todo miembro de este grupo,
pero ademas conozco del alto nivel tecnico de los miembros de Solid Quality
Learning.

una prueba más feaciente ¿Como cual?

Me referia a postear un codigo mas ilustrativo que se pueda ejecutar en dos
sesiones diferentes y que generen el interbloqueo, como este que a
continuación pego y que muestra que tienes toda la razón sobre la ocurrencia
del interbloqueo cuando dos sesiones trantan de insertar por primera vez un
nombre cuyos prefijos son iguales y no existen en la tabla de secuencias,
debido al bajo nivel de aislamiento que intente usar y el prechequeo de
existencia.

DECLARE @i INT
DECLARE @c CHAR(1)
DECLARE @Nombre VARCHAR(50)

SET @c = 'a'

WAITFOR TIME '19:31:00'

WHILE @c BETWEEN 'a' AND 'z'
BEGIN
SET @i = 1
WHILE @i < 500
BEGIN
SET @Nombre = REPLICATE(UPPER(@c), 3) + ' - ' + LTRIM(@i)
EXEC dbo.usp_InsertItem @Nombre
SET @i = @i + 1
END

SET @c = CHAR(ASCII(@c) + 1)
END
GO

DECLARE @i INT
DECLARE @c CHAR(1)
DECLARE @Nombre VARCHAR(50)

SET @c = 'a'

WAITFOR TIME '19:31:00'

WHILE @c BETWEEN 'a' AND 'z'
BEGIN
SET @i = 500
WHILE @i < 1000
BEGIN
SET @Nombre = REPLICATE(UPPER(@c), 3) + ' - ' + LTRIM(@i)
EXEC dbo.usp_InsertItem @Nombre
SET @i = @i + 1
END

SET @c = CHAR(ASCII(@c) + 1)
END
GO

Resultado en una de las sesiones:

Msg 1205, Level 13, State 48, Procedure usp_TomarUltimaSeq, Line 23
Transaction (Process ID 54) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.

Hasta aqui queda lo de el interbloqueo y reconozco que teneis toda la razón.

Se puede hacer algo mas para poder utilizar este esquema y procedimientos?

Si disminuimos el nivel de aislamiento, entonces damos paso, como bien
dijistes, a que ocurra un error por insertar clave duplicada, problema que
podemos manejar un poco si le agregamos al indice unico la opcion WITH
(IGNORE_DUP_KEY = ON), en cuyo caso SQL Server solo generara una advertencia.
Asi que hice los sgtes cambios:

- Subi el nivel de aislamiento al nivel por defecto (READ COMMITTED)
- En la transaccion inserto si el prefijo no existe, pero ademas actualizo
para todos los items, Por lo que el primero de cada serie es insertado pero a
su vez actualizado para tener el mismo orden de acceso y no IF NOT EXISTS ...
insert ... ELSE update ...

El codigo queda de la sgte forma:

USE tempdb
GO

DROP PROCEDURE dbo.usp_InsertItem, dbo.usp_TomarUltimaSeq
GO

DROP TABLE dbo.Item, dbo.prefijo_item_ultima_seq
GO

CREATE TABLE dbo.prefijo_item_ultima_seq (
prefijo_item_ultima_seqID INT NOT NULL IDENTITY(1, 1),
prefijo_item CHAR(3) NOT NULL,
ultima_seq INT NOT NULL DEFAULT(0) CHECK(ultima_seq >= 0),
CONSTRAINT PK_prefijo_item_ultima_seq PRIMARY KEY CLUSTERED
(prefijo_item_ultima_seqID),
CONSTRAINT UQ_prefijo_item_ultima_seq_prefijo_item UNIQUE (prefijo_item)
WITH (IGNORE_DUP_KEY = ON)
)
go

CREATE PROCEDURE dbo.usp_TomarUltimaSeq
@prefijo_item CHAR(3),
@seq INT OUTPUT
AS
DECLARE @error INT
DECLARE @rc INT

SET NOCOUNT ON
SET XACT_ABORT ON

BEGIN TRANSACTION

IF NOT EXISTS (
SELECT *
FROM dbo.prefijo_item_ultima_seq
WHERE prefijo_item = @prefijo_item
)
INSERT INTO dbo.prefijo_item_ultima_seq (prefijo_item)
VALUES (@prefijo_item)

SELECT @error = @@ERROR, @rc = @@ROWCOUNT

IF @error != 0 GOTO ErrorHandler

UPDATE
dbo.prefijo_item_ultima_seq
SET
@seq = ultima_seq = ultima_seq + 1
WHERE
prefijo_item = @prefijo_item

SELECT @error = @@ERROR, @rc = @@ROWCOUNT

IF @error != 0 OR @rc = 0 GOTO ErrorHandler

COMMIT TRANSACTION

RETURN @@ERROR

ErrorHandler:
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION

RETURN @error
GO

CREATE TABLE dbo.Item (
ItemID INT NOT NULL IDENTITY(1, 1),
prefijo_item CHAR(3) NOT NULL REFERENCES
dbo.prefijo_item_ultima_seq(prefijo_item),
seq INT NOT NULL CHECK(seq >= 0),
prefijo_seq AS ISNULL(prefijo_item + RIGHT('000' + LTRIM(seq), 3), ''),
Nombre VARCHAR(50) NOT NULL,
CONSTRAINT PK_Item PRIMARY KEY CLUSTERED (ItemID),
CONSTRAINT UQ_Item_prefijo_item_seq UNIQUE (prefijo_item, seq),
CONSTRAINT CK_Item_Nombre CHECK(Nombre LIKE prefijo_item + '%')
)
go

CREATE PROCEDURE dbo.usp_InsertItem
@Nombre VARCHAR(50)
AS
SET NOCOUNT ON
SET XACT_ABORT ON

DECLARE @prefijo CHAR(3)
DECLARE @seq INT

DECLARE @rv INT
DECLARE @rc INT
DECLARE @error INT

SET @prefijo = LEFT(@Nombre, 3)

BEGIN TRANSACTION

EXEC @rv = dbo.usp_TomarUltimaSeq @prefijo, @seq OUTPUT

SET @error = COALESCE(NULLIF(@rv, 0), @@ERROR)

IF @error != 0 OR @seq IS NULL GOTO ErrorHandler

INSERT INTO dbo.Item (prefijo_item, seq, Nombre)
VALUES (@prefijo, @seq, @Nombre)

SELECT @error = @@ERROR, @rc = @@ROWCOUNT

IF @error != 0 OR @rc = 0 GOTO ErrorHandler

COMMIT TRANSACTION

RETURN @@ERROR

ErrorHandler:
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION

RETURN COALESCE(NULLIF(@error, 0), -1)
GO

Ejecute los scripts, mencionados al principio de este mensaje, varias veces
y no tuve problemas. De vez en cuando una de las sesiones mostrava el mensaje
"Duplicate key was ignored.", probando que una de las sesiones trato de
insertar el prefijo nuevamente pero esta vez no hay error sino advertencia.

Este ha sido un hilo muy interesante


Saludos,
Alejandro Mesa


"Jesús López" wrote:

Alejandro,

una prueba más feaciente ¿Como cual?

Sí, sería estupendo tener una máquina con 16 procesadores y 50 máquinas
cliente y hacer un programa que constantemente vaya insertando. Pero aunque
tuviermos ese entorno no sería fácil hacer que los cicuenta a la vez
intenten insertar un registro con un prefijo que todavía no esté en la
tabla. La probabilidad de interbloqueo es bastante pequeña, así que es
difícil provocarlo.

¿Cinco segundos te parece mucho? Necesito tiempo para darle al botón de
ejecutar, cambiar a la otra ventana y darle al botón de ejecutar otra vez
:-). Si pudiera hacerlo en un milisegundo ...

Que la tabla no sea exáctamente la tuya es irrelevante completamente, para
el caso que tratamos es equivalente. ¿Por qué no lo pruebas con la tuya tu
mismo? Sólo tienes que meter el delay para que te dé tiempo a cambiar de
ventana.

No sólamente he dado un código que muestra el interbloqueo, también he dado
una explicación de cómo ocurre y por qué, no he visto que hayas rebatido la
discusión de por qué ocurre. Viendo el código se puede deducir perfectamente
que puede producir interbloqueos ya que se dan las circunstancias

1) Nivel de aislamiento serializable
2) Primero una lectura
3) Luego una inserción o actualización relacionada con la lectura.

Creo que yo ya he demostrado sobradamente que produce interbloqueos, ¿por
qué no intentas tú demostrar lo contrario? ¿Qué prueba feaciente puedes
proporcionar tú para demostrarlo?

Saludos:

Jesús López



"Alejandro Mesa" escribió en el
mensaje news:
> Jesús López,
>
> Cinco segundos es demasiado, ademas lo estas haciendo sobre la tabla que
> proveistes tu como solucion, no la que yo provei.
>
> Yo preferiria una prueba mas feaciente.
>
> Alejandro Mesa
>
>
> "Jesús López" wrote:
>
>> Yo tampoco estoy diciendo que la mía sea mejor que la tuya, sólo he
>> cuestionado la apreciación de que la tuya sea la mejor y he dado
>> argumentos.
>> Creo que es muy interesante que cuando hay más de una solución a un
>> problema
>> se discutan las ventajas e inconvenientes de cada una.
>>
>> Por otra parte la solución que planteas al problema de la posibilidad de
>> que
>> haya una violación de la clave primaria es válida, ya que tal violación
>> se
>> evita utilizando el nivel de aislamiento serializable. Sin embargo esto
>> plantea otro problema: se pueden producir interbloqueos.
>>
>> Sea la tabla :
>>
>> USE tempdb
>> GO
>>
>> CREATE TABLE Secuencias
>> (
>> Prefijo char(3),
>> Secuencial int,
>> PRIMARY KEY (Prefijo, Secuencial)
>> )
>>
>> Si ejecutamos este código en dos conexiones al mismo tiempo:
>>
>> USE Tempdb
>> GO
>>
>> BEGIN TRANSACTION
>> SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
>> IF NOT EXISTS (SELECT * FROM Secuencias WHERE Prefijo = 'XXX')
>> BEGIN
>> WAITFOR DELAY '00:00:05'
>> INSERT INTO Secuencias VALUES ('XXX', 0)
>> END
>> COMMIT
>>
>> Recibimos el error de interbloqueo:
>>
>> Msg 1205, Level 13, State 48, Line 7
>> Transaction (Process ID 55) was deadlocked on lock resources with another
>> process and has been chosen as the deadlock victim. Rerun the
>> transaction.
>>
>>
>> La explicación es que las dos conexiones pueden haber ejecutado SELECT *
>> FROM Secuencias WHERE Prefijo = 'XXX' antes que la otra haga el INSERT.
>> Al
>> haber ejecutado la sentencia las dos conexiones han obtenido un bloqueo
>> compartido de intervalo de claves que impide a la otra otra conexión
>> insertar un registro con el valor de prefijo 'XXX', produciéndose de esta
>> manera el interbloqueo.
>>
>> La secuencia sería esta:
>>
>> Conexión 1: SELECT * FROM Secuencias WHERE Prefijo = 'XXX' -> obtiene
>> bloqueo compartido en el intervalo de claves 'XXX' - 'XXX'
>> Conexión 2: SELECT * FROM Secuencias WHERE Prefijo = 'XXX' -> obtiene
>> bloqueo compartido en el intervalo de claves 'XXX' - 'XXX'
>> Conexión 1: Intenta insertar registro con Prefijo = 'XXX', pero no puede
>> ya
>> que para ello tendría que poner un bloqueo exclusivo sobre la clave 'XXX'
>> que es incompatible con el bloqueo de intervalo de claves que tiene la
>> conexión 1. Por tanto queda a la espera de que la conexión 1 libere el
>> bloqueo
>> Conexión 2: Intenta insertar registro con Prefijo = 'XXX', pero no puede
>> ya
>> que para ello tendría que poner un bloqueo exclusivo sobre la clave 'XXX'
>> que es incompatible con el bloqueo de intervalo de claves que tiene la
>> conexión 2. Por tanto queda a la espera de que la conexión 2 libere el
>> bloqueo
>>
>>
>> La conexión 1 está esperando a que la conexión 2 libere un bloqueo, y la
>> conexión 2 está esperando a que la conexión 1 libere un bloqueo. Ninguna
>> de
>> las dos puede continuar. SQL Server detecta esta condición conocida como
>> interbloqueo, elige una como víctima (la más barata), deshace la
>> transacción
>> de la víctima y le envía un error.
>>
>> Saludos:
>>
>>
>> Jesús López
>> www.solidq.com
>>
>>
>>
>>
>> "Alejandro Mesa" escribió en el
>> mensaje news:
>> > Jesús López,
>> >
>> >> La solución de Alejandro tiene un problema: presupone que todos los
>> >> prefijos
>> >> ya están añadidos a la tabla de prefijos. ¿Qué pása si intentas añadir
>> >> un
>> >> item cuyo prefijo no está en la tabla de prefijos? Sencillamente no
>> >> funciona. Una primera aproximación a ese problema sería que el
>> >> procedimiento
>> >> usp_TomarUltimaSeq insertara el prefijo en caso de no existir, pero
>> >> eso
>> >> plantea un nuevo problema ¿Qué pasa si dos conexiones intentan
>> >> insertar
>> >> un
>> >> nuevo item cuyo prefijo no está en la tabla de prefijos? pues que las
>> >> dos
>> >> conexiones intentarán insertar el mismo prefijo, violándose la clave
>> >> primaria.
>> >
>> > El primer problema que plateas tiene sentido y tiene solución. El
>> > segundo
>> > problema no es en realidad un problema, pues existen metodos para
>> > evitar
>> > esto.
>> >
>> > Con lo anterior no quiero decir que la solucion que sugei sea mejor que
>> > la
>> > tuya.
>> >
>> > USE tempdb
>> > GO
>> >
>> > CREATE TABLE dbo.prefijo_item_ultima_seq (
>> > prefijo_item_ultima_seqID INT NOT NULL IDENTITY(1, 1),
>> > prefijo_item CHAR(3) NOT NULL,
>> > ultima_seq INT NOT NULL DEFAULT(0) CHECK(ultima_seq >= 0),
>> > CONSTRAINT PK_prefijo_item_ultima_seq PRIMARY KEY CLUSTERED
>> > (prefijo_item_ultima_seqID),
>> > CONSTRAINT UQ_prefijo_item_ultima_seq_prefijo_item UNIQUE
>> > (prefijo_item)
>> > )
>> > go
>> >
>> > CREATE PROCEDURE dbo.usp_TomarUltimaSeq
>> > @prefijo_item CHAR(3),
>> > @seq INT OUTPUT
>> > AS
>> > DECLARE @error INT
>> > DECLARE @rc INT
>> >
>> > SET NOCOUNT ON
>> >
>> > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
>> > BEGIN TRANSACTION
>> >
>> > IF NOT EXISTS (
>> > SELECT *
>> > FROM dbo.prefijo_item_ultima_seq
>> > WHERE prefijo_item = @prefijo_item
>> > )
>> > BEGIN
>> > SET @seq = 1
>> >
>> > INSERT INTO prefijo_item_ultima_seq (
>> > prefijo_item,
>> > ultima_seq
>> > ) VALUES (@prefijo_item, 1)
>> >
>> > END
>> > ELSE
>> > UPDATE dbo.prefijo_item_ultima_seq
>> > SET @seq = ultima_seq = ultima_seq + 1
>> > WHERE prefijo_item = @prefijo_item
>> >
>> > SELECT @error = @@ERROR, @rc = @@ROWCOUNT
>> >
>> > IF @error != 0 OR @rc = 0 GOTO ErrorHandler
>> >
>> > COMMIT TRANSACTION
>> >
>> > RETURN @@ERROR
>> >
>> > ErrorHandler:
>> > IF @@TRANCOUNT > 0
>> > ROLLBACK TRANSACTION
>> >
>> > RETURN @error
>> > GO
>> >
>> > CREATE TABLE dbo.Item (
>> > ItemID INT NOT NULL IDENTITY(1, 1),
>> > prefijo_item CHAR(3) NOT NULL REFERENCES
>> > dbo.prefijo_item_ultima_seq(prefijo_item),
>> > seq INT NOT NULL CHECK(seq >= 0),
>> > prefijo_seq AS ISNULL(prefijo_item + RIGHT('000' + LTRIM(seq), 3), ''),
>> > Nombre VARCHAR(50) NOT NULL,
>> > CONSTRAINT PK_Item PRIMARY KEY CLUSTERED (ItemID),
>> > CONSTRAINT UQ_Item_prefijo_item_seq UNIQUE (prefijo_item, seq),
>> > CONSTRAINT CK_Item_Nombre CHECK(Nombre LIKE prefijo_item + '%')
>> > )
>> > go
>> >
>> > CREATE PROCEDURE dbo.usp_InsertItem
>> > @Nombre VARCHAR(50)
>> > AS
>> > SET NOCOUNT ON
>> >
>> > DECLARE @prefijo CHAR(3)
>> > DECLARE @seq INT
>> >
>> > DECLARE @rv INT
>> > DECLARE @rc INT
>> > DECLARE @error INT
>> >
>> > SET @prefijo = LEFT(@Nombre, 3)
>> >
>> > BEGIN TRANSACTION
>> >
>> > EXEC @rv = dbo.usp_TomarUltimaSeq @prefijo, @seq OUTPUT
>> >
>> > SET @error = COALESCE(NULLIF(@rv, 0), @@ERROR)
>> >
>> > IF @error != 0 OR @seq IS NULL GOTO ErrorHandler
>> >
>> > INSERT INTO dbo.Item (prefijo_item, seq, Nombre)
>> > VALUES (@prefijo, @seq, @Nombre)
>> >
>> > SELECT @error = @@ERROR, @rc = @@ROWCOUNT
>> >
>> > IF @error != 0 OR @rc = 0 GOTO ErrorHandler
>> >
>> > COMMIT TRANSACTION
>> >
>> > RETURN @@ERROR
>> >
>> > ErrorHandler:
>> > IF @@TRANCOUNT > 0
>> > ROLLBACK TRANSACTION
>> >
>> > RETURN COALESCE(NULLIF(@error, 0), -1)
>> > GO
>> >
>> > DECLARE @rv INT
>> >
>> > EXEC @rv = dbo.usp_InsertItem 'SQL Server 7.0'
>> > EXEC @rv = dbo.usp_InsertItem 'SQL Server 2000'
>> > EXEC @rv = dbo.usp_InsertItem 'SQL Server 2005'
>> > EXEC @rv = dbo.usp_InsertItem 'SQL Server 2008'
>> >
>> > EXEC @rv = dbo.usp_InsertItem 'MS Office 2003'
>> > EXEC @rv = dbo.usp_InsertItem 'MS Office 2007'
>> > GO

Jesús López,

Otra opcion que acabo de probar, es subiendo el nivel de aislamiento al
nivel por defecto y usar sp_getapplock. Funcionó sin problema alguno, pero en
vez de llamar a sp_releaseapplock para liberar el bloqueo, me apoyo en que
este sera liberado al hacer COMMIT o ROLLBACK de la transaccion. Tambien
prendo dentro del procedimiento la opcion SET XACT_ABORT para asegurar que no
se quede abierta una transaccion, en caso de error, y a su vez se libere el
bloqueo sobre el recurso. Cuando ejecute el procedimiento llamando a
sp_releaseapplock, obtuve un tiempo de 5133 milisegundos, mientras que la
ejecucion sin incluirlo resulta en 4133 milisegundos.

Aca pego el procedimiento como queda.

CREATE PROCEDURE dbo.usp_TomarUltimaSeq
@prefijo_item CHAR(3),
@seq INT OUTPUT
AS
DECLARE @error INT
DECLARE @rc INT

SET NOCOUNT ON
SET XACT_ABORT ON

BEGIN TRANSACTION
EXEC sp_getapplock @prefijo_item, 'Exclusive'

IF NOT EXISTS (
SELECT *
FROM dbo.prefijo_item_ultima_seq
WHERE prefijo_item = @prefijo_item
)
BEGIN
SET @seq = 1

INSERT INTO prefijo_item_ultima_seq (
prefijo_item,
ultima_seq
) VALUES (@prefijo_item, 1)

END
ELSE
UPDATE dbo.prefijo_item_ultima_seq
SET @seq = ultima_seq = ultima_seq + 1
WHERE prefijo_item = @prefijo_item

SELECT @error = @@ERROR, @rc = @@ROWCOUNT

IF @error != 0 OR @rc = 0 GOTO ErrorHandler

COMMIT TRANSACTION

RETURN @@ERROR

ErrorHandler:
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION

RETURN @error
GO

Este es el script que use para medir tiempos.

DECLARE @i INT
DECLARE @c CHAR(1)
DECLARE @Nombre VARCHAR(50)
DECLARE @d DATETIME

SET @c = 'a'
SET @d = GETDATE()

WHILE @c BETWEEN 'a' AND 'z'
BEGIN
SET @i = 1
WHILE @i < 1000
BEGIN
SET @Nombre = REPLICATE(UPPER(@c), 3) + ' - ' + LTRIM(@i)
EXEC dbo.usp_InsertItem @Nombre
SET @i = @i + 1
END

SET @c = CHAR(ASCII(@c) + 1)
END

SELECT DATEDIFF(millisecond, @d, GETDATE())
GO


AMB


"Alejandro Mesa" wrote:

Jesús López,

Vamos a conversar, Yo respeto profesionalmente a todo miembro de este grupo,
pero ademas conozco del alto nivel tecnico de los miembros de Solid Quality
Learning.

> una prueba más feaciente ¿Como cual?

Me referia a postear un codigo mas ilustrativo que se pueda ejecutar en dos
sesiones diferentes y que generen el interbloqueo, como este que a
continuación pego y que muestra que tienes toda la razón sobre la ocurrencia
del interbloqueo cuando dos sesiones trantan de insertar por primera vez un
nombre cuyos prefijos son iguales y no existen en la tabla de secuencias,
debido al bajo nivel de aislamiento que intente usar y el prechequeo de
existencia.

DECLARE @i INT
DECLARE @c CHAR(1)
DECLARE @Nombre VARCHAR(50)

SET @c = 'a'

WAITFOR TIME '19:31:00'

WHILE @c BETWEEN 'a' AND 'z'
BEGIN
SET @i = 1
WHILE @i < 500
BEGIN
SET @Nombre = REPLICATE(UPPER(@c), 3) + ' - ' + LTRIM(@i)
EXEC dbo.usp_InsertItem @Nombre
SET @i = @i + 1
END

SET @c = CHAR(ASCII(@c) + 1)
END
GO

DECLARE @i INT
DECLARE @c CHAR(1)
DECLARE @Nombre VARCHAR(50)

SET @c = 'a'

WAITFOR TIME '19:31:00'

WHILE @c BETWEEN 'a' AND 'z'
BEGIN
SET @i = 500
WHILE @i < 1000
BEGIN
SET @Nombre = REPLICATE(UPPER(@c), 3) + ' - ' + LTRIM(@i)
EXEC dbo.usp_InsertItem @Nombre
SET @i = @i + 1
END

SET @c = CHAR(ASCII(@c) + 1)
END
GO

Resultado en una de las sesiones:

Msg 1205, Level 13, State 48, Procedure usp_TomarUltimaSeq, Line 23
Transaction (Process ID 54) was deadlocked on lock resources with another
process and has been chosen as the deadlock victim. Rerun the transaction.

Hasta aqui queda lo de el interbloqueo y reconozco que teneis toda la razón.

Se puede hacer algo mas para poder utilizar este esquema y procedimientos?

Si disminuimos el nivel de aislamiento, entonces damos paso, como bien
dijistes, a que ocurra un error por insertar clave duplicada, problema que
podemos manejar un poco si le agregamos al indice unico la opcion WITH
(IGNORE_DUP_KEY = ON), en cuyo caso SQL Server solo generara una advertencia.
Asi que hice los sgtes cambios:

- Subi el nivel de aislamiento al nivel por defecto (READ COMMITTED)
- En la transaccion inserto si el prefijo no existe, pero ademas actualizo
para todos los items, Por lo que el primero de cada serie es insertado pero a
su vez actualizado para tener el mismo orden de acceso y no IF NOT EXISTS ...
insert ... ELSE update ...

El codigo queda de la sgte forma:

USE tempdb
GO

DROP PROCEDURE dbo.usp_InsertItem, dbo.usp_TomarUltimaSeq
GO

DROP TABLE dbo.Item, dbo.prefijo_item_ultima_seq
GO

CREATE TABLE dbo.prefijo_item_ultima_seq (
prefijo_item_ultima_seqID INT NOT NULL IDENTITY(1, 1),
prefijo_item CHAR(3) NOT NULL,
ultima_seq INT NOT NULL DEFAULT(0) CHECK(ultima_seq >= 0),
CONSTRAINT PK_prefijo_item_ultima_seq PRIMARY KEY CLUSTERED
(prefijo_item_ultima_seqID),
CONSTRAINT UQ_prefijo_item_ultima_seq_prefijo_item UNIQUE (prefijo_item)
WITH (IGNORE_DUP_KEY = ON)
)
go

CREATE PROCEDURE dbo.usp_TomarUltimaSeq
@prefijo_item CHAR(3),
@seq INT OUTPUT
AS
DECLARE @error INT
DECLARE @rc INT

SET NOCOUNT ON
SET XACT_ABORT ON

BEGIN TRANSACTION

IF NOT EXISTS (
SELECT *
FROM dbo.prefijo_item_ultima_seq
WHERE prefijo_item = @prefijo_item
)
INSERT INTO dbo.prefijo_item_ultima_seq (prefijo_item)
VALUES (@prefijo_item)

SELECT @error = @@ERROR, @rc = @@ROWCOUNT

IF @error != 0 GOTO ErrorHandler

UPDATE
dbo.prefijo_item_ultima_seq
SET
@seq = ultima_seq = ultima_seq + 1
WHERE
prefijo_item = @prefijo_item

SELECT @error = @@ERROR, @rc = @@ROWCOUNT

IF @error != 0 OR @rc = 0 GOTO ErrorHandler

COMMIT TRANSACTION

RETURN @@ERROR

ErrorHandler:
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION

RETURN @error
GO

CREATE TABLE dbo.Item (
ItemID INT NOT NULL IDENTITY(1, 1),
prefijo_item CHAR(3) NOT NULL REFERENCES
dbo.prefijo_item_ultima_seq(prefijo_item),
seq INT NOT NULL CHECK(seq >= 0),
prefijo_seq AS ISNULL(prefijo_item + RIGHT('000' + LTRIM(seq), 3), ''),
Nombre VARCHAR(50) NOT NULL,
CONSTRAINT PK_Item PRIMARY KEY CLUSTERED (ItemID),
CONSTRAINT UQ_Item_prefijo_item_seq UNIQUE (prefijo_item, seq),
CONSTRAINT CK_Item_Nombre CHECK(Nombre LIKE prefijo_item + '%')
)
go

CREATE PROCEDURE dbo.usp_InsertItem
@Nombre VARCHAR(50)
AS
SET NOCOUNT ON
SET XACT_ABORT ON

DECLARE @prefijo CHAR(3)
DECLARE @seq INT

DECLARE @rv INT
DECLARE @rc INT
DECLARE @error INT

SET @prefijo = LEFT(@Nombre, 3)

BEGIN TRANSACTION

EXEC @rv = dbo.usp_TomarUltimaSeq @prefijo, @seq OUTPUT

SET @error = COALESCE(NULLIF(@rv, 0), @@ERROR)

IF @error != 0 OR @seq IS NULL GOTO ErrorHandler

INSERT INTO dbo.Item (prefijo_item, seq, Nombre)
VALUES (@prefijo, @seq, @Nombre)

SELECT @error = @@ERROR, @rc = @@ROWCOUNT

IF @error != 0 OR @rc = 0 GOTO ErrorHandler

COMMIT TRANSACTION

RETURN @@ERROR

ErrorHandler:
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION

RETURN COALESCE(NULLIF(@error, 0), -1)
GO

Ejecute los scripts, mencionados al principio de este mensaje, varias veces
y no tuve problemas. De vez en cuando una de las sesiones mostrava el mensaje
"Duplicate key was ignored.", probando que una de las sesiones trato de
insertar el prefijo nuevamente pero esta vez no hay error sino advertencia.

Este ha sido un hilo muy interesante


Saludos,
Alejandro Mesa


"Jesús López" wrote:

> Alejandro,
>
> una prueba más feaciente ¿Como cual?
>
> Sí, sería estupendo tener una máquina con 16 procesadores y 50 máquinas
> cliente y hacer un programa que constantemente vaya insertando. Pero aunque
> tuviermos ese entorno no sería fácil hacer que los cicuenta a la vez
> intenten insertar un registro con un prefijo que todavía no esté en la
> tabla. La probabilidad de interbloqueo es bastante pequeña, así que es
> difícil provocarlo.
>
> ¿Cinco segundos te parece mucho? Necesito tiempo para darle al botón de
> ejecutar, cambiar a la otra ventana y darle al botón de ejecutar otra vez
> :-). Si pudiera hacerlo en un milisegundo ...
>
> Que la tabla no sea exáctamente la tuya es irrelevante completamente, para
> el caso que tratamos es equivalente. ¿Por qué no lo pruebas con la tuya tu
> mismo? Sólo tienes que meter el delay para que te dé tiempo a cambiar de
> ventana.
>
> No sólamente he dado un código que muestra el interbloqueo, también he dado
> una explicación de cómo ocurre y por qué, no he visto que hayas rebatido la
> discusión de por qué ocurre. Viendo el código se puede deducir perfectamente
> que puede producir interbloqueos ya que se dan las circunstancias
>
> 1) Nivel de aislamiento serializable
> 2) Primero una lectura
> 3) Luego una inserción o actualización relacionada con la lectura.
>
> Creo que yo ya he demostrado sobradamente que produce interbloqueos, ¿por
> qué no intentas tú demostrar lo contrario? ¿Qué prueba feaciente puedes
> proporcionar tú para demostrarlo?
>
> Saludos:
>
> Jesús López
>
>
>
> "Alejandro Mesa" escribió en el
> mensaje news:
> > Jesús López,
> >
> > Cinco segundos es demasiado, ademas lo estas haciendo sobre la tabla que
> > proveistes tu como solucion, no la que yo provei.
> >
> > Yo preferiria una prueba mas feaciente.
> >
> > Alejandro Mesa
> >
> >
> > "Jesús López" wrote:
> >
> >> Yo tampoco estoy diciendo que la mía sea mejor que la tuya, sólo he
> >> cuestionado la apreciación de que la tuya sea la mejor y he dado
> >> argumentos.
> >> Creo que es muy interesante que cuando hay más de una solución a un
> >> problema
> >> se discutan las ventajas e inconvenientes de cada una.
> >>
> >> Por otra parte la solución que planteas al problema de la posibilidad de
> >> que
> >> haya una violación de la clave primaria es válida, ya que tal violación
> >> se
> >> evita utilizando el nivel de aislamiento serializable. Sin embargo esto
> >> plantea otro problema: se pueden producir interbloqueos.
> >>
> >> Sea la tabla :
> >>
> >> USE tempdb
> >> GO
> >>
> >> CREATE TABLE Secuencias
> >> (
> >> Prefijo char(3),
> >> Secuencial int,

Jesús López,

Esta es la ultima prueba que hago por hoy.

Deje el nivel de aislamiento en SERALIZABLE, pero restructure el codigo y
siempre actualizo y si el # de filas actualizadas es cero (0), entonces
inserto. No tuve interbloqueos y el tiempo que tomo usando el script para
medir tiempo fue de 2903.
Aca esta el codigo.

CREATE PROCEDURE dbo.usp_TomarUltimaSeq
@prefijo_item CHAR(3),
@seq INT OUTPUT
AS
DECLARE @error INT
DECLARE @rc INT

SET NOCOUNT ON
SET XACT_ABORT ON

BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

UPDATE dbo.prefijo_item_ultima_seq
SET @seq = ultima_seq = ultima_seq + 1
WHERE prefijo_item = @prefijo_item

SELECT @error = @@ERROR, @rc = @@ROWCOUNT

IF @error != 0 GOTO ErrorHandler

IF @rc = 0
BEGIN
SET @seq = 1

INSERT INTO prefijo_item_ultima_seq (prefijo_item, ultima_seq)
VALUES (@prefijo_item, 1)

SELECT @error = @@ERROR, @rc = @@ROWCOUNT

IF @error != 0 OR @rc = 0 GOTO ErrorHandler
END

COMMIT TRANSACTION

RETURN @@ERROR

ErrorHandler:
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION

RETURN @error
GO


AMB

"Alejandro Mesa" wrote:

Jesús López,

Otra opcion que acabo de probar, es subiendo el nivel de aislamiento al
nivel por defecto y usar sp_getapplock. Funcionó sin problema alguno, pero en
vez de llamar a sp_releaseapplock para liberar el bloqueo, me apoyo en que
este sera liberado al hacer COMMIT o ROLLBACK de la transaccion. Tambien
prendo dentro del procedimiento la opcion SET XACT_ABORT para asegurar que no
se quede abierta una transaccion, en caso de error, y a su vez se libere el
bloqueo sobre el recurso. Cuando ejecute el procedimiento llamando a
sp_releaseapplock, obtuve un tiempo de 5133 milisegundos, mientras que la
ejecucion sin incluirlo resulta en 4133 milisegundos.

Aca pego el procedimiento como queda.

CREATE PROCEDURE dbo.usp_TomarUltimaSeq
@prefijo_item CHAR(3),
@seq INT OUTPUT
AS
DECLARE @error INT
DECLARE @rc INT

SET NOCOUNT ON
SET XACT_ABORT ON

BEGIN TRANSACTION
EXEC sp_getapplock @prefijo_item, 'Exclusive'

IF NOT EXISTS (
SELECT *
FROM dbo.prefijo_item_ultima_seq
WHERE prefijo_item = @prefijo_item
)
BEGIN
SET @seq = 1

INSERT INTO prefijo_item_ultima_seq (
prefijo_item,
ultima_seq
) VALUES (@prefijo_item, 1)

END
ELSE
UPDATE dbo.prefijo_item_ultima_seq
SET @seq = ultima_seq = ultima_seq + 1
WHERE prefijo_item = @prefijo_item

SELECT @error = @@ERROR, @rc = @@ROWCOUNT

IF @error != 0 OR @rc = 0 GOTO ErrorHandler

COMMIT TRANSACTION

RETURN @@ERROR

ErrorHandler:
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION

RETURN @error
GO

Este es el script que use para medir tiempos.

DECLARE @i INT
DECLARE @c CHAR(1)
DECLARE @Nombre VARCHAR(50)
DECLARE @d DATETIME

SET @c = 'a'
SET @d = GETDATE()

WHILE @c BETWEEN 'a' AND 'z'
BEGIN
SET @i = 1
WHILE @i < 1000
BEGIN
SET @Nombre = REPLICATE(UPPER(@c), 3) + ' - ' + LTRIM(@i)
EXEC dbo.usp_InsertItem @Nombre
SET @i = @i + 1
END

SET @c = CHAR(ASCII(@c) + 1)
END

SELECT DATEDIFF(millisecond, @d, GETDATE())
GO


AMB


"Alejandro Mesa" wrote:

> Jesús López,
>
> Vamos a conversar, Yo respeto profesionalmente a todo miembro de este grupo,
> pero ademas conozco del alto nivel tecnico de los miembros de Solid Quality
> Learning.
>
> > una prueba más feaciente ¿Como cual?
>
> Me referia a postear un codigo mas ilustrativo que se pueda ejecutar en dos
> sesiones diferentes y que generen el interbloqueo, como este que a
> continuación pego y que muestra que tienes toda la razón sobre la ocurrencia
> del interbloqueo cuando dos sesiones trantan de insertar por primera vez un
> nombre cuyos prefijos son iguales y no existen en la tabla de secuencias,
> debido al bajo nivel de aislamiento que intente usar y el prechequeo de
> existencia.
>
> DECLARE @i INT
> DECLARE @c CHAR(1)
> DECLARE @Nombre VARCHAR(50)
>
> SET @c = 'a'
>
> WAITFOR TIME '19:31:00'
>
> WHILE @c BETWEEN 'a' AND 'z'
> BEGIN
> SET @i = 1
> WHILE @i < 500
> BEGIN
> SET @Nombre = REPLICATE(UPPER(@c), 3) + ' - ' + LTRIM(@i)
> EXEC dbo.usp_InsertItem @Nombre
> SET @i = @i + 1
> END
>
> SET @c = CHAR(ASCII(@c) + 1)
> END
> GO
>
> DECLARE @i INT
> DECLARE @c CHAR(1)
> DECLARE @Nombre VARCHAR(50)
>
> SET @c = 'a'
>
> WAITFOR TIME '19:31:00'
>
> WHILE @c BETWEEN 'a' AND 'z'
> BEGIN
> SET @i = 500
> WHILE @i < 1000
> BEGIN
> SET @Nombre = REPLICATE(UPPER(@c), 3) + ' - ' + LTRIM(@i)
> EXEC dbo.usp_InsertItem @Nombre
> SET @i = @i + 1
> END
>
> SET @c = CHAR(ASCII(@c) + 1)
> END
> GO
>
> Resultado en una de las sesiones:
>
> Msg 1205, Level 13, State 48, Procedure usp_TomarUltimaSeq, Line 23
> Transaction (Process ID 54) was deadlocked on lock resources with another
> process and has been chosen as the deadlock victim. Rerun the transaction.
>
> Hasta aqui queda lo de el interbloqueo y reconozco que teneis toda la razón.
>
> Se puede hacer algo mas para poder utilizar este esquema y procedimientos?
>
> Si disminuimos el nivel de aislamiento, entonces damos paso, como bien
> dijistes, a que ocurra un error por insertar clave duplicada, problema que
> podemos manejar un poco si le agregamos al indice unico la opcion WITH
> (IGNORE_DUP_KEY = ON), en cuyo caso SQL Server solo generara una advertencia.
> Asi que hice los sgtes cambios:
>
> - Subi el nivel de aislamiento al nivel por defecto (READ COMMITTED)
> - En la transaccion inserto si el prefijo no existe, pero ademas actualizo
> para todos los items, Por lo que el primero de cada serie es insertado pero a
> su vez actualizado para tener el mismo orden de acceso y no IF NOT EXISTS ...
> insert ... ELSE update ...
>
> El codigo queda de la sgte forma:
>
> USE tempdb
> GO
>
> DROP PROCEDURE dbo.usp_InsertItem, dbo.usp_TomarUltimaSeq
> GO
>
> DROP TABLE dbo.Item, dbo.prefijo_item_ultima_seq
> GO
>
> CREATE TABLE dbo.prefijo_item_ultima_seq (
> prefijo_item_ultima_seqID INT NOT NULL IDENTITY(1, 1),
> prefijo_item CHAR(3) NOT NULL,
> ultima_seq INT NOT NULL DEFAULT(0) CHECK(ultima_seq >= 0),
> CONSTRAINT PK_prefijo_item_ultima_seq PRIMARY KEY CLUSTERED
> (prefijo_item_ultima_seqID),
> CONSTRAINT UQ_prefijo_item_ultima_seq_prefijo_item UNIQUE (prefijo_item)
> WITH (IGNORE_DUP_KEY = ON)
> )
> go
>
> CREATE PROCEDURE dbo.usp_TomarUltimaSeq
> @prefijo_item CHAR(3),
> @seq INT OUTPUT
> AS
> DECLARE @error INT
> DECLARE @rc INT
>
> SET NOCOUNT ON
> SET XACT_ABORT ON
>
> BEGIN TRANSACTION
>
> IF NOT EXISTS (
> SELECT *
> FROM dbo.prefijo_item_ultima_seq
> WHERE prefijo_item = @prefijo_item
> )
> INSERT INTO dbo.prefijo_item_ultima_seq (prefijo_item)
> VALUES (@prefijo_item)
>
> SELECT @error = @@ERROR, @rc = @@ROWCOUNT
>
> IF @error != 0 GOTO ErrorHandler
>
> UPDATE
> dbo.prefijo_item_ultima_seq
> SET
> @seq = ultima_seq = ultima_seq + 1
> WHERE
> prefijo_item = @prefijo_item
>
> SELECT @error = @@ERROR, @rc = @@ROWCOUNT
>
> IF @error != 0 OR @rc = 0 GOTO ErrorHandler
>
> COMMIT TRANSACTION
>
> RETURN @@ERROR
>
> ErrorHandler:
> IF @@TRANCOUNT > 0
> ROLLBACK TRANSACTION
>
> RETURN @error
> GO
>
> CREATE TABLE dbo.Item (
> ItemID INT NOT NULL IDENTITY(1, 1),
> prefijo_item CHAR(3) NOT NULL REFERENCES
> dbo.prefijo_item_ultima_seq(prefijo_item),
> seq INT NOT NULL CHECK(seq >= 0),
> prefijo_seq AS ISNULL(prefijo_item + RIGHT('000' + LTRIM(seq), 3), ''),
> Nombre VARCHAR(50) NOT NULL,
> CONSTRAINT PK_Item PRIMARY KEY CLUSTERED (ItemID),
> CONSTRAINT UQ_Item_prefijo_item_seq UNIQUE (prefijo_item, seq),
> CONSTRAINT CK_Item_Nombre CHECK(Nombre LIKE prefijo_item + '%')
> )
> go
>
> CREATE PROCEDURE dbo.usp_InsertItem
> @Nombre VARCHAR(50)
> AS
> SET NOCOUNT ON
> SET XACT_ABORT ON
>
> DECLARE @prefijo CHAR(3)
> DECLARE @seq INT
>
> DECLARE @rv INT
> DECLARE @rc INT
> DECLARE @error INT
>
> SET @prefijo = LEFT(@Nombre, 3)
>
> BEGIN TRANSACTION
>
> EXEC @rv = dbo.usp_TomarUltimaSeq @prefijo, @seq OUTPUT
>
> SET @error = COALESCE(NULLIF(@rv, 0), @@ERROR)
>
> IF @error != 0 OR @seq IS NULL GOTO ErrorHandler
>
> INSERT INTO dbo.Item (prefijo_item, seq, Nombre)
> VALUES (@prefijo, @seq, @Nombre)
>
> SELECT @error = @@ERROR, @rc = @@ROWCOUNT
>
> IF @error != 0 OR @rc = 0 GOTO ErrorHandler
>
> COMMIT TRANSACTION
>
> RETURN @@ERROR
>
> ErrorHandler:
> IF @@TRANCOUNT > 0
> ROLLBACK TRANSACTION
>