Pregunta sobre diseño de PK

Jesús López,

El problema es que la conexión que recibe la advertencia también devuelve un
secuencial de 1. Con lo cual estás duplicando los secuenciales.

Buena atrapada :-)


AMB

"Jesús López" wrote:

> WAITFOR TIME '19:31:00'

Eso no se me había ocurrido. Y efectivamente es una forma de provocar la
situación.

> Ejecute los scripts, mencionados al principio de este mensaje, varias
> veces
> y no tuve problemas. De vez en cuando una de las sesiones mostraba 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.
>

El problema es que la conexión que recibe la advertencia también devuelve un
secuencial de 1. Con lo cual estás duplicando los secuenciales.


Saludos:

Jesús López
www.solidq.com

Luis Flores,

Ya Jesús nos dio la respuesta. Yo por mi parte prefiero usar cuanto pueda,
puesto que SQL Server no se comporta de igual manera con todo error. Unos
hacen que se aborte solo la transaccion que lo genera, otros abortan el
batch, unas veces SQL Server aborta la transaccion automaticamente, otras no,
que lio.

Hechale un vistazo a este par de articulos sobre el tratamiento de errores
en SQL Server, escritos por Erland Sommarskog, SQL Server MVP.

Error Handling in SQL Server – a Background
http://www.sommarskog.se/error-handling-I.html

Implementing Error Handling with Stored Procedures
http://www.sommarskog.se/error-handling-II.html


AMB

"Luis Flores" wrote:

Hola Alejandro
Finalmente de mi parte,
Para ese ultimo ejemplo, es necesario el SET TRANSACTION ISOLATION LEVEL
SERIALIZABLE ?

Ademas, si yo tengo la transaccion solo desde el SP principal, es decir, el
usp_InsertItem, desde el cual siempre llamo al que incrementa la secuencia
(usp_TomarUltimaSeq), puedo dejar este ultimo sp sin begin transaction , no
es asi?
Tiene eso alguna implicacion en lo que se ha estado discutiendo sobre la
concurrencia y los bloqueos?

Nuevamente gracias por sus mensajes ya que el hilo me ha sido muy
instructivo. Me he dado cuenta que no sabia practicamente nada de este tema
:)

Saludos

Luis


"Alejandro Mesa" escribió en el
mensaje 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

Jesús López,

Espera, mientras tomo el cafe, noto que no es asi. Veamos el codigo.

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


Fijate que solo pregunto por el error (@error) para saltar el UPDATE, pero
cuando se inserta un duplicado, lo que se recive es una advertencia (de que
el duplicado no se inserto - no se recive error) y seguidamente ocurre la
actualizacion. Fijate que la sentencia INSERT inserta el valor por defecto
(0) y luego este es actualizado.

La idea no es muy ortodoxa que digamos, pero devuelve la proxima secuencia
de acuerdo al valor presente durante la actualizacion.

De todas maneras prefiero usar la version que hace uso de sp_getapplock, que
por cierto no he mencionado que es una idea interesante y fuera de lo comun
para aplicar a este tipo de problemas.

AMB

"Alejandro Mesa" wrote:

Jesús López,

> El problema es que la conexión que recibe la advertencia también devuelve un
> secuencial de 1. Con lo cual estás duplicando los secuenciales.

Buena atrapada :-)


AMB

"Jesús López" wrote:

> > WAITFOR TIME '19:31:00'
>
> Eso no se me había ocurrido. Y efectivamente es una forma de provocar la
> situación.
>
> > Ejecute los scripts, mencionados al principio de este mensaje, varias
> > veces
> > y no tuve problemas. De vez en cuando una de las sesiones mostraba 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.
> >
>
> El problema es que la conexión que recibe la advertencia también devuelve un
> secuencial de 1. Con lo cual estás duplicando los secuenciales.
>
>
> Saludos:
>
> Jesús López
> www.solidq.com
>
>
>

Tienes razón. Con tanto lío de versiones diferentes del procedimiento he
debido de ejecutar otra versión en la que se hacía:

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

Saludos:

Jesús López
www.solidq.com

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