Pregunta sobre diseño de PK

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,

Claro. Yo noté ese detalle y lo completé justamente haciendo un if not
exists() y un insert. Lo importante es tomar la idea de la solución que el
me dio que, como tu propuesta, me ayudó a dar solucion terminada a mi
problema. Obviamente no hice solo copiar y pegar.

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.

Realmente no da problemas porque hay una transaccion explícita abierta que
bloqueará ambas tablas actualizadas. De hecho con el begin transaction no
es necesario hacer bloqueos explicitos.
Claro, solo hay que hacer la prueba para saber si funciona.

No te creas que cuando yo he propuesto mi solución no he pensado en una
tabla auxiliar de "secuencias" o prefijos como la quieras llamar. He visto
los problemas que planteaba y he pensado que era mejor solución utilizar
una variación de la técnica MAX+1 para secuencias. Además la técnica que
yo he propuesto es más simple, no requiere de tablas auxiliares ni crear
procedimientos auxiliares. También te puedo decir que es eficiente. He
probado de varias formas diferentes de implementarla y he llegado a la
conclusión de que la técnica propuesta es la implementación más eficiente
posible de técnica MAX+1 para secuencias.

Tu solución también es buena, de hecho muy buena. Solo que pensé en el
crecimiento de la tabla, lo cual puede ser mas eficiente con una tabla de
consecutivos que hacer cada vez un select y un agregado. Además pensando
quizás en generalización del generador del consecutivo, bastaría con agregar
el parámetro NombreTabla a la tabla de consecutivos.

De todos modos gracias por tu aportación que ha sido muy buena y tambien por
la explicación.


Saludos
Luis

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

SELECT * FROM dbo.prefijo_item_ultima_seq
SELECT * FROM dbo.Item
GO

DROP PROCEDURE dbo.usp_InsertItem, dbo.usp_TomarUltimaSeq
GO

DROP TABLE dbo.Item, dbo.prefijo_item_ultima_seq
GO


AMB




"Jesús López" wrote:

¿Por qué dices que es la mejor solución?

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.

No te creas que cuando yo he propuesto mi solución no he pensado en una
tabla auxiliar de "secuencias" o prefijos como la quieras llamar. He visto
los problemas que planteaba y he pensado que era mejor solución utilizar una
variación de la técnica MAX+1 para secuencias. Además la técnica que yo he
propuesto es más simple, no requiere de tablas auxiliares ni crear
procedimientos auxiliares. También te puedo decir que es eficiente. He
probado de varias formas diferentes de implementarla y he llegado a la
conclusión de que la técnica propuesta es la implementación más eficiente
posible de técnica MAX+1 para secuencias.

Saludos:

Jesús López
www.solidq.com






