Pregunta sobre diseño de PK

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

Otra opcion que acabo de probar, es subiendo el nivel de aislamiento al
nivel por defecto y usar sp_getapplock

Sí, desde luego con sp_getapplock asunto solucionado. Pero como bien dices
el rendimiento decae, y esta técnica pierde su gracia.

Saludos:

Jesús López
www.solidq.com

Ni tendrás que hacer más pruebas mañana :)

Esta solución sí que no tiene ningún problema y es más efiente. Te ha
costado un poquito, pero al final has conseguido la solución idónea.

Tengo que reconocer que esta última solución no plantea problemas y es más
eficiente que la que propuse yo al principio.


Un saludo:

Jesús López

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
> (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
>

"Luis Flores" escribió en el mensaje
news:

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

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:

UPDATE dbo.prefijo_item_ultima_seq WITH (SERIALIZABLE)
SET @seq = ultima_seq = ultima_seq + 1
WHERE prefijo_item = @prefijo_item

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?

Así es. Pero es una buena práctica que si un procedimiento requiere que se
ejecute en una transacción, eso se especifique dentro del procedimiento

Tiene eso alguna implicacion en lo que se ha estado discutiendo sobre la
concurrencia y los bloqueos?

Ninguna. Las transacciones simplemente están anidadas, pero el anidamiento
de transacciones es puramente sintáctico, en realidad sólo puede haber una
transacción en curso.

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
> (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
>