Pregunta sobre diseño de PK

Eso ultimo que me explicas no equivaldria a haber puesto ese lock hint en
el "select where not exists()" de la version original del procedimiento? o
incluso no seria lo mismo que haber puesto el mismo "set ...serializable"
en aquella version del proc. ? Al menos leyendo la ayuda uno puede deducir
eso.

Técnica A:

if not exists( select * )
insert ...
else
update ...

(1) con un nivel de aislamiento serializable tienes interbloqueos. Tanto
si pones SET TRANSACTION ISOLATION LEVEL SERIALIZABLE como si usas un lock
hint en el select if not exists( select * form tabla with (serializable)
(2) con un nivel de aislamiento distinto de serializable tienes
violaciones de clave


Técnica B:

update

if @@rowcount = 0
insert ...

(1) con un nivel de aislamiento serializable no tienes problemas, eso lo
puedes hacer de dos maneras, con SET TRANSACTION ISOLATION LEVEL
SERIALIZABLE o poniendo el lock hint el el update que es la única parte que
lo requiere en este caso.

(2) con un nivel de aislamiento distinto de serializable tienes violaciones
de clave


Técnica C:

Otra técnica presentada consiste en tratar las violaciones de clave, primero
hacemos que la restricción de unicidad ignore los duplicados, de esta
manera, en vez de tener errores en la violación de clave, tenemos sólo
advertencias:

if not exists (select * from ... )
begin
insert
end
update .


Técnica D:

Y la última técnica consiste en usar sp_getapplock para gestionar la
concurrencia.


Conclusión:

Yo descartaría la técnica A por los problemas que produce. A mi entender la
mejor, por eficiencia y simplicidad, es la técnica B usando un lock hint en
el update. Las técnicas C y D también son válidas, pero no son tan
eficientes como la B.

Tambien, por qué si ese tipo de actualizaciones multitablas (ejemplo los
casos de header -detail) son tan normales en las transacciones, por que
segun la ayuda el tipo de isolation level por defecto NO es SERIALIZABLE
sino READ COMMITTED ?

El nivel de aislamiento serializable produce muchos bloqueos, disminuyendo
la simultaneidad y consumiendo más recursos y en la mayoría de los casos con
un nivel de aislamiento read committed es suficiente. Observa que la
solución que a mi más me gusta (técnica C) toda la transacción se ejecuta en
su mayoría en un nivel de aislamiento read committed, sólamente la
instrucción update de la secuencia es necesario usar el nivel de aislamiento
serializable. Por otra parte la técnica C se ejecuta perfectamente sin
problemas en el nivel de aislamiento read committed.


Saludos:

Jesús López
www.solidq.com



"Luis Flores" escribió en el mensaje
news:

Es necesario si quieres evitar la posibilidad de violación de clave. Pero
en realidad ese nivel de aislamiento sólo se necesita para la instrucción
UPDATE, así que se podría cambiar el nivel de aislamiento después de la
ejecución de la instrucción UPDATE, o simplemente no ejecutar en ningún
momento SET TRANSACTION ISOLATION LEVEL SERIALIZABLE y su lugar utilizar
un lock hint en la tabla:

Otra preguntita porque al final me confundi un poco para aclarar los
conceptos:

Eso ultimo que me explicas no equivaldria a haber puesto ese lock hint en
el "select where not exists()" de la version original del procedimiento? o
incluso no seria lo mismo que haber puesto el mismo "set ...serializable"
en aquella version del proc. ? Al menos leyendo la ayuda uno puede deducir
eso.
En este caso no hablo de la eficiciencia sino para evitar el error de
duplicidad en la concurrencia y el interlock que ustedes citaban.

Tambien, por qué si ese tipo de actualizaciones multitablas (ejemplo los
casos de header -detail) son tan normales en las transacciones, por que
segun la ayuda el tipo de isolation level por defecto NO es SERIALIZABLE
sino READ COMMITTED ?
No es muy propenso a ese tipo de problemas el Read Commited ?


Luis

Jesús López,

Debido a los test que has hecho al respecto y el manejo que tienes sobre
este tema, te exhorto a que escribas un articulo sobre el mismo. Me parece
que la opcion de usar sp_getapplock no es mala, si eliminamos el uso de
sp_releaseapplock y usamos SET XACT_ABORT y manejo de errorres para asegurar
que la transaccion no se quede abierta y por ende el lock sobre el recurso
persista por mas tiempo de el necesario.

Que crees?


AMB


"Jesús López" wrote:

> Eso ultimo que me explicas no equivaldria a haber puesto ese lock hint en
> el "select where not exists()" de la version original del procedimiento? o
> incluso no seria lo mismo que haber puesto el mismo "set ...serializable"
> en aquella version del proc. ? Al menos leyendo la ayuda uno puede deducir
> eso.


Técnica A:

if not exists( select * )
insert ...
else
update ...

(1) con un nivel de aislamiento serializable tienes interbloqueos. Tanto
si pones SET TRANSACTION ISOLATION LEVEL SERIALIZABLE como si usas un lock
hint en el select if not exists( select * form tabla with (serializable)
(2) con un nivel de aislamiento distinto de serializable tienes
violaciones de clave


Técnica B:

update

if @@rowcount = 0
insert ...

(1) con un nivel de aislamiento serializable no tienes problemas, eso lo
puedes hacer de dos maneras, con SET TRANSACTION ISOLATION LEVEL
SERIALIZABLE o poniendo el lock hint el el update que es la única parte que
lo requiere en este caso.

(2) con un nivel de aislamiento distinto de serializable tienes violaciones
de clave


Técnica C:

Otra técnica presentada consiste en tratar las violaciones de clave, primero
hacemos que la restricción de unicidad ignore los duplicados, de esta
manera, en vez de tener errores en la violación de clave, tenemos sólo
advertencias:

if not exists (select * from ... )
begin
insert
end
update .


Técnica D:

Y la última técnica consiste en usar sp_getapplock para gestionar la
concurrencia.


Conclusión:

Yo descartaría la técnica A por los problemas que produce. A mi entender la
mejor, por eficiencia y simplicidad, es la técnica B usando un lock hint en
el update. Las técnicas C y D también son válidas, pero no son tan
eficientes como la B.


> Tambien, por qué si ese tipo de actualizaciones multitablas (ejemplo los
> casos de header -detail) son tan normales en las transacciones, por que
> segun la ayuda el tipo de isolation level por defecto NO es SERIALIZABLE
> sino READ COMMITTED ?

El nivel de aislamiento serializable produce muchos bloqueos, disminuyendo
la simultaneidad y consumiendo más recursos y en la mayoría de los casos con
un nivel de aislamiento read committed es suficiente. Observa que la
solución que a mi más me gusta (técnica C) toda la transacción se ejecuta en
su mayoría en un nivel de aislamiento read committed, sólamente la
instrucción update de la secuencia es necesario usar el nivel de aislamiento
serializable. Por otra parte la técnica C se ejecuta perfectamente sin
problemas en el nivel de aislamiento read committed.


Saludos:

Jesús López
www.solidq.com



"Luis Flores" escribió en el mensaje
news:
>
>>
>> Es necesario si quieres evitar la posibilidad de violación de clave. Pero
>> en realidad ese nivel de aislamiento sólo se necesita para la instrucción
>> UPDATE, así que se podría cambiar el nivel de aislamiento después de la
>> ejecución de la instrucción UPDATE, o simplemente no ejecutar en ningún
>> momento SET TRANSACTION ISOLATION LEVEL SERIALIZABLE y su lugar utilizar
>> un lock hint en la tabla:
>>
>
> Otra preguntita porque al final me confundi un poco para aclarar los
> conceptos:
>
> Eso ultimo que me explicas no equivaldria a haber puesto ese lock hint en
> el "select where not exists()" de la version original del procedimiento? o
> incluso no seria lo mismo que haber puesto el mismo "set ...serializable"
> en aquella version del proc. ? Al menos leyendo la ayuda uno puede deducir
> eso.
> En este caso no hablo de la eficiciencia sino para evitar el error de
> duplicidad en la concurrencia y el interlock que ustedes citaban.
>
> Tambien, por qué si ese tipo de actualizaciones multitablas (ejemplo los
> casos de header -detail) son tan normales en las transacciones, por que
> segun la ayuda el tipo de isolation level por defecto NO es SERIALIZABLE
> sino READ COMMITTED ?
> No es muy propenso a ese tipo de problemas el Read Commited ?
>
>
> Luis
>

Debido a los test que has hecho al respecto y el manejo que tienes sobre
este tema, te exhorto a que escribas un articulo sobre el mismo.

Hace unos días que llevamos trabajando sobre un arttículo que hemos mandado
ayer mismo a dotnetmania para ver si lo publican, donde presentamos
distintas técnicas para la generación de secuencias, son 16 páginas :-) así
que probablemente lo tendremos que reducir o partirlo en dos.

Me parece
que la opcion de usar sp_getapplock no es mala

No, desde luego que no.

si eliminamos el uso de
sp_releaseapplock y usamos SET XACT_ABORT y manejo de errorres para
asegurar
que la transaccion no se quede abierta y por ende el lock sobre el recurso
persista por mas tiempo de el necesario.

Que crees?

Que así es como habría que hacerlo antes de ponerlo en producción :-)