"Luis Flores" escribió en el mensaje
news:%
> Gracias.
> Creo que es la mejor solución que puedo implementar y te agradezco los
> detalles y la explicacion.
>
> Saludos
> Luis
>
> "Alejandro Mesa" escribió en el
> mensaje news:
>> Luis Flores,
>>
>> Pudieras tener una tabla de ayuda, donde almacenas el par (prefijo_Item,
>> ultima_secuencia) donde el prefijo del item es clave primaria. Entonces
>> cada
>> vez que insertes un item, tomas el valor de la proxima secuencia desde
>> esta
>> tabla.
>>
>> 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
>> SET nocount ON
>>
>> UPDATE dbo.prefijo_item_ultima_seq
>> SET @seq = ultima_seq = ultima_seq + 1
>> WHERE prefijo_item = @prefijo_item
>>
>> 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
>>
>> INSERT INTO dbo.prefijo_item_ultima_seq (
>> prefijo_item,
>> ultima_seq
>> ) VALUES ('SQL', 0)
>>
>> INSERT INTO dbo.prefijo_item_ultima_seq (
>> prefijo_item,
>> ultima_seq
>> ) VALUES ('MS ', 0)
>> GO
>>
>> SELECT * FROM dbo.prefijo_item_ultima_seq
>> 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
>>
>> SELECT * FROM dbo.prefijo_item_ultima_seq
>> SELECT * FROM dbo.Item
>> GO
>>
>> DROP PROCEDURE dbo.usp_InsertItem, dbo.usp_TomarUltimaSeq
>> GO
>>
>> DROP TABLE dbo.Item, dbo.prefijo_item_ultima_seq
>> GO
>>
>> Ahora te comento porque decidi poner el prefijo del item y la secuencia
>> en
>> columnas separadas. Si deseas hacer este tipo de busquedas:
>>
>> declare @prefijo char(3)
>> declare @seq_inicio int, @seq_fin int
>>
>> set @prefijo = 'SQL'
>> set @seq_inicio = 2
>> set @seq_fin = 3
>>
>> select
>> *
>> from
>> dbo.Item
>> where
>> prefijo_item = @prefijo_Item
>> and seq between @seq_inicio and @seq_fin
>>
>> No tendras ningun problema con el esquema usado, pero si en cambio solo
>> tienes la columna prefijo_item_seq (AAA999), entonces tendras que
>> manipular
>> la columna en la expresion y no estaras ayudando al optimizador de
>> queries en
>> la busqueda del plan de ejecucion adecuado.
>>
>> set @prefijo = 'SQL'
>> set @seq_inicio = 2
>> set @seq_fin = 3
>>
>> select
>> *
>> from
>> dbo.Item
>> where
>> prefijo_item like @prefijo_Item + '%'
>> and right(seq, 3) between @seq_inicio and @seq_fin
>>
>> Fijate que la expresion de filtro cambia, sobre todo fijate en la
>> introduccion de right(seq, 3).
>>
>> No existe una formula magica en esto de dise~nar una base de datos, asi
>> que
>> tambien te comento que usar este esquema tiene el inconveniente de que no
>> podemos llamar al procedimiento que nos devuelve la proxima secuencia a
>> usar
>> para un determinado prefijo, durante una carga masiva (BULK INSERT).
>>
>>
>> AMB
>>
>>
>> "Luis Flores" wrote:
>>
>>> Me piden para una tabla de Items un codigo alfanumerico pero con la
>>> siguiente caracteristica;
>>>
>>> AAA999, donde AAA son tres letras correspondientes a las tres primeras
>>> del
>>> nombre del item y 999 es una secuencia.
>>> Ejemplos de codigos: PAP001, PAP002, ALM001, ALM002...
>>>
>>> Quieren que el sistema me genere la proxima secuencia. He buscado en
>>> los
>>> BOL como hacer eso en SQL pero no encuentro la manera, solo veo lo de
>>> las
>>> identity.
>>>
>>>
>>> Alguna ayuda al respecto? Me conformaria incluso con tener el codigo
>>> separado en dos campos, si no hay mas remedio y tener un el consecutivo
>>> autoincrementado, si se puede.
>>>
>>> Gracias
>>>
>>> Luis
>>>
>>>
>>>
>
>

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.

Realmente no da problemas porque hay una transaccion explícita abierta
que bloqueará ambas tablas actualizadas. De hecho con el begin
transaction no es necesario hacer bloqueos explicitos.
Claro, solo hay que hacer la prueba para saber si funciona.

Sí da problemas, la transacción no hace que se bloquen las tablas, de forma
predeterminada SQL Server bloquea a nivel de regirstro.

Dada la tabla de secuencias:

USE tempdb
GO

CREATE TABLE Secuencias
(
Prefijo char(3),
Secuencial int,
PRIMARY KEY (Prefijo, Secuencial)
)

Supongamos que el código que estás ejecutando es el siguiente:

BEGIN TRANSACTION

IF NOT EXISTS( SELECT * FROM Secuencias WHERE Prefijo = @Prefijo)
INSERT INTO Secuencias VALUES (@Prefijo, 0)

