sql - repetible - problema de aislamiento de transacción o enfoque equivocado?

repeatable read

Question

Estaba ayudando a algunos colegas míos con un problema de SQL. Principalmente querían mover todas las filas de la tabla A a la tabla B (ambas tablas tenían las mismas columnas (nombres y tipos)). Aunque esto se hizo en Oracle 11g, no creo que realmente importe.

Su implementación inicial ingenua era algo así como

BEGIN
  INSERT INTO B SELECT * FROM A
  DELETE FROM A
  COMMIT;
END

Su preocupación era si se hacían INSERTES en la tabla A durante la copia de A a B y "ELIMINAR DE A" (o TRUNCATE por lo que valía) causaría la pérdida de datos (se eliminarían las filas insertadas más nuevas en A).

Por supuesto, rápidamente recomendé almacenar los ID de las filas copiadas en una tabla temporal y luego eliminar solo las filas en A que coinciden con los IDS en la tabla temporal.

Sin embargo, por curiosidad, hacemos una pequeña prueba al agregar un comando de espera (no recuerdo la sintaxis PL / SQL) entre INSERT y DELETE. A partir de una conexión diferente, insertaríamos filas DURANTE LA ESPERANZA .

Observamos que fue una pérdida de datos al hacerlo. Reproduje todo el contexto en SQL Server y lo envolví todo en una transacción, pero aún los nuevos datos nuevos también se perdieron en SQL Server. Esto me hizo pensar que hay un error / error sistemático en el enfoque inicial.

Sin embargo, no puedo decir si fue el hecho de que la TRANSACCIÓN no estaba (de alguna manera?) Aislada de los nuevos INSERT o el hecho de que los INSERT aparecían durante el comando WAIT.

Al final, se implementó usando la tabla temporal sugerida por mí, pero no pudimos obtener la respuesta a "Por qué la pérdida de datos". ¿Sabes por qué?

Answer 1

Alternativamente, puede usar el aislamiento de instantáneas para detectar actualizaciones perdidas:

Cuando el aislamiento de instantáneas ayuda y cuando duele

Answer 2

Debe establecer el nivel de aislamiento de su transacción para que las inserciones de otra transacción no afecten su transacción. No sé cómo hacer eso en Oracle.

Answer 3

Dependiendo de su nivel de aislamiento, seleccionar todas las filas de una tabla no evita nuevas inserciones, simplemente bloqueará las filas que lea. En SQL Server, si usa el nivel de aislamiento Serializable, evitará filas nuevas si se hubieran incluido en su consulta de selección.

http://msdn.microsoft.com/en-us/library/ms173763.aspx -

SERIALIZABLE Especifica lo siguiente:

• Las declaraciones no pueden leer datos que hayan sido modificados pero que aún no hayan sido comprometidos por otras transacciones.

• Ninguna otra transacción puede modificar los datos que ha leído la transacción actual hasta que se complete la transacción actual.

• Otras transacciones no pueden insertar nuevas filas con valores clave que corresponderían al rango de claves leídas por cualquier instrucción en la transacción actual hasta que se complete la transacción actual.

Answer 4

En Oracle, el nivel de aislamiento de transacción predeterminado se confirma por lectura. Eso básicamente significa que Oracle devuelve los resultados tal como existían en el SCN (número de cambio del sistema) cuando comenzó su consulta. Establecer el nivel de aislamiento de la transacción en serializable significa que la SCN se captura al inicio de la transacción, por lo que todas las consultas en la transacción devuelven datos a partir de esa SCN. Eso garantiza resultados consistentes independientemente de lo que otras sesiones y transacciones estén haciendo. Por otro lado, puede haber un costo en que Oracle puede determinar que no puede serializar su transacción debido a la actividad que otras transacciones están realizando, por lo que tendría que manejar ese tipo de error.

El enlace de Tony a la discusión de AskTom contiene más detalles sobre todo esto. Lo recomiendo encarecidamente.

Answer 5

Es solo la forma en que funcionan las transacciones. Debe elegir el nivel de aislamiento correcto para la tarea en cuestión.

Estás haciendo INSERTAR y ELIMINAR en la misma transacción. No mencionas el modo de aislamiento que está usando la transacción, pero probablemente sea ''read committed''. Esto significa que el comando DELETE verá los registros que fueron cometidos mientras tanto. Para este tipo de trabajo, es mucho mejor utilizar el tipo de transacción de "instantánea", ya que tanto INSERT como DELETE conocerían el mismo conjunto de registros, solo esos y nada más.

