Secuencias Sql Server

[Edgar Ramos]

Gente un saludo

Doy un poco de contexto sobre el problema que requiero solucionar, actualmente el sistema en que trabajo, tiene varias tablas cuya clave principal es del tipo identity, hasta aqui no hay ningun problema, este surge cuando en este sistema se ha utilizado ese identity (secuencial/incremento1) como dato para un proceso de negocio particular.

Este proceso de negocio es la facturacion, este proceso genera un documento cuya principal caracteristica es su numero de identificacion, la cual es secuencial

Se ha visto que este identity pierde su secuencial cada vez que una transaccion no se completa, es decir si el secuencial estaba en el 100, el siguiente numero que toca seria 101, pero ese momento algo sucede, y el proceso involucrado hace roolback, por lo tanto como resultado el 101 se ha perdido, y ese momento se genera otro numero  a veces un con valores de 100 hacia arriba o 10.

Hay alguna forma de atacar este problema?, quisiera mantener un mecanismo estable que me permita tener un secuencial para estos numeros de factura, que nunca se pierdan, y que siempre se incrementen en uno, no se puede perder el secuencial ya que el gobierno de turno es muy problematico con este tema.

Tambien comentarles que existe concurrencia de varios usuarios generando estos numeros de factura.

Cualquier comentario o sugerencia es bienvenido.

Muchas gracias

--

Saludos

Edgar

[Angel Java Lopez]

Hmmm.... primero separaria una clave primaria de algo tan importante del negocio.

O sea, que la clave primaria sea el que da la base de datos como identity, o un GUID, o lo que sea.

Y otro sea el campo del nro. de factura que pide el gobierno, campo marcado en la base como no nulo, unique, generado dentro de una transaccion, tomando el maximo hasta ese momento e incrementando uno.

Comentarios?

Nos leemos!

Angel "Java" Lopez

@ajlopez

--
Has recibido este mensaje porque estás suscrito al grupo "AltNet-Hispano" de Grupos de Google.
Para anular la suscripción a este grupo y dejar de recibir sus mensajes, envía un correo electrónico a altnet-hispan...@googlegroups.com.
Para publicar en este grupo, envía un correo electrónico a altnet-...@googlegroups.com.
Visita este grupo en http://groups.google.com/group/altnet-hispano.
Para acceder a más opciones, visita https://groups.google.com/d/optout.

[Gabriel Osorio]

Unas respuestas:
http://pipevilla2.blogspot.com/2011/06/manejo-eficiente-de-consecutivos-y.html

[César Pistiner]

Hola Edgar,

Te comento lo que hicimos nosotros frente a ese problema:

Primero te aviso que nuestra clave principal en la tabla es un GUID.

Para atacar el problema lo que hicimos fue manejarnos con numeradores, el cual se persiste en una tabla específica que va llevando la numeración de cada "entidad", en esta tabla se encuentra la numeración (último número) de varias cosas: factura, remitos, etc. Y básicamente lo que hacemos es cada vez que se va numerar, obtenemos el objeto específico (numerador de facturas en tu caso) pero lo obtenemos lockeando ese registro (te aclaro que usamos NHibernate nosotros el cual nos brinda esta funcionalidad). Esto nos permite asegurarnos del último número el cual antes de mandar a guardar lo incrementamos en 1.

Si también estas usando NH, este link te puede interesar. Actualmente no contamos con un gran nivel de concurrencia pero si te aseguras que el proceso de grabación sea corto no creo que tengas mayores problemas.

Si tenes un entorno similar te puedo compartir el modelo y el mapping.

Saludos,
César

--

[Edgar Ramos]

Cesar, no tengo un entorno similar, pero estamos portando la app a ese entorno, asi que tu modelo y mapping es de gran ayuda

Muchas gracias

--

Saludos

Edgar

[Carlos Peix]

+1 a la ajRecomendación y a lo que aporta el gran César, hincha de Temperley campeón!