etc etc.

COMMIT

La instrucción BEGIN TRANSACTION por sí misma no bloquea absolutamente nada.

Luego viene la instrucción IF NOT EXISTS(). Nada impide que dos
conexiones estén ejecutando al mismo tiempo esta instrucción SELECT y
obtengan el mismo resultado. La instrucción select produce bloqueos
compartidos que permiten a otras conexiones leer los datos.

Por lo tanto dos conexiones pueden intentar insertar el mismo prefijo
violando la clave primaria. La situación puede darse perfectamente. Sobre
todo en un sistema con bastante carga y una máquina con varios procesadores.

Observa el siguiente código que es el mismo código real, sólo le añadimos un
DELAY para que la situación se produzca más fácilmente.

Prepara este script en dos conexiones de SQL Server:

USE Tempdb
GO

BEGIN TRANSACTION
IF NOT EXISTS (SELECT * FROM Secuencias WHERE Prefijo = 'XXX')
BEGIN
WAITFOR DELAY '00:00:05'
INSERT INTO Secuencias VALUES ('XXX', 0)
END
COMMIT

Y luego ejecuta los dos al mismo tiempo. En el segundo que ejecutas recibes
el error de violación de clave primaria.

En el código real el delay no existe, pero nada impide que la secuencia de
ejecución sea la siguiente:

Conexión 1: SELECT * FROM Secuencias WHERE Prefijo = 'XXX'
Conexión 2: SELECT * FROM Secuencias WHERE Prefijo = 'XXX'
Conexión 1: INSERT INTO ... COMMIT
Conexión 2: INSERT INTO (Violación de clave primaria)

Tu solución también es buena, de hecho muy buena. Solo que pensé en el
crecimiento de la tabla, lo cual puede ser mas eficiente con una tabla de
consecutivos que hacer cada vez un select y un agregado

El plan de ejecución que me sale para la ejecución del procedimietno
InsertItem no puede ser mejor, no aparece ningún tipo de agregado,
simplemente se busca un registro en un índice, no hay cosa más rápida en SQL
Server.


(14% del batch)
SELECT < Top <- Compute Scalar < Index Seek


(86% del batch)
INSERT < Index Insert < Compute Scalar < Clustered Index Insert




Aquí lo tienes completo


1 1 SELECT TOP(1) @Secuential = Secuential + 1 FROM Items
WHERE ItemPrefix = @ItemPrefix ORDER BY Secuential DESC 1 1 0 NULL
NULL NULL NULL 1 NULL NULL NULL 0,0032833 NULL NULL SELECT 0 NULL
1 1 |--Top(TOP EXPRESSION:((1))) 1 2 1 Top Top TOP EXPRESSION:((1)) NULL 1
0 1E-07 11 0,0032833 [Expr1003] NULL PLAN_ROW 0 1
0 0 |--Compute
Scalar(DEFINE:([Pruebas].[dbo].[Items].[Secuential]=[Pruebas].[dbo].[Items].[Secuential],
[Expr1003]=[Pruebas].[dbo].[Items].[Secuential]+(1))) 1 3 2 Compute Scalar
Compute Scalar
DEFINE:([Pruebas].[dbo].[Items].[Secuential]=[Pruebas].[dbo].[Items].[Secuential],
[Expr1003]=[Pruebas].[dbo].[Items].[Secuential]+(1))
[Pruebas].[dbo].[Items].[Secuential]=[Pruebas].[dbo].[Items].[Secuential],
[Expr1003]=[Pruebas].[dbo].[Items].[Secuential]+(1) 1 0 1E-07 15 0,0032832
[Pruebas].[dbo].[Items].[Secuential], [Pruebas].[dbo].[Items].[Secuential],
[Expr1003] NULL PLAN_ROW 0 1
1 1 |--Index
Seek(OBJECT:([Pruebas].[dbo].[Items].[Items_Prefix_Secuential]),
SEEK:([Pruebas].[dbo].[Items].[ItemPrefix]=[@ItemPrefix]) ORDERED FORWARD) 1
4 3 Index Seek Index Seek
OBJECT:([Pruebas].[dbo].[Items].[Items_Prefix_Secuential]),
SEEK:([Pruebas].[dbo].[Items].[ItemPrefix]=[@ItemPrefix]) ORDERED FORWARD
[Pruebas].[dbo].[Items].[Secuential] 1 0,003125 0,0001581 11 0,0032831
[Pruebas].[dbo].[Items].[Secuential] NULL PLAN_ROW 0 1