Answer 6

Este es el comportamiento estándar del modo predeterminado de lectura confirmada, como se mencionó anteriormente. El comando WAIT solo causa un retraso en el procesamiento, no hay ningún enlace para el manejo de transacciones DB.

Para solucionar el problema, puede:

1. establezca el nivel de aislamiento en serializable, pero luego puede obtener errores de ORA, que debe manejar con reintentos. Además, puede obtener un golpe de rendimiento serio.
2. use una tabla temporal para almacenar primero los valores
3. si los datos no son demasiado grandes para caber en la memoria, puede usar una cláusula RETURNING para BULK COLLECT INTO a una tabla anidada y eliminar solo si la fila está presente en la tabla anidada.

Answer 7

Esto se puede lograr en Oracle usando:

Alter session set isolation_level=serializable;

Esto se puede configurar en PL / SQL usando EXECUTE INMEDIATO:

BEGIN
    EXECUTE IMMEDIATE ''Alter session set isolation_level=serializable'';
    ...
END;

Consulte Preguntar a Tom: sobre niveles de aislamiento de transacciones

Answer 8

No puedo hablar de la estabilidad de la transacción, pero un enfoque alternativo sería eliminar el segundo paso de la tabla de origen donde existe (seleccionar identificadores de la tabla de destino).

Perdona la sintaxis, no he probado este código, pero deberías poder hacerte la idea:

INSERT INTO B SELECT * FROM A;

DELETE FROM A WHERE EXISTS (SELECT B.<primarykey> FROM B WHERE B.<primarykey> = A.<primarykey>);

De esta forma, está utilizando el motor relacional para exigir que no se eliminen datos más nuevos, y no necesita hacer los dos pasos en una transacción.

Actualización: sintaxis corregida en la subconsulta

Answer 9

Sí, Milán, no he especificado el nivel de aislamiento de la transacción. Supongo que es el nivel de aislamiento predeterminado que no sé cuál es. Ni en Oracle 11g ni en SQL Server 2005.

Además, el INSERT que se realizó durante el comando WAIT (en la segunda conexión) NO estaba dentro de una transacción. ¿Debería haber sido para evitar esta pérdida de datos?

Answer 10

no sé si esto es relevante, pero en SQL Server la sintaxis es

begin tran
....
commit

no solo ''comenzar''