----------------------------------
Carlos Peix

[Miguel Eduardo Román Martínez]

Que tal Edgar, solo para aportar un poco, en su momento me toco trabajar en un ambiente similar y lo que funcionó muy bien fue manejar (como bien lo dice aj) el identity de la base de datos y adicionalmente un campo de correlativo, sin embargo durante las pruebas de estrés nos topamos con problemas de performance y concurrencia con hacer un simple select del max y luego un insert

 

INSERT INTO tabla (correlativo, fecha) VALUES​ (SELECT MAX(correlativo) + 1 FROM tabla, date());

 

Entonces para mejorar el rendimiento y eliminar problemas de concurrencia, lo que hicimos fue llevar una tabla de correlativos a la cual dentro de una transacción se le hace un update con el último correlativo (para bloquear el registro) y por último hacer el insert con dicho correlativo.

 

BEGIN TRANSACTION

 

UPDATE correlativos SET correlativo = correlativo + 1 OUTPUT inserted.corrlativo INTO @nuevo_correlativo

 

INSERT INTO tabla (correlativo, fecha) values (@nuevo_correlativo, date());

 

COMMIT;

 

En su momento para el equipo de trabajo hice este gist https://gist.github.com/miguelerm/b17a47a20455f41d29fc (OJO: no es código funcional) solamente me sirvió para que el equipo comprendiera la idea general (el código que se implementó al final es un poco distinto al que se encuentra en el link). Nota: en nuestro caso incluso el requerimiento principal del negocio era que los correlativos se reiniciaran por empresa, año y mes.

 

No se si complicamos mucho nuestra solución :) por el momento nos funcionó de forma mas optima que con un select max(), podrías realizar tus propias pruebas para ver si nuestra solución puede adaptarse a tu escenario.

 

Espero que te ayude de algo nuestro caso.

 

Saludos,

Miguel

 

 

[César Pistiner]

Hola Edgar,

Espero que te sirva, le tuve que hacer adaptaciones así no se mezcla con mi entorno y queda más limpio para vos.

Esta es la clase Numerador:

public class Numerador : EntidadBase

{

private string _nombre;

private int _numeroActual = 0;

protected Numerador()

{

}

public Numerador(string nombre)

{

_nombre = nombre;

}

public string Nombre

{

get { return _nombre; }

}

public int NumeroActual

{

get { return _numeroActual; }

}

public virtual int ProximoNumero()

{

_numeroActual++;

return _numeroActual;

}

}

EntidadBase: Me aporta el Id del tipo GUID.

Este es el mapping (lo hacemo con archivos XML nosotros, pero hay otras formas):

<?xml version="1.0" encoding="utf-8" ?>

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"

assembly="Modelo" namespace="Modelo"

default-access="field.camelcase-underscore" default-lazy="false">

    <class name="Numerador">

        <id name="Id">

            <generator class="guid.comb"/>

        </id>

        <property name="Nombre"/>

        <property name="NumeroActual"/>

    </class>

</hibernate-mapping>

Como podrás ver el Numerador tiene un constructor donde se le pasa el "nombre" y por el cual harías la diferencia sobre lo que quieres manejar. A esta implementación tenés que sumarle que cada vez que obtienes un numerador debes asegurarte de lockear el registro correspondiente. Para eso nosotros usamos lo que se conoce como "repositorios" y la implementación sería algo así:

public Numerador ObtenerUno(System.Guid id, bool locked)

{

if (locked)

return Sesion.Get<Numerador>(id, LockMode.Upgrade);

else

return Sesion.Get<Numerador>(id);

}

Sesion: Contiene el currentSession. Nosotros usamos SessionPerAction Y para conocer un poco más sobre el tipo de lockeo que hago podes ver este link.

Y un ejemplo de uso en un Action del Controller sería algo así:

....

var idNumerador = ObtenerIdNumerador("FacturaCliente");