1 1 INSERT INTO Items VALUES( @CodItem, @ItemName) 2 1 0 NULL NULL NULL NULL
1 NULL NULL NULL 0,02000326 NULL NULL INSERT 0 NULL
1 1 |--Index
Insert(OBJECT:([Pruebas].[dbo].[Items].[Items_Prefix_Secuential]),
SET:([CodItem1006] = [Pruebas].[dbo].[Items].[CodItem],[ItemPrefix1007] =
[Pruebas].[dbo].[Items].[ItemPrefix],[Secuential1008] =
[Pruebas].[dbo].[Items].[Secuential])) 2 2 1 Index Insert Insert
OBJECT:([Pruebas].[dbo].[Items].[Items_Prefix_Secuential]),
SET:([CodItem1006] = [Pruebas].[dbo].[Items].[CodItem],[ItemPrefix1007] =
[Pruebas].[dbo].[Items].[ItemPrefix],[Secuential1008] =
[Pruebas].[dbo].[Items].[Secuential]) NULL 1 0,01 1E-06 9 0,02000326 NULL
NULL PLAN_ROW 0 1
0 0 |--Compute
Scalar(DEFINE:([Pruebas].[dbo].[Items].[ItemPrefix]=substring([Pruebas].[dbo].[Items].[CodItem],(1),(3)),
[Pruebas].[dbo].[Items].[Secuential]=CONVERT(int,right([Pruebas].[dbo].[Items].[CodItem],(3)),0)))
2 3 2 Compute Scalar Compute Scalar
DEFINE:([Pruebas].[dbo].[Items].[ItemPrefix]=substring([Pruebas].[dbo].[Items].[CodItem],(1),(3)),
[Pruebas].[dbo].[Items].[Secuential]=CONVERT(int,right([Pruebas].[dbo].[Items].[CodItem],(3)),0))
[Pruebas].[dbo].[Items].[ItemPrefix]=substring([Pruebas].[dbo].[Items].[CodItem],(1),(3)),
[Pruebas].[dbo].[Items].[Secuential]=CONVERT(int,right([Pruebas].[dbo].[Items].[CodItem],(3)),0)
1 0 1E-07 22 0,01000226 [Pruebas].[dbo].[Items].[CodItem],
[Pruebas].[dbo].[Items].[ItemPrefix], [Pruebas].[dbo].[Items].[Secuential]
NULL PLAN_ROW 0 1
1 1 |--Clustered Index
Insert(OBJECT:([Pruebas].[dbo].[Items].[PK__Items__412EB0B6]),
SET:([Pruebas].[dbo].[Items].[CodItem] =
RaiseIfNull([@CodItem]),[Pruebas].[dbo].[Items].[ItemName] =
RaiseIfNull([@ItemName]))) 2 4 3 Clustered Index Insert Insert
OBJECT:([Pruebas].[dbo].[Items].[PK__Items__412EB0B6]),
SET:([Pruebas].[dbo].[Items].[CodItem] =
RaiseIfNull([@CodItem]),[Pruebas].[dbo].[Items].[ItemName] =
RaiseIfNull([@ItemName])) NULL 1 0,01 1E-06 13 0,01000216
[Pruebas].[dbo].[Items].[CodItem] NULL PLAN_ROW 0 1



"Luis Flores" escribió en el mensaje
news:%

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,

