(para integral) ejemplo para manejar correlativos (se graba el último correlativo en la configuración del sistema)

[Cristian Novoa]

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_CorrelativoProximo]
    @rut_empresa    udt_rut_empresa2,
    @cod_param        varchar(50),    -- ejemplo: CORRELATIVO_FACTURAS_ELECTRONICAS
    @prox_num        decimal(10,0) output
AS
    BEGIN TRANSACTION



    update tbl_parametro_empresa
        set valor = cast(cast(valor AS decimal)+1 as varchar)
            where rut_empresa = @rut_empresa AND codigo_parametro = @cod_param;
   
    SET @prox_num = (SELECT cast(valor as decimal)
                        FROM tbl_parametro_empresa
                            WHERE rut_empresa = @rut_empresa AND codigo_parametro = @cod_param);



    COMMIT TRANSACTION;

[Victor Espina]

Este tema es bastante complejo, sobre todo en ambientes de alta transaccionalidad.   Efectivamente, incrementar el contador dentro de una transaccion es lo correcto... peeeeero... que pasa si invocas ese SP desde otro SP que ya tenia abierta una transaccion?   el COMMIT TRANSACTION del SP cerrar TODA la transaccion hasta el primer nivel. lo cual no es deseable si aun faltan cosas por grabar.

Una solucion podria ser verificar en el SP si hay una transaccion abierta ya, antes de abrir otra:

DECLARE @innterTx BIT = 0

IF @@TRANCOUNT = 0 BEGIN

   BEGIN TRANSACTION

   SET @innerTx = 1

END

...

IF @innreTx = 1 COMMIT TRANSACTION

De esta forma si el SP es invocado cuando ya habia una transaccion abierta, no se ejecutara el COMMIT y el UPDATE sobre la tabla de control quedara enmarcado dentro de la transaccion preexistente.  Peeeeero, esto generara un bloqueo sobre ese registro en particular (o incluso sobre el DATAPAGE completo), lo cual significa que si otro usuario intenta obtener otro numero de la misma secuencia mientras esa transaccion aun no finaliza, quedara bloqueado esperando a que este bloqueo anterior se libere.   Si tienes, por ejemplo, 5 cajas de venta con un volumen de transacciones alto, esto escalara muy rapidamente causando una caida notable en el tiempo de respuesta e incluso los temidos DEADLOCKS (dos procesos bloqueandose mutuamente).

La mejor solucion que yo he encontrado para esto es tener UNA TABLA POR CADA SECUENCIA DE NUMEROS, con una columna IDENTITY, de modo que el SP quedaria asi:

CREATE PROCEDURE [dbo].[usp_CorrelativoProximo]
    @cod_empresa    varchar(50),

    @cod_param      varchar(50),    -- ejemplo: CORRELATIVO_FACTURAS_ELECTRONICAS
    @prox_num       decimal(10,0) output
AS BEGIN

   DECLARE @sql NVARCHAR(max)

   DECLARE @tname VARCHAR(max) = 'seq_' + @cod_empresa + '_' + @cod_param

   IF OBJECT_ID(@tname) IS NULL BEGIN

      SET @sql = N'CREATE TABLE ' + @tname + N'(fecha DATETIME, user VARCHAR(max), valor INT IDENTITY)

      EXEC sp_ExecuteSQL @sql

   END

   SET @sql = N'INSERT INTO ' + @tname + ' (fecha, user) VALUES (GETDATE(), SUSER_NAME())'

   EXEC sp_ExecuteSQL

   SET @prox_num = ident_current(@tname)

END

GO

En este escenario IGUAL se crea un bloqueo sobre la tabla de control, pero se hara sobre un registro que NADIE MAS va a accesar en ese momento, por lo que se elimina el problema del bloqueo con otras transacciones que pudieran estar necesitando un valor de la misma secuencia.  La parte negativa es que el manejo es bastante "poco ortodoxo".

Otra forma de lidiar con este problema es mantener la version original del SP pero dividir la transaccion en dos partes:

a) Primero se obtiene el numero de secuencia y se crea el registro en la tabla principal, con un estatus ANULADO.  La idea de esto es que si ocurre cualquier error mas adelante, no se producen saltos en la secuencia porque el registro con el numero de secuencia queda grabado pero con estatus ANULADO.

b) Ahora se abre la transaccion y se actualizan todas las demas tablas inolucradas, haciendo un UPDATE final sobre al tabla principal para actualizar su status (a, digamos, PENDIENTE o FINALIZADO).

Saludos

Victor Espina

[Cristian Novoa]

Nunca me he ha fallado, lo invoco desde otra transacción, o sea, con unidamiento.

Hice pruebas con llamados a este sp en dos sesiones, y no se producían colisiones.

Investigué harto el tema.

[Victor Espina]

Yo inicialmente hacia algo muy similar y tampoco me habia fallado nunca... hasta que me toco un cliente con 15 sucursales,  multiples vendedores por sucursal y una carga transaccional de mas de 1.000 documentos por dia.... 

Victor Espina

[Cristian Novoa]

Yo hice una prueba extrema. Abrí dos sesiones de SQL Server, corriendo este mismo sp en un ciclo. Nunca hubo colisión.

[integral]

Gracias Amigo...

Saludos,

INTEGRAL

[Victor Espina]

Bueno amigo, solo comparto mi (dolorosa) experiencia con estos temas.  Al final, cada quien aprende a traves de su propio sufrimiento.

Saludos

Victor Espina