var numerador = _repositorioNumeradores.ObtenerUno(idNumerador, true);

factura.Numero = numerador.ProximoNumero();

....

_repositorioFacturas.Grabar(factura);

_repositorioNumeradores.Grabar(numerador);

...

Creo que no me olvido de nada, espero que te de una idea general de lo que hago. Te repito que actualmente no tenemos un gran nivel de concurrencia.

Nuestro querido Carlos Peix fue de gran ayuda en esta implementación, como así en tantas otras cosas que tenemos en nuestro proyecto. Él es el grande!

Espero que te sirva Edgar y cualquier consulta seguimos en contacto.

Saludos,

César

PD: Guarda con el GASOLERO eh!!!

[Edgar Ramos]

Gracias Cesar, eso fue de gran ayuda, por cierto no conocia esa forma de bloquear con nh, nuevamente muchas gracias

[Edgar Ramos]

Gente muchas gracias, su ayuda siempre es bienvenida

--

Saludos

Edgar

[Edgar Ramos]

Gente una consulta adicional, al no manejar este momento nhibernate, como me afrontar el testear desde c# y el tema de concurrencia, instrucciones como las sugeridas

----------------------

BEGIN TRANSACTION

 

UPDATE correlativos SET correlativo = correlativo + 1 OUTPUT inserted.corrlativo INTO @nuevo_correlativo

 

INSERT INTO tabla (correlativo, fecha) values (@nuevo_correlativo, date());

 

COMMIT;

----------------------

muchas gracias

--

Saludos

Edgar

[Miguel Eduardo Román Martínez]

que tal Edgar, si comprendo bien tu consulta, podes hacerlo de dos formas (asumiendo que estas usando MS SQL Server) y para acceso a datos estas utilizando algo como Dapper,

1. Podrías​ crear un procedimiento almacenado que incluya las instrucciones que indicas (asumamos que ese procedimiento se llama TABLA_INSERTAR) en ese procedimiento tendrías que declarar el parámetro @nuevo_correlativo como OUTPUT, ahora en C# algo como esto:

var p = new DynamicParameters();
p.Add("@nuevo_correlativo", dbType: DbType.Int32, direction: ParameterDirection.Output);

conexion.Execute("TABLA_INSERTAR", p, commandType: commandType.StoredProcedure);

2. sin usar procedimientos almacenados tendrías que ejecutar los dos comandos por separado:

using (var trx = new TransactionScope())

{

    using(var conexion = abrirConexion())

    {

        var p = new DynamicParameters();
        p.Add("@nuevo_correlativo", dbType: DbType.Int32, direction: ParameterDirection.Output);

        conexion.Execute("UPDATE.... todo lo demas", p);

        int nuevo_correlativo = p.Get<int>("@nuevo_correlativo");

        conexion.Execute("insert ...", new { nuevo_correlativo });

        trx.Complete();

    }

}

y una tercer forma (combinada con la primera) si después del insert colocas un SELECT @nuevo_correlativo, podrías invocarlo de la siguiente forma:

var nuevo_correlativo = conexion.Query<int>("TABLA_INSERTAR", commandType: commandType.StoredProcedure).Single();

Saludos,

Miguel

[Edgar Ramos]

Estimado Cesar, he utilizado tu implementacion para generar numeros unicos muchas gracias, pero tengo el siguiente inconveniente, al testear con nhibernate, yo abro dos sessions en cada una utilizo el numerador de la siguiente forma Sesion.Get<Numerador>(id, LockMode.Upgrade); hasta aqui todo bien, en cada session hago un flush de la misma y mi sorpresa es que al final cuando el proceso termina, los dos objetos factura creados tienen el mismo numero, de seguro hay algo que estoy haciendo mal.

Cualquier sugerencia es bienvenida

-------

public class Numerador : EntidadBase