Claro. Yo noté ese detalle y lo completé justamente haciendo un if not
exists() y un insert. Lo importante es tomar la idea de la solución que
el me dio que, como tu propuesta, me ayudó a dar solucion terminada a mi
problema. Obviamente no hice solo copiar y pegar.

No te creas que cuando yo he propuesto mi solución no he pensado en una
tabla auxiliar de "secuencias" o prefijos como la quieras llamar. He
visto los problemas que planteaba y he pensado que era mejor solución
utilizar una variación de la técnica MAX+1 para secuencias. Además la
técnica que yo he propuesto es más simple, no requiere de tablas
auxiliares ni crear procedimientos auxiliares. También te puedo decir que
es eficiente. He probado de varias formas diferentes de implementarla y
he llegado a la conclusión de que la técnica propuesta es la
implementación más eficiente posible de técnica MAX+1 para secuencias.

Tu solución también es buena, de hecho muy buena. Solo que pensé en el
crecimiento de la tabla, lo cual puede ser mas eficiente con una tabla de
consecutivos que hacer cada vez un select y un agregado. Además pensando
quizás en generalización del generador del consecutivo, bastaría con
agregar el parámetro NombreTabla a la tabla de consecutivos.

De todos modos gracias por tu aportación que ha sido muy buena y tambien
por la explicación.


Saludos
Luis

Sí da problemas, la transacción no hace que se bloquen las tablas, de
forma predeterminada SQL Server bloquea a nivel de regirstro.

No el select en este caso. El INSERT o el UPDATE sería lo que provocaría el
bloqueo. Al menos eso es lo que yo pensaba que es la forma en que funciona.

BEGIN TRANSACTION

IF NOT EXISTS( SELECT * FROM Secuencias WHERE Prefijo = @Prefijo)
INSERT INTO Secuencias VALUES (@Prefijo, 0)

Faltó el else, donde habría un UPDATE que como el insert hará que se
bloquee.

La instrucción BEGIN TRANSACTION por sí misma no bloquea absolutamente
nada.

No, pero el INSERT o el UPDATE sí lo harán. El tiempo del select no sería
tan significativo para una tabla limitada a consecutivos.

Luego viene la instrucción IF NOT EXISTS(). Nada impide que dos
conexiones estén ejecutando al mismo tiempo esta instrucción SELECT y
obtengan el mismo resultado. La instrucción select produce bloqueos
compartidos que permiten a otras conexiones leer los datos.

Por lo tanto dos conexiones pueden intentar insertar el mismo prefijo
violando la clave primaria. La situación puede darse perfectamente. Sobre
todo en un sistema con bastante carga y una máquina con varios
procesadores.

Claro que puede darse pero la probabilidad es muy pequeña y si da error de
clave primaria, pues se reintenta y listo.
Ojo: no digo que lo que sugieres no sea lo más preciso.

Observa el siguiente código que es el mismo código real, sólo le añadimos
un DELAY para que la situación se produzca más fácilmente.

Prepara este script en dos conexiones de SQL Server:

USE Tempdb
GO

BEGIN TRANSACTION
IF NOT EXISTS (SELECT * FROM Secuencias WHERE Prefijo = 'XXX')
BEGIN
WAITFOR DELAY '00:00:05'
INSERT INTO Secuencias VALUES ('XXX', 0)
END

Antes del insert OK y es lógico pero si lo pones después la historia es
distinta porque "Secuencias" estaría bloqueada hasta el Commit. No?

COMMIT

Y luego ejecuta los dos al mismo tiempo. En el segundo que ejecutas
recibes el error de violación de clave primaria.

Se entiende tu punto pero no es tan grave el asunto. Ojalá me entiendas a
mi :)

El plan de ejecución que me sale para la ejecución del procedimietno
InsertItem no puede ser mejor, no aparece ningún tipo de agregado,

