Pregunta sobre diseño de PK

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

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

Es que esta respuesta la puse después de la otra en la que doy las razones y
muestro el código.

Así es, prodía adaptarse perfectamente:

CREATE TABLE Documentos (
CodDocumento char(8) PRIMARY KEY,
NombreDocumento varchar(50)
)

GO

CREATE PROCEDURE AñadirDocumento
@NombreDocumento varchar(50),
@CodDocumento char(8) OUT
AS
BEGIN
BEGIN TRANSACTION
SET NOCOUNT ON
EXEC sp_getapplock 'InsercionDocumentos', 'Exclusive'

DECLARE @Secuencial int

SELECT TOP(1) @Secuencial = CONVERT(int, CodDocumento) + 1
FROM Documentos
ORDER BY CodDocumento DESC

IF @Secuencial IS NULL SET @Secuencial = 1

SET @CodDocumento = RIGHT(REPLICATE('0', 8) + CONVERT(varchar(8),
@Secuencial), 8)

SET NOCOUNT OFF
INSERT INTO Documentos VALUES (@CodDocumento, @NombreDocumento)
SET NOCOUNT ON

EXEC sp_releaseapplock 'InsercionDocumentos'
COMMIT
END
GO

Y es bastante eficiente. Después de insertar unos 140.000 registros en esa
tabla, la ejecución del siguiente script que inserta 10000 registros más:

DECLARE @i int
SET @i = 0
WHILE @i < 10000
BEGIN
EXEC AñadirDocumento 'Un documento cualquiera y tal y tal', NULL
SET @i = @i + 1
END

Tarda unos cuatro segundos en mi portátil. (un centrino)

La ejecución del siguiente código:

SET STATISTICS IO ON
GO
DECLARE @CodDocumento char(8)
EXEC AñadirDocumento 'Mi primer documento', @CodDocumento OUT
PRINT @CodDocumento
GO

Muestra el siguiente resultado:

Table 'Documentos'. Scan count 1, logical reads 3, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.
Table 'Documentos'. Scan count 0, logical reads 3, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob
read-ahead reads 0.

(1 row(s) affected)
00150080

Lo que indica que para hacer la select que coge el último CodDocumento tiene
que leer tres páginas, es decir muy poca cosa comparado con el tamaño de la
tabla que ocupa 1029 páginas.

De todas maneras, la técnica mostrada por Alejandro que podríamos llamar
"tabla auxiliar de secuencias" sólo plantea problemas (a mi entender, ya que
Alejandro no está de acuerdo) cuando no se conoce a priori el nombre de la
secuencia (el prefijo en el problema que ha planteado Luis Flores). Si se
conoce a priori el nombre de la secuencia no hay problemas, 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.

Saludos:

Jesús López



"Josias" <josias> escribió en el mensaje
news:%

Hola Jesús:

dos preguntas:
-Yo tengo una tabla de documentos donde la clave es tipo char(8) para
guardar un numero secuencial que lleva ceros a la izquierda. Por razones
que no voy a explicar aqui debe ser char(8) y no un identity. Podría
adaptar ese metodo que usas para llevar de forma eficiente el consecutivo
sin tener que poner una tabla aparte ?
Nota: la tabla tiende a crecer mucho (imaginate son 8 posiciones de la
clave).




-Que ocurre si el sp_getapplock no prospera?



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

Bueno pues una solución podría ser la siguiente:


CREATE TABLE Items
(
CodItem char(6) PRIMARY KEY,
ItemPrefix AS LEFT(CodItem, 3),
Secuential AS CONVERT(int, RIGHT(CodItem, 3)),
ItemName varchar(50) NOT NULL
)
GO

CREATE INDEX Items_Prefix_Secuential ON Items(ItemPrefix, Secuential
DESC)


ALTER PROCEDURE InsertItem
@ItemName varchar(50),
@CodItem char(6) OUT
AS
BEGIN
SET NOCOUNT ON
DECLARE @ItemPrefix char(3)
SET @ItemPrefix = LEFT(@ItemName, 3)
DECLARE @Secuential int
BEGIN TRANSACTION
EXEC sp_getapplock @ItemPrefix, 'Exclusive'

SELECT TOP(1) @Secuential = Secuential + 1
FROM Items
WHERE ItemPrefix = @ItemPrefix
ORDER BY Secuential DESC

IF @Secuential IS NULL SET @Secuential = 1

SET @CodItem = @ItemPrefix + RIGHT( '00' + CONVERT(varchar(3),
@Secuential), 3)
SET NOCOUNT OFF
INSERT INTO Items VALUES( @CodItem, @ItemName)
SET NOCOUNT ON
EXEC sp_releaseapplock @ItemPrefix
COMMIT
END


Pruebas:

DECLARE @CodItem char(6)
EXEC InsertItem 'AAA uno', @CodItem OUT
PRINT @CodItem
GO

DECLARE @CodItem char(6)
EXEC InsertItem 'AAA dos', @CodItem OUT
PRINT @CodItem
GO

DECLARE @CodItem char(6)
EXEC InsertItem 'AAA tres', @CodItem OUT
PRINT @CodItem
GO

DECLARE @CodItem char(6)
EXEC InsertItem BBB uno', @CodItem OUT
PRINT @CodItem
GO

DECLARE @CodItem char(6)
EXEC InsertItem BBB dos', @CodItem OUT
PRINT @CodItem
GO

DECLARE @CodItem char(6)
EXEC InsertItem BBB tres', @CodItem OUT
PRINT @CodItem
GO

Saludos:

Jesús López
www.solidq.com



"Luis Flores" escribió en el mensaje
news:

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

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