Answer 11

    I have written a sample code:-

    First run this on Oracle DB:-


     Create table AccountBalance
        (
              id integer Primary Key,
              acctName varchar2(255) not null,
              acctBalance integer not null,
              bankName varchar2(255) not null
        );

        insert into AccountBalance values (1,''Test'',50000,''Bank-a'');

    Now run the below code 





 package com.java.transaction.dirtyread;
        import java.sql.Connection;
        import java.sql.DriverManager;
        import java.sql.SQLException;

        public class DirtyReadExample {

         /**
          * @param args
         * @throws ClassNotFoundException 
          * @throws SQLException 
          * @throws InterruptedException 
          */
         public static void main(String[] args) throws ClassNotFoundException, SQLException, InterruptedException {

             Class.forName("oracle.jdbc.driver.OracleDriver");
             Connection connectionPayment = DriverManager.getConnection(
                        "jdbc:oracle:thin:@localhost:1521:xe", "hr",
                        "hr");
             Connection connectionReader = DriverManager.getConnection(
                        "jdbc:oracle:thin:@localhost:1521:xe", "hr",
                        "hr");

          try {
              connectionPayment.setAutoCommit(false);
              connectionPayment.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);


          } catch (SQLException e) {
           e.printStackTrace();
          }


          Thread pymtThread=new Thread(new PaymentRunImpl(connectionPayment));
          Thread readerThread=new Thread(new ReaderRunImpl(connectionReader));

          pymtThread.start();
          Thread.sleep(2000);
          readerThread.start();

         }

        }



        package com.java.transaction.dirtyread;

        import java.sql.Connection;
        import java.sql.PreparedStatement;
        import java.sql.ResultSet;
        import java.sql.SQLException;

        public class ReaderRunImpl  implements Runnable{

         private Connection conn;

         private static final String QUERY="Select acctBalance from AccountBalance where id=1";

         public ReaderRunImpl(Connection conn){
          this.conn=conn;
         }

         @Override
         public void run() {
          PreparedStatement stmt =null; 
          ResultSet rs =null;

          try {
           stmt = conn.prepareStatement(QUERY);
           System.out.println("In Reader thread --->Statement Prepared");
           rs = stmt.executeQuery();
           System.out.println("In Reader thread --->executing");
           while (rs.next()){

            System.out.println("Balance is:" + rs.getDouble(1));

           }
           System.out.println("In Reader thread --->Statement Prepared");
           Thread.sleep(5000);
           stmt.close();
           rs.close();
           stmt = conn.prepareStatement(QUERY);
           rs = stmt.executeQuery();
           System.out.println("In Reader thread --->executing");
           while (rs.next()){

            System.out.println("Balance is:" + rs.getDouble(1));

           }
           stmt.close();
           rs.close();
           stmt = conn.prepareStatement(QUERY);
           rs = stmt.executeQuery();
           System.out.println("In Reader thread --->executing");
           while (rs.next()){

            System.out.println("Balance is:" + rs.getDouble(1));

           }
          } catch (SQLException | InterruptedException e) {
           e.printStackTrace();
          }finally{
           try {
            stmt.close();
            rs.close();
           } catch (SQLException e) {
            e.printStackTrace();
           }   
          }
         }

        }

        package com.java.transaction.dirtyread;
        import java.sql.Connection;
        import java.sql.PreparedStatement;
        import java.sql.SQLException;

        public class PaymentRunImpl implements Runnable{

         private Connection conn;

         private static final String QUERY1="Update AccountBalance set acctBalance=40000 where id=1";
         private static final String QUERY2="Update AccountBalance set acctBalance=30000 where id=1";
         private static final String QUERY3="Update AccountBalance set acctBalance=20000 where id=1";
         private static final String QUERY4="Update AccountBalance set acctBalance=10000 where id=1";

         public PaymentRunImpl(Connection conn){
          this.conn=conn;
         }

         @Override
         public void run() {
          PreparedStatement stmt = null;

          try {   
           stmt = conn.prepareStatement(QUERY1);
           stmt.execute();
           System.out.println("In Payment thread --> executed");
           Thread.sleep(3000);
           stmt = conn.prepareStatement(QUERY2);
           stmt.execute();
           System.out.println("In Payment thread --> executed");
           Thread.sleep(3000);
           stmt = conn.prepareStatement(QUERY3);
           stmt.execute();
           System.out.println("In Payment thread --> executed");
           stmt = conn.prepareStatement(QUERY4);
           stmt.execute();
           System.out.println("In Payment thread --> executed");

           Thread.sleep(5000);
            //case 1
           conn.rollback();
           System.out.println("In Payment thread --> rollback");
          //case 2
           //conn.commit();
          // System.out.println("In Payment thread --> commit");
          } catch (SQLException e) {
           e.printStackTrace();
          } catch (InterruptedException e) {    
           e.printStackTrace();
          }finally{
           try {
            stmt.close();
           } catch (SQLException e) {
            e.printStackTrace();
           }
          }
         }

        }

    Output:-
    In Payment thread --> executed
    In Reader thread --->Statement Prepared
    In Reader thread --->executing
    Balance is:50000.0
    In Reader thread --->Statement Prepared
    In Payment thread --> executed
    In Payment thread --> executed
    In Payment thread --> executed
    In Reader thread --->executing
    Balance is:50000.0
    In Reader thread --->executing
    Balance is:50000.0
    In Payment thread --> rollback

U puede probarlo insertando nuevas filas según lo define Oracle: - Una lectura fantasma ocurre cuando la transacción A recupera un conjunto de filas que satisfacen una condición dada, la transacción B subsecuentemente inserta o actualiza una fila de modo que la fila ahora cumple con la condición en la transacción A , y la transacción A más tarde repite la recuperación condicional. La transacción A ahora ve una fila adicional. Esta fila se conoce como un fantasma. Evitará el escenario anterior y también he usado TRANSACTION_SERIALIZABLE. Fijará el bloqueo más estricto en el Oracle. Oracle solo admite 2 tipos de niveles de aislamiento de transacción: TRANSACTION_READ_COMMITTED y TRANSACTION_SERIALIZABLE.