Problema concurrencia transacciones SQL SERVER
 

Buenas tardes,
Escribo este hilo, porque después de muchas horas investigando y leer bastantes hilos del foro, no doy con la solución.

Os explico el problema: tengo una función que reserva el código de una tabla (mira el más alto y devuelve el siguiente), para después yo poder hacer una serie de operaciones y luego insertar un registro en esa tabla con ese código de reserva. Para reservar ese código, lo que hago es un INSERT en esa tabla con el resto de campos vacíos que después rellenaré, para tener la seguridad de tener ese código reservado. La función devuelve True si ha podido realizar la reserva correctamente o False si ha dado algún error. Y el problema viene ahora, y es que varias veces me ha pasado que la función me ha devuelto el mismo código de reserva que otra reserva realizada previamente. No lo entiendo, porque si hago la reserva con un INSERT, ¿cómo es que no ha dado una excepción? En cambio me ha devuelto un código repetido y a True. Es como si el SQL SERVER no se hubiese enterado del INSERT. También lo he probado con los diferentes IsolationLevel de la conexión (ilCursorStability, ilReadUncommitted, ilReadCommitted, etc...) pero con el mismo resultado.

Os pongo el código por si veis algo que está mal.

Agradecería cualquier ayuda, sugerencia, lo que sea... Muchas gracias!

Código Delphi [-]
function TfrmPrincipal.ReservarCodigoAsiento(var Asiento: integer): boolean;
var
   qExec: TADOQuery;
   sql: string;
begin
   Result:=False;

   qExec:=TADOQuery.Create(Self);
   qExec.Connection:=Con001; //Asignamos conexión
   qExec.ParamCheck:=False;

   //Iniciamos transacción
   if Con001.InTransaction then Con001.RollbackTrans;
   Con001.BeginTrans;

   if Con001.InTransaction then
   begin
      try
         Asiento:=BuscarProximoCodigoAsiento(Date);
         //La clave primaria son los 3 campos CODIGO,CODIGO_LINEA,ANYO
         sql:='INSERT INTO TABLA (CODIGO,CODIGO_LINEA,ANYO, OTROS CAMPOS ....) VALUES('+
              IntToStr(Asiento)+','+
              '0,'+
              '2022',
              Los demás campos los dejo a blanco);
          qExec.Close;
          qExec.SQL.Text:=sql;
          qExec.ExecSQL;

          Con001.CommitTrans;

          Result:=True;
      except
         on E: Exception do
         begin
            Result:=False;
            if Con001.InTransaction then Con001.RollbackTrans;
         end;
      end;
   end;

   qExec.Close;
   qExec.Free;
end;

function TfrmPrincipal.BuscarProximoCodigoAsiento(FechaAsiento: TDateTime): integer;
var
   qExec: TADOQuery;
   sql: string;
begin
   Result:=2;
   qExec:=TADOQuery.Create(Self);
   qExec.Connection:=Con001;
   qExec.ParamCheck:=False;

   //Asignamos siguiente código de asiento
   sql:='SELECT  MAX(CODIGO) AS CODIGO '+
        'FROM    TABLA '+
        'WHERE  (ANYO = '+FormatDateTime('yyyy',FechaAsiento)+')';
   qExec.SQL.Text:=sql;
   qExec.Open;
   if not qExec.Eof then
   begin
      if not qExec.FieldByName('CODIGO').IsNull then
         Result:=qExec.FieldByName('CODIGO').AsInteger+1;
   end;

   qExec.Close;
   qExec.Free;
end;

No lo comentas (y es muy importante), pero imagino que tienes varios procesos/máquinas/aplicaciones pasando por ese punto. Si no es así lo siguiente olvídalo.

Yo creo que entre en SELECT y el INSERT, se te está colando un segundo proceso, de forma que antes de que el primer proceso haga el INSERT el segundo hace el SELECT (la consecuencia es que ambos obtienen el mismo número).

Haría unas pruebas con una simple aplicación que haga muchas peticiones y que puedas lanzar manualmente muchas veces para sobrecargar el sistema. Si tienes ese problema, al hacer uso intensivo obtendrás ese error de forma más clara.

En cuando al código de SELECT+INSERT yo usuaría IsolationLevel serializable (evita ejecuciones en paralelo, es decir es secuencial) ya que es la más restrictiva. Sólo debería usarse en casos muy puntuales y con sentencias SQL que tarden muy, muy poco tiempo (*NOTA1*).
Utiliza el mismo componente ADOQuery para ambas sentencias y asegúrate de que están dentro de la misma transacción.