Sin embargo creo que es una buena práctica llamar a sp_releaseapplock para
liberar el bloqueo lo antes posible. La generación del secuencial puede ser
parte de una transacción más grande donde podríamos liberar el bloqueo antes
de que termine la transacción gorda.

AMB


"Jesús López" wrote:

> Eso ultimo que me explicas no equivaldria a haber puesto ese lock hint
> en
> el "select where not exists()" de la version original del
> procedimiento? o
> incluso no seria lo mismo que haber puesto el mismo "set
> ...serializable"
> en aquella version del proc. ? Al menos leyendo la ayuda uno puede
> deducir
> eso.


Técnica A:

if not exists( select * )
insert ...
else
update ...

(1) con un nivel de aislamiento serializable tienes interbloqueos.
Tanto
si pones SET TRANSACTION ISOLATION LEVEL SERIALIZABLE como si usas un
lock
hint en el select if not exists( select * form tabla with (serializable)
(2) con un nivel de aislamiento distinto de serializable tienes
violaciones de clave


Técnica B:

update

if @@rowcount = 0
insert ...

(1) con un nivel de aislamiento serializable no tienes problemas, eso lo
puedes hacer de dos maneras, con SET TRANSACTION ISOLATION LEVEL
SERIALIZABLE o poniendo el lock hint el el update que es la única parte
que
lo requiere en este caso.

(2) con un nivel de aislamiento distinto de serializable tienes
violaciones
de clave


Técnica C:

Otra técnica presentada consiste en tratar las violaciones de clave,
primero
hacemos que la restricción de unicidad ignore los duplicados, de esta
manera, en vez de tener errores en la violación de clave, tenemos sólo
advertencias:

if not exists (select * from ... )
begin
insert
end
update .


Técnica D:

Y la última técnica consiste en usar sp_getapplock para gestionar la
concurrencia.


Conclusión:

Yo descartaría la técnica A por los problemas que produce. A mi entender
la
mejor, por eficiencia y simplicidad, es la técnica B usando un lock hint
en
el update. Las técnicas C y D también son válidas, pero no son tan
eficientes como la B.


> Tambien, por qué si ese tipo de actualizaciones multitablas (ejemplo
> los
> casos de header -detail) son tan normales en las transacciones, por que
> segun la ayuda el tipo de isolation level por defecto NO es
> SERIALIZABLE
> sino READ COMMITTED ?

El nivel de aislamiento serializable produce muchos bloqueos,
disminuyendo
la simultaneidad y consumiendo más recursos y en la mayoría de los casos
con
un nivel de aislamiento read committed es suficiente. Observa que la
solución que a mi más me gusta (técnica C) toda la transacción se ejecuta
en
su mayoría en un nivel de aislamiento read committed, sólamente la
instrucción update de la secuencia es necesario usar el nivel de
aislamiento
serializable. Por otra parte la técnica C se ejecuta perfectamente sin
problemas en el nivel de aislamiento read committed.


Saludos:

Jesús López
www.solidq.com



"Luis Flores" escribió en el mensaje
news:
>
>>
>> Es necesario si quieres evitar la posibilidad de violación de clave.
>> Pero
>> en realidad ese nivel de aislamiento sólo se necesita para la
>> instrucción
>> UPDATE, así que se podría cambiar el nivel de aislamiento después de
>> la
>> ejecución de la instrucción UPDATE, o simplemente no ejecutar en
>> ningún
>> momento SET TRANSACTION ISOLATION LEVEL SERIALIZABLE y su lugar
>> utilizar
>> un lock hint en la tabla:
>>
>
> Otra preguntita porque al final me confundi un poco para aclarar los
> conceptos:
>
> Eso ultimo que me explicas no equivaldria a haber puesto ese lock hint
> en
> el "select where not exists()" de la version original del
> procedimiento? o
> incluso no seria lo mismo que haber puesto el mismo "set
> ...serializable"
> en aquella version del proc. ? Al menos leyendo la ayuda uno puede
> deducir
> eso.
> En este caso no hablo de la eficiciencia sino para evitar el error de
> duplicidad en la concurrencia y el interlock que ustedes citaban.
>
> Tambien, por qué si ese tipo de actualizaciones multitablas (ejemplo
> los
> casos de header -detail) son tan normales en las transacciones, por que
> segun la ayuda el tipo de isolation level por defecto NO es
> SERIALIZABLE
> sino READ COMMITTED ?
> No es muy propenso a ese tipo de problemas el Read Commited ?
>
>
> Luis
>

Jesús López,

Me parece estupendo. Dejanos saber cuando este listo, posteando el link aca
en el grupo.

Saludos,
Alejandro Mesa

"Jesús López" wrote:

> Debido a los test que has hecho al respecto y el manejo que tienes sobre
> este tema, te exhorto a que escribas un articulo sobre el mismo.

Hace unos días que llevamos trabajando sobre un arttículo que hemos mandado
ayer mismo a dotnetmania para ver si lo publican, donde presentamos
distintas técnicas para la generación de secuencias, son 16 páginas :-) así
que probablemente lo tendremos que reducir o partirlo en dos.