{

private string _nombre;

private int _numeroActual = 0;

protected Numerador()

{

}

public Numerador(string nombre)

{

_nombre = nombre;

}

public string Nombre

{

get { return _nombre; }

}

public int NumeroActual

{

get { return _numeroActual; }

}

public virtual int ProximoNumero()

{

_numeroActual++;

return _numeroActual;

}

}

--------

<?xml version="1.0" encoding="utf-8" ?>

<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"

 assembly="Modelo" namespace="Modelo"

 default-access="field.camelcase-underscore" default-lazy="false">

    <class name="Numerador">

        <id name="Id">

            <generator class="guid.comb"/>

        </id>

        <property name="Nombre"/>

        <property name="NumeroActual"/>

    </class>

</hibernate-mapping>

----------

public Numerador ObtenerUno(System.Guid id, bool locked)

{

if (locked)

return Sesion.Get<Numerador>(id, LockMode.Upgrade);

else

return Sesion.Get<Numerador>(id);

}

-------

[César Pistiner]

Hola Edgar,

Me alegro que usaste esta implementación, con respecto a tu consulta ¿podrías compartir el test?

Recuerdo que nosotros luchamos un poco con eso también pero de ninguna manera nos duplicaba el número. Lo que sí nos pasaba era que si solamente utilizábamos las sesiones de NH nos bloqueaba al pedir el segundo número.

Para esto lo que hicimos fue simular realmente una concurrencia ejecutando los procesos de cada sesión en sus respectivos hilos, se puede lograr con la clase Thread.

Si no logras resolverlo avisame que con más tiempo te comparto la idea.

Espero que te sirva.

Saludos,

César

[Edgar Ramos]

Gracias Cesar, te cuento, cuando hice el test, esta utilizando session.flush, cambie a utilizar transaction de nhibernate y se comporto como pensaba, una vez que se lee en modo pesista una entidad esta es bloqueada, si en otra session es solicitado esa entidad esta recibe un timeout.

Me ayudaria mucho me expliques un poco mas acerca de crear hilos por session.

Muchas gracias

[César Pistiner]

Buen día Edgar,

Perdón por la demora, ando con poco tiempo disponible últimamente, para entender un poco de hilos podes empezar por acá y luego ir profundizando.

Para que entiendas este ejemplo que te doy a continuación lo que tenes que saber es que IIS cuando llega una petición designa un hilo para procesarla, por eso te comentaba que para hacer un ejemplo más real lo que hicimos fue utilizar esta características en los test.

Espero que comprendas que solamente te comparto el método de test no así toda la clase ya que me llevaría más tiempo quitar las dependencias para que funcione. Pero resolviendo _sesionFactory (sesión de NH) ya casi estaría para que lo puedas ejecutar en tu solución.

[TestMethod]

public void ConDosNumeradoresConcurrentes()

{

// arrange

var session1 = _sessionFactory.OpenSession();

session1.FlushMode = FlushMode.Commit;

session1.BeginTransaction();

var session2 = _sessionFactory.OpenSession();

session2.FlushMode = FlushMode.Commit;

session2.BeginTransaction();

var thread = new Thread(() => SegundoNumerador(session2));

// act

var numerador1 = session1.Get<Numerador>(_numerador.Id, LockMode.Upgrade);

thread.Start();

do

{

Thread.Sleep(10);

} while (!_comenzo);

Thread.Sleep(100);

_numeroNumerador1 = numerador1.ProximoNumero();

session1.Transaction.Commit();

thread.Join();

session2.Transaction.Commit();

session1.Dispose();

session2.Dispose();

// assert

Assert.AreEqual(2, _numeroNumerador2);

Assert.AreEqual(1, _numeroNumerador1);

}

private void SegundoNumerador(ISession session2)

{

_comenzo = true;

var numerador2 = session2.Get<Numerador>(_numerador.Id, LockMode.Upgrade);

_numeroNumerador2 = numerador2.ProximoNumero();

}

Espero que te sirva, y cualquier cosa seguimos avanzando!

Saludos,

César