Dije agregado porque citaste MAX() pero luego chequeé que es un TOP 1 y
Order by lo que haces. Ok. Es que siempre que veo construcciones similares
sobre una valor repetido me luce que es más trabajoso que un simple seek a
la pk de otra tabla.

Pero la aclaración me confirma lo dicho de que tu propuesta es muy buena. En
eso estamos de acuerdo.

De nuevo gracias pues he aprendido y aclarado algunas cosas nuevas con tus
mensajes. Se ve claro que sabes de lo que estas hablando.

Saludos y gracias,

Luis

Jesús López,

La instrucción BEGIN TRANSACTION por sí misma no bloquea absolutamente nada.

Luego viene la instrucción IF NOT EXISTS(). Nada impide que dos
conexiones estén ejecutando al mismo tiempo esta instrucción SELECT y
obtengan el mismo resultado. La instrucción select produce bloqueos
compartidos que permiten a otras conexiones leer los datos.

Lo que mencionas no se cumple si el nivel de aislamiento es SERIALIZABLE.
Bajo este nivel, se garantiza:

- Otra transaccion no puede leer data que ha sido modificada y no salvada
- Ninguna otra transaccion puede modificar data que se ha leido por la
transaccion corriente hasta que esta se haya completado.
- Ninguna otra transaccion puede insertar nuevas filas con clave que pueden
caer en el range de las claves leidas por cualquier sentencia en la
transaccion corriente hasta que esta se complete.

Lo importante aqui es que se tiene un indice unico por el prefijo.

Saludos,
Alejandro Mesa


"Jesús López" wrote:

>>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.
>>
>
> Realmente no da problemas porque hay una transaccion explícita abierta
> que bloqueará ambas tablas actualizadas. De hecho con el begin
> transaction no es necesario hacer bloqueos explicitos.
> Claro, solo hay que hacer la prueba para saber si funciona.
>


Sí da problemas, la transacción no hace que se bloquen las tablas, de forma
predeterminada SQL Server bloquea a nivel de regirstro.

Dada la tabla de secuencias:

USE tempdb
GO

CREATE TABLE Secuencias
(
Prefijo char(3),
Secuencial int,
PRIMARY KEY (Prefijo, Secuencial)
)

Supongamos que el código que estás ejecutando es el siguiente:

BEGIN TRANSACTION

IF NOT EXISTS( SELECT * FROM Secuencias WHERE Prefijo = @Prefijo)
INSERT INTO Secuencias VALUES (@Prefijo, 0)

etc etc.

COMMIT

La instrucción BEGIN TRANSACTION por sí misma no bloquea absolutamente nada.

Luego viene la instrucción IF NOT EXISTS(). Nada impide que dos
conexiones estén ejecutando al mismo tiempo esta instrucción SELECT y
obtengan el mismo resultado. La instrucción select produce bloqueos
compartidos que permiten a otras conexiones leer los datos.

Por lo tanto dos conexiones pueden intentar insertar el mismo prefijo
violando la clave primaria. La situación puede darse perfectamente. Sobre
todo en un sistema con bastante carga y una máquina con varios procesadores.

Observa el siguiente código que es el mismo código real, sólo le añadimos un
DELAY para que la situación se produzca más fácilmente.

Prepara este script en dos conexiones de SQL Server:

USE Tempdb
GO

BEGIN TRANSACTION
IF NOT EXISTS (SELECT * FROM Secuencias WHERE Prefijo = 'XXX')
BEGIN
WAITFOR DELAY '00:00:05'
INSERT INTO Secuencias VALUES ('XXX', 0)
END
COMMIT

Y luego ejecuta los dos al mismo tiempo. En el segundo que ejecutas recibes
el error de violación de clave primaria.

En el código real el delay no existe, pero nada impide que la secuencia de
ejecución sea la siguiente:

Conexión 1: SELECT * FROM Secuencias WHERE Prefijo = 'XXX'
Conexión 2: SELECT * FROM Secuencias WHERE Prefijo = 'XXX'
Conexión 1: INSERT INTO ... COMMIT
Conexión 2: INSERT INTO (Violación de clave primaria)


