read_committed_snapshot - transaction sql définition




problème d'isolement de transaction ou mauvaise approche? (8)

J'aidais certains de mes collègues avec un problème SQL. Principalement, ils voulaient déplacer toutes les lignes de la table A à la table B (les deux tables ayant les mêmes colonnes (noms et types)). Bien que cela ait été fait dans Oracle 11g, je ne pense pas que ce soit vraiment important.

Leur mise en œuvre naïve initiale était quelque chose comme

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

Leur souci était de savoir s'il y avait des INSERTs dans la table A pendant la copie de A à B et que "DELETE FROM A" (ou TRUNCATE pour ce qui valait la peine) entraînerait une perte de données (les nouvelles lignes insérées dans A sont supprimées).

Bien sûr, j'ai rapidement recommandé de stocker les ID des lignes copiées dans une table temporaire, puis de supprimer uniquement les lignes de A correspondant à l'IDS dans la table temporaire.

Cependant, pour des raisons de curiosité, nous avons mis en place un petit test en ajoutant une commande d'attente (je ne me souviens pas de la syntaxe PL / SQL) entre INSERT et DELETE. Puis, à partir d'une connexion différente, nous insérerions des lignes pendant l'attente.

Nous avons observé que c'était une perte de données en le faisant. J'ai reproduit le contexte entier dans SQL Server et l'ai enveloppé tout dans une transaction mais les nouvelles données fraîches ont été perdues aussi dans SQL Server. Cela m'a fait penser qu'il y a une erreur systématique dans l'approche initiale.

Cependant je ne peux pas dire si c'était le fait que la TRANSACTION n'était pas (en quelque sorte?) Isolée des nouveaux INSERTs frais ou le fait que les INSERTs sont venus pendant la commande ATTENDRE.

En fin de compte, il a été implémenté en utilisant la table temporaire que j'avais suggérée mais nous n'avons pas pu trouver la réponse à "Pourquoi la perte de données". Est-ce que tu sais pourquoi?


C'est juste la façon dont les transactions fonctionnent. Vous devez choisir le niveau d'isolement correct pour la tâche en cours.

Vous faites INSERT et DELETE dans la même transaction. Vous ne mentionnez pas le mode d'isolation utilisé par la transaction, mais il s'agit probablement d'une "lecture validée". Cela signifie que la commande DELETE verra les enregistrements qui ont été commis entre-temps. Pour ce genre de travail, il est préférable d'utiliser une transaction de type "snapshot", car alors INSERT et DELETE connaîtraient le même ensemble d'enregistrements - seulement ceux-là et rien d'autre.


C'est le comportement standard du mode read-committed par défaut, comme mentionné ci-dessus. La commande WAIT provoque juste un retard dans le traitement, il n'y a pas de lien vers une gestion de transaction DB.

Pour résoudre le problème, vous pouvez soit:

  1. Définissez le niveau d'isolation sur sérialisable, mais vous pouvez alors obtenir des erreurs ORA, que vous devez gérer avec des tentatives! De plus, vous pourriez avoir un sérieux coup de performance.
  2. utiliser une table temporaire pour stocker les valeurs en premier
  3. Si les données ne sont pas trop volumineuses pour tenir dans la mémoire, vous pouvez utiliser une clause RETURNING pour BULK COLLECT INTO une table imbriquée et supprimer uniquement si la ligne est présente dans la table imbriquée.

Dans Oracle, le niveau d'isolation des transactions par défaut est read committed. Cela signifie essentiellement qu'Oracle renvoie les résultats tels qu'ils existaient au numéro SCN (numéro de modification du système) lorsque votre requête a démarré. Définir le niveau d'isolation de la transaction sur sérialisable signifie que le SCN est capturé au début de la transaction afin que toutes les requêtes de votre transaction renvoient des données à partir de ce SCN. Cela garantit des résultats cohérents indépendamment de ce que font les autres sessions et transactions. D'autre part, il peut y avoir un coût en ce que Oracle peut déterminer qu'il ne peut pas sérialiser votre transaction en raison de l'activité que d'autres transactions effectuent, de sorte que vous devrez gérer ce genre d'erreur.

Le lien de Tony à la discussion d'AskTom va à sensiblement plus de détail au sujet de tout ceci-- je le recommande fortement.


En fonction de votre niveau d'isolement, la sélection de toutes les lignes d'une table n'empêche pas les nouvelles insertions, elle verrouille simplement les lignes que vous avez lues. Dans SQL Server, si vous utilisez le niveau d'isolation Serializable, il empêchera les nouvelles lignes si elles avaient été incluses dans votre requête de sélection.

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

SERIALIZABLE Spécifie les éléments suivants:

  • Les instructions ne peuvent pas lire les données qui ont été modifiées mais pas encore validées par d'autres transactions.

  • Aucune autre transaction ne peut modifier les données qui ont été lues par la transaction en cours tant que la transaction en cours n'est pas terminée.

  • Les autres transactions ne peuvent pas insérer de nouvelles lignes avec des valeurs de clé qui tomberaient dans la plage de clés lues par les instructions de la transaction en cours jusqu'à la fin de la transaction en cours.


Je ne sais pas si cela est pertinent, mais dans SQL Server, la syntaxe est

begin tran
....
commit

pas seulement "commencer"


Oui Milan, je n'ai pas spécifié le niveau d'isolation de la transaction. Je suppose que c'est le niveau d'isolation par défaut que je ne connais pas. Ni dans Oracle 11g ni dans SQL Server 2005.

De plus, l'INSERT qui a été créé pendant la commande WAIT (sur la deuxième connexion) n'était PAS dans une transaction. Aurait-il fallu empêcher cette perte de données?



    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 peut le tester en insérant de nouvelles lignes comme défini par oracle: - Une lecture fantôme se produit lorsque la transaction A récupère un ensemble de lignes satisfaisant une condition donnée, la transaction B insère ou met à jour une ligne de telle sorte que la ligne remplit la condition dans la transaction A , et la transaction A répète plus tard la récupération conditionnelle. La transaction A voit maintenant une ligne supplémentaire. Cette rangée est appelée fantôme. Cela évitera le scénario ci-dessus et j'ai utilisé TRANSACTION_SERIALIZABLE. Il va définir le verrou le plus strict sur l'Oracle. Oracle ne prend en charge que deux types de niveaux d'isolation de transaction: - TRANSACTION_READ_COMMITTED et TRANSACTION_SERIALIZABLE.







database