*NOTA1*: Imagino que esa tabla tiene un índice único y rápido (clustered) por el campo CODIGO. Si ese SELECT tarda mucho vas a tener problemas de bloqueos. Si no es así busca otra manera, porque de siempre el MAX no ha sido la opción más rápida.

Primero, esos rollback que tienes infiltrados muy sospechosos. Eso es definitivamente un error. Lo normal es que una transaccion encapsula las internas, en vez de retroceder en CADA intento.

Segundo, lo que describes es fácil de explicar:

• Maquina 1 @ 10pm => SELECT...
• Maquina 2 @ 10::01pm => INSERT...

La BD esta andando correcto, y este es tu algoritmo. No hay como decir que el "código" que usas es el ULTIMO código entre TODOS los participantes. Lo que tienes es "el ultimo código en ESTE INSTANTE".

Es distinto!

La forma correcta es que al MOMENTO de solicitar el código te "adueñas de el" y le avanzas el contador. Maso así:

Código SQL [-]
UPDATE Codigos SET Codigo = Codigo + 1
OUTPUT INSERTED. Codigo
WHERE Id = @Id

La diferencia:

Tu algoritmo
Lo que quieres

Hola,

Yo también creo que lo mejor es tener un tabla de contadores separado para esta situación y para ejecutarlo podrias crear un stored procedure que haria todo el proceso en una sola llamada (sin transacciones):

Código SQL [-]

CREATE PROCEDURE [dbo].[ReservarCodigoAsiento] 
  @Anyo int, 
  @NumeroAsiento int OUTPUT
AS
BEGIN
  SET NOCOUNT ON;

  SET @NumeroAsiento = -1;
 
  UPDATE ContadorAsientos
  SET @NumeroAsiento = Contador = Contador + 1
  FROM ContadorAsientos C
  WHERE C.Anyo = @Anyo

  if @NumeroAsiento = -1
  BEGIN
    -- No existe contador para este año, lo creo
    SET @NumeroAsiento = 1
    INSERT INTO Contadores Values(@Anyo, @NumeroAsiento)
  END

  INSERT INTO TABLA (CODIGO, CODIGO_LINEA, ANYO, OTROS CAMPOS ....) 
    VALUES(@NumeroAsiento, 0, @Anyo, ...)

END

y desde Delphi podrias hacer una llamada al store procedure parecido a esto

Código Delphi [-]

  var Anyo: Integer := YearOf(Date);

  StoredProc.StoredProcName := 'ReservarCodigoAsiento';
  StoredProc.Params(ftInteger, -1, ptInput);
  StoredProc.Params(ftInteger, -1, ptOutput);

  StoredProc.Params[0].AsInteger := Anyo;

  StoredProc.ExecProc;

  var NumeroAsiento: Integer := StoredProc.Params[1].asInteger;

Muchas gracias por vuestra ayuda!! Le doy una vuelta a ver cómo lo enfoco, aunque la idea del StoredProcedure me parece interesante y puedo adaptarla fácilmente al código actual.

De todas formas, lo que no acabo de entender es por qué no da error (no salta la excepción) al intentar insertar un registro con la clave primaria duplicada.

Gracias y saludos!

Porque una transacción es una "imagen del mundo" en un momento determinado. Hasta que no confirmes la transacción, los demás seguirán viendo el mundo tal y como estaba, por muchos cambios que hagas en "tu mundo/transacción".
Ejemplo simplón:
ID=1;

Transacción 1: select ID -> 1 ; ID=5
Transacción 2: select ID -> 1 ; ID=3
Transacción 3: select ID -> 1 ; ID=28

ID sigue siendo 1 hasta que alguna transacción haga el commit;

Sí, pero justo después de ejecutar el INSERT hago el COMMIT de la transacción:

Código Delphi [-]
             
   qExec.Close;
   qExec.SQL.Text:=sql;
   qExec.ExecSQL;

   DataModule1.Con001.CommitTrans;

   Result:=True;
   except
      on E: Exception do ....

Tienes que pensar en TIEMPO. Las operaciones se ejecutan en tiempos distintos. Aun una diferencia de nanosegundos es suficiente para que la cosa sea como te pasa.

En otras palabras: Las transacciones son confirmaciones DEL PASADO. Solo veras EL PASADO de forma CONSISTENTE.

En el PRESENTE, todo esta asincrónico. Lo que hacen las transacciones es "converger" en un estado consistente al hacer "merge" de los diferentes estados de la bd hasta el momento de la transacción.