> Tu solución también es buena, de hecho muy buena. Solo que pensé en el
> crecimiento de la tabla, lo cual puede ser mas eficiente con una tabla de
> consecutivos que hacer cada vez un select y un agregado

El plan de ejecución que me sale para la ejecución del procedimietno
InsertItem no puede ser mejor, no aparece ningún tipo de agregado,
simplemente se busca un registro en un índice, no hay cosa más rápida en SQL
Server.


(14% del batch)
SELECT < Top <- Compute Scalar < Index Seek


(86% del batch)
INSERT < Index Insert < Compute Scalar < Clustered Index Insert




Aquí lo tienes completo


1 1 SELECT TOP(1) @Secuential = Secuential + 1 FROM Items
WHERE ItemPrefix = @ItemPrefix ORDER BY Secuential DESC 1 1 0 NULL
NULL NULL NULL 1 NULL NULL NULL 0,0032833 NULL NULL SELECT 0 NULL
1 1 |--Top(TOP EXPRESSION:((1))) 1 2 1 Top Top TOP EXPRESSION:((1)) NULL 1
0 1E-07 11 0,0032833 [Expr1003] NULL PLAN_ROW 0 1
0 0 |--Compute
Scalar(DEFINE:([Pruebas].[dbo].[Items].[Secuential]=[Pruebas].[dbo].[Items].[Secuential],
[Expr1003]=[Pruebas].[dbo].[Items].[Secuential]+(1))) 1 3 2 Compute Scalar
Compute Scalar
DEFINE:([Pruebas].[dbo].[Items].[Secuential]=[Pruebas].[dbo].[Items].[Secuential],
[Expr1003]=[Pruebas].[dbo].[Items].[Secuential]+(1))
[Pruebas].[dbo].[Items].[Secuential]=[Pruebas].[dbo].[Items].[Secuential],
[Expr1003]=[Pruebas].[dbo].[Items].[Secuential]+(1) 1 0 1E-07 15 0,0032832
[Pruebas].[dbo].[Items].[Secuential], [Pruebas].[dbo].[Items].[Secuential],
[Expr1003] NULL PLAN_ROW 0 1
1 1 |--Index
Seek(OBJECT:([Pruebas].[dbo].[Items].[Items_Prefix_Secuential]),
SEEK:([Pruebas].[dbo].[Items].[ItemPrefix]=[@ItemPrefix]) ORDERED FORWARD) 1
4 3 Index Seek Index Seek
OBJECT:([Pruebas].[dbo].[Items].[Items_Prefix_Secuential]),
SEEK:([Pruebas].[dbo].[Items].[ItemPrefix]=[@ItemPrefix]) ORDERED FORWARD
[Pruebas].[dbo].[Items].[Secuential] 1 0,003125 0,0001581 11 0,0032831
[Pruebas].[dbo].[Items].[Secuential] NULL PLAN_ROW 0 1