>Me parece
> que la opcion de usar sp_getapplock no es mala

No, desde luego que no.

> si eliminamos el uso de
> sp_releaseapplock y usamos SET XACT_ABORT y manejo de errorres para
> asegurar
> que la transaccion no se quede abierta y por ende el lock sobre el recurso
> persista por mas tiempo de el necesario.
>
> Que crees?
>

Que así es como habría que hacerlo antes de ponerlo en producción :-)

Sin embargo creo que es una buena práctica llamar a sp_releaseapplock para
liberar el bloqueo lo antes posible. La generación del secuencial puede ser
parte de una transacción más grande donde podríamos liberar el bloqueo antes
de que termine la transacción gorda.

>
> AMB
>
>
> "Jesús López" wrote:
>
>> > Eso ultimo que me explicas no equivaldria a haber puesto ese lock hint
>> > en
>> > el "select where not exists()" de la version original del
>> > procedimiento? o
>> > incluso no seria lo mismo que haber puesto el mismo "set
>> > ...serializable"
>> > en aquella version del proc. ? Al menos leyendo la ayuda uno puede
>> > deducir
>> > eso.
>>
>>
>> Técnica A:
>>
>> if not exists( select * )
>> insert ...
>> else
>> update ...
>>
>> (1) con un nivel de aislamiento serializable tienes interbloqueos.
>> Tanto
>> si pones SET TRANSACTION ISOLATION LEVEL SERIALIZABLE como si usas un
>> lock
>> hint en el select if not exists( select * form tabla with (serializable)
>> (2) con un nivel de aislamiento distinto de serializable tienes
>> violaciones de clave
>>
>>
>> Técnica B:
>>
>> update
>>
>> if @@rowcount = 0
>> insert ...
>>
>> (1) con un nivel de aislamiento serializable no tienes problemas, eso lo
>> puedes hacer de dos maneras, con SET TRANSACTION ISOLATION LEVEL
>> SERIALIZABLE o poniendo el lock hint el el update que es la única parte
>> que
>> lo requiere en este caso.
>>
>> (2) con un nivel de aislamiento distinto de serializable tienes
>> violaciones
>> de clave
>>
>>
>> Técnica C:
>>
>> Otra técnica presentada consiste en tratar las violaciones de clave,
>> primero
>> hacemos que la restricción de unicidad ignore los duplicados, de esta
>> manera, en vez de tener errores en la violación de clave, tenemos sólo
>> advertencias:
>>
>> if not exists (select * from ... )
>> begin
>> insert
>> end
>> update .
>>
>>
>> Técnica D:
>>
>> Y la última técnica consiste en usar sp_getapplock para gestionar la
>> concurrencia.
>>
>>
>> Conclusión:
>>
>> Yo descartaría la técnica A por los problemas que produce. A mi entender
>> la
>> mejor, por eficiencia y simplicidad, es la técnica B usando un lock hint
>> en
>> el update. Las técnicas C y D también son válidas, pero no son tan
>> eficientes como la B.
>>
>>
>> > Tambien, por qué si ese tipo de actualizaciones multitablas (ejemplo
>> > los
>> > casos de header -detail) son tan normales en las transacciones, por que
>> > segun la ayuda el tipo de isolation level por defecto NO es
>> > SERIALIZABLE
>> > sino READ COMMITTED ?
>>
>> El nivel de aislamiento serializable produce muchos bloqueos,
>> disminuyendo
>> la simultaneidad y consumiendo más recursos y en la mayoría de los casos
>> con
>> un nivel de aislamiento read committed es suficiente. Observa que la
>> solución que a mi más me gusta (técnica C) toda la transacción se ejecuta
>> en
>> su mayoría en un nivel de aislamiento read committed, sólamente la
>> instrucción update de la secuencia es necesario usar el nivel de
>> aislamiento
>> serializable. Por otra parte la técnica C se ejecuta perfectamente sin
>> problemas en el nivel de aislamiento read committed.
>>
>>
>> Saludos:
>>
>> Jesús López
>> www.solidq.com
>>
>>
>>
>> "Luis Flores" escribió en el mensaje
>> news:
>> >
>> >>
>> >> Es necesario si quieres evitar la posibilidad de violación de clave.
>> >> Pero
>> >> en realidad ese nivel de aislamiento sólo se necesita para la
>> >> instrucción
>> >> UPDATE, así que se podría cambiar el nivel de aislamiento después de
>> >> la
>> >> ejecución de la instrucción UPDATE, o simplemente no ejecutar en
>> >> ningún
>> >> momento SET TRANSACTION ISOLATION LEVEL SERIALIZABLE y su lugar
>> >> utilizar
>> >> un lock hint en la tabla:
>> >>
>> >
>> > Otra preguntita porque al final me confundi un poco para aclarar los
>> > conceptos:
>> >
>> > Eso ultimo que me explicas no equivaldria a haber puesto ese lock hint
>> > en
>> > el "select where not exists()" de la version original del
>> > procedimiento? o
>> > incluso no seria lo mismo que haber puesto el mismo "set
>> > ...serializable"
>> > en aquella version del proc. ? Al menos leyendo la ayuda uno puede
>> > deducir
>> > eso.
>> > En este caso no hablo de la eficiciencia sino para evitar el error de
>> > duplicidad en la concurrencia y el interlock que ustedes citaban.
>> >
>> > Tambien, por qué si ese tipo de actualizaciones multitablas (ejemplo
>> > los
>> > casos de header -detail) son tan normales en las transacciones, por que
>> > segun la ayuda el tipo de isolation level por defecto NO es
>> > SERIALIZABLE
>> > sino READ COMMITTED ?
>> > No es muy propenso a ese tipo de problemas el Read Commited ?
>> >
>> >
>> > Luis
>> >
>>
>>
>>

Gracias compa, dudando de lo que escribiste aquí, quise implementarlo y la
verdad me que dé de seis, jaló y no importa que tenga o no dado de alta el
prefijo, de todos modos jala...

"Alejandro Mesa" escribió en el
mensaje de noticias
news:

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
>