1 1 INSERT INTO Items VALUES( @CodItem, @ItemName) 2 1 0 NULL NULL NULL NULL
1 NULL NULL NULL 0,02000326 NULL NULL INSERT 0 NULL
1 1 |--Index
Insert(OBJECT:([Pruebas].[dbo].[Items].[Items_Prefix_Secuential]),
SET:([CodItem1006] = [Pruebas].[dbo].[Items].[CodItem],[ItemPrefix1007] =
[Pruebas].[dbo].[Items].[ItemPrefix],[Secuential1008] =
[Pruebas].[dbo].[Items].[Secuential])) 2 2 1 Index Insert Insert
OBJECT:([Pruebas].[dbo].[Items].[Items_Prefix_Secuential]),
SET:([CodItem1006] = [Pruebas].[dbo].[Items].[CodItem],[ItemPrefix1007] =
[Pruebas].[dbo].[Items].[ItemPrefix],[Secuential1008] =
[Pruebas].[dbo].[Items].[Secuential]) NULL 1 0,01 1E-06 9 0,02000326 NULL
NULL PLAN_ROW 0 1
0 0 |--Compute
Scalar(DEFINE:([Pruebas].[dbo].[Items].[ItemPrefix]=substring([Pruebas].[dbo].[Items].[CodItem],(1),(3)),
[Pruebas].[dbo].[Items].[Secuential]=CONVERT(int,right([Pruebas].[dbo].[Items].[CodItem],(3)),0)))
2 3 2 Compute Scalar Compute Scalar
DEFINE:([Pruebas].[dbo].[Items].[ItemPrefix]=substring([Pruebas].[dbo].[Items].[CodItem],(1),(3)),
[Pruebas].[dbo].[Items].[Secuential]=CONVERT(int,right([Pruebas].[dbo].[Items].[CodItem],(3)),0))
[Pruebas].[dbo].[Items].[ItemPrefix]=substring([Pruebas].[dbo].[Items].[CodItem],(1),(3)),
[Pruebas].[dbo].[Items].[Secuential]=CONVERT(int,right([Pruebas].[dbo].[Items].[CodItem],(3)),0)
1 0 1E-07 22 0,01000226 [Pruebas].[dbo].[Items].[CodItem],
[Pruebas].[dbo].[Items].[ItemPrefix], [Pruebas].[dbo].[Items].[Secuential]
NULL PLAN_ROW 0 1
1 1 |--Clustered Index
Insert(OBJECT:([Pruebas].[dbo].[Items].[PK__Items__412EB0B6]),
SET:([Pruebas].[dbo].[Items].[CodItem] =
RaiseIfNull([@CodItem]),[Pruebas].[dbo].[Items].[ItemName] =
RaiseIfNull([@ItemName]))) 2 4 3 Clustered Index Insert Insert
OBJECT:([Pruebas].[dbo].[Items].[PK__Items__412EB0B6]),
SET:([Pruebas].[dbo].[Items].[CodItem] =
RaiseIfNull([@CodItem]),[Pruebas].[dbo].[Items].[ItemName] =
RaiseIfNull([@ItemName])) NULL 1 0,01 1E-06 13 0,01000216
[Pruebas].[dbo].[Items].[CodItem] NULL PLAN_ROW 0 1



"Luis Flores" escribió en el mensaje
news:%
>> 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,
>
> Claro. Yo noté ese detalle y lo completé justamente haciendo un if not
> exists() y un insert. Lo importante es tomar la idea de la solución que
> el me dio que, como tu propuesta, me ayudó a dar solucion terminada a mi
> problema. Obviamente no hice solo copiar y pegar.
>
>>
>
>>
>> No te creas que cuando yo he propuesto mi solución no he pensado en una
>> tabla auxiliar de "secuencias" o prefijos como la quieras llamar. He
>> visto los problemas que planteaba y he pensado que era mejor solución
>> utilizar una variación de la técnica MAX+1 para secuencias. Además la
>> técnica que yo he propuesto es más simple, no requiere de tablas
>> auxiliares ni crear procedimientos auxiliares. También te puedo decir que
>> es eficiente. He probado de varias formas diferentes de implementarla y
>> he llegado a la conclusión de que la técnica propuesta es la
>> implementación más eficiente posible de técnica MAX+1 para secuencias.
>>
>
> Tu solución también es buena, de hecho muy buena. Solo que pensé en el
> crecimiento de la tabla, lo cual puede ser mas eficiente con una tabla de
> consecutivos que hacer cada vez un select y un agregado. Además pensando
> quizás en generalización del generador del consecutivo, bastaría con
> agregar el parámetro NombreTabla a la tabla de consecutivos.
>
> De todos modos gracias por tu aportación que ha sido muy buena y tambien
> por la explicación.
>
>
> Saludos
> Luis
>
>
>