Transakcje JDBC z użyciem bazy H2 i MySQL


Transakcje JDBC z użyciem bazy H2 i MySQL

Transakcja to zbiór operacji, które zmieniają stan systemu. Wykonanie wyłącznie wszystkich kroków wchodzących w skład transakcji może być uznane za sukces (z ang. commit). Jeśli któryś z kroków się nie powiedzie, należy wycofać (z ang. rollback) wszystkie zmiany dokonane w ramach danej transakcji. Książkowym przykładem transakcji może być transakcja bankowa jaką jest przelew. Muszą tu zostać dokonane dwie operacje – obniżenie konta nadawcy oraz podbicie sumy konta odbiorcy. W przypadku wystąpienia błędu żadna z tych operacji nie może być zatwierdzona.

Zasady ACID (anagram) to zbiór zasad które opisują transakcję

  • Atomowość – wykonanie wszystkich kroków wchodzących w skład transakcji decyduje o jej sukcesie,
  • Spójność – stan bazy danych zawsze przedstawia stan przed lub po transakcji,
  • Izolacja – transakcja jest odizolowana od innych transakcji, działa niezależnie od pozostałych,
  • Trwałość – w przypadku awarii systemu bazodanowego, transakcja albo wykonana jest w całości albo wcale.

transaction management

[źródło] https://www.sitesbay.com/sql/sql-tcl-command

Systemy baz danych zazwyczaj realizują jednocześnie więcej niż jedną transakcję. Aby zapewnić kontrolę nad tym procesem wprowadzono tzw. poziomy izolacji, poprzez które określa się zasady równoległej realizacji kilku transakcji. Izolacja transakcji jest istotna ze względu na możliwość wystąpienia anomalii:

  • Brudny odczyt (z ang. Dirty Reads):

Transakcja A rozpoczyna się i wywołuje:

SELECT * FROM Order

W tym samym czasie transakcja B wykonuje:

UPDATE Order
SET order_amount=10
WHERE order_id =1

Druga transakcja może odczytać dane które nie są jeszcze zatwierdzone przez pierwszą transakcję.

  • Niepowtarzalny odczyt (z ang. Non-Repeatable Reads):

Transakcja A rozpoczyna się i wywołuje:

SELECT * FROM Order where order_id = 1

W tym samym czasie transakcja B wykonuje:

UPDATE Order
SET order_amount =10
WHERE order_id = 1;

COMMIT;

Dwukrotne wykonanie zapytania SELECT może zwracać różne wyniki co spowodowane jest modyfikacją rekordu przez przez drugą transakcję.

  • Fantomowe odczyty (z ang. Phantom Reads):

Transakcja A rozpoczyna się i wywołuje:

SELECT * FROM Order where order_id = 1

Transakcja B w tym samym czasie wstawia nowy rekord:

INSERT INTO Order
(order_id, order_name)
VALUES ('140', 'milk');

COMMIT;

Dwukrotne wykonanie zapytania SELECT może zwracać nadmiarowe wyniki co spowodowane jest wstawieniem nowego rekordu przez drugą transakcję.

Transakcje JDBC

JDBC udostępnia kilka różnych poziomów izolacji poszczególnych transakcji. Pozwala to na określenie, jak bardzo poszczególne transakcje mają być oddzielone od siebie. Poziom transakcji jest ustawiany w ramach pojedynczego połączenia z bazą danych! Im wyższy poziom izolacji transakcji tym mniejsza wydajność operacji na bazie danych.

Poziomy izolacji transakcji w JDBC

TRANSACTION_NONE – brak transakcji,

TRANSACTION_READ_UNCOMMITTED – dopuszcza odczyt danych przed wywołaniem metody commit(),

TRANSACTION_READ_COMMITTED – inne transakcje nie mogą odczytywać zmienionych wierszy przed wywołaniem metody commit() (dirty reads)  – domyślnie baza H2 (http://www.h2database.com/html/advanced.html),

TRANSACTION_REPEATABLE_READ – dodatkowo chroni przed sytuacją gdy transakcja odczytuje wiersz, druga transakcja go zmienia a pierwsza ponownie go odczytuje otrzymując inne dane (non-repetable reads) – domyślnie baza MySQL
(https://dev.mysql.com/doc/refman/8.0/en/innodb-transaction-isolation-levels.html),

TRANSACTION_SERIALIZABLE – dodatkowo chroni przed sytuacją, gdy jedna transakcja odczytuje zbiór wierszy spełniający kryteria zawarte w warunku WHERE, następnie druga transakcja wstawia wiersz spełniający ten warunek, po czym pierwsza transakcja ponownie odczytuje zbiór wierszy dostając nowy rekord (phantom-read).

  • Testy z użyciem bazy H2

Znalezione obrazy dla zapytania h2 logo database

Wersja bazy danych – plik pom.xml:

<dependencies>
      <dependency>
          <groupId>com.h2database</groupId>
          <artifactId>h2</artifactId>
          <version>1.3.148</version>
      </dependency>
</dependencies>

Klasa ConnectionManager – ustanowienie połączenia:

public class ConnectionManager {

    public static Connection getConnectionManager() throws SQLException {

        Properties myProp = new Properties();
        myProp.put("user"    , "sa");
        myProp.put("password", "");

       return DriverManager.getConnection("jdbc:h2:~/test", myProp);
    }

    public static void closeConnection(Connection connection) throws SQLException {
        connection.close();
    }
}

Inicjalizowanie przykładowej tabeli users do testów:

public class InitDatabaseManager {

    private static final String CREATE_TABLE_SQL =
            "CREATE TABLE IF NOT EXISTS users ("
            + "ID INT NOT NULL AUTO_INCREMENT,"
            + "EMAIL VARCHAR(45) NOT NULL,"
            + "PRIMARY KEY (ID))";

    private static final String DROP_TABLE_USERS = "drop table if exists users;";

    public static void initStarterTable (Connection connection) throws SQLException {
        Statement stmt = connection.createStatement();
        stmt.executeUpdate(DROP_TABLE_USERS);
        stmt.executeUpdate(CREATE_TABLE_SQL);
    }
}

Przykładowa klasa do operacji CRUD:

public class CRUD {

    private static void printAllUsers(Connection connection) throws SQLException {
        String sql     = "SELECT * FROM users";
        Statement stmt = connection.createStatement();
        ResultSet rs   = stmt.executeQuery(sql);
        while(rs.next()){
            String email  = rs.getString("email");
            System.out.print("email: " + email + System.lineSeparator());
        }
    }

    public static void getAllUsers(Connection connection) throws SQLException {
        System.out.println("[SELECT]");
        printAllUsers(connection);
    }

    public static void insertUsers(Connection connection) throws SQLException {
        System.out.println("[INSERT]");
        String sqlInsert1 = "INSERT INTO users (email) VALUES ('user@mail.com');";
        PreparedStatement psInsert = connection.prepareStatement(sqlInsert1);
        psInsert.execute();
    }

}

Kluczowa klasa testująca:

Poziomy izolacji ustawia się metodą setTransactionIsolation() wywołaną na rzecz obiektu klasy Connection.

public class StartUseCases {

    private static Connection connection;
    private static Connection connection2;

    public StartUseCases() {
        try {

            connection = ConnectionManager.getConnectionManager();
            connection.setAutoCommit(false);
            connection.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);

            connection2 = ConnectionManager.getConnectionManager();
            connection2.setAutoCommit(false);
            connection2.setTransactionIsolation(Connection.TRANSACTION_READ_UNCOMMITTED);

            InitDatabaseManager.initStarterTable(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws SQLException {

        new StartUseCases();
        CRUD.insertUsers(connection2);
        CRUD.getAllUsers(connection);
    }
}

Dla poziomu izolacji transakcji TRANSACTION_READ_UNCOMMITTED wynik programu jest następujący:

[INSERT]
[SELECT]
email: user@mail.com

co oznacza, że druga transakcja odczytała dane które zostały niezatwierdzone przez pierwszą transakcję!

Zmieńmy poziom izolacji na TRANSACTION_READ_COMMITTED:

public class StartUseCases {

    private static Connection connection;
    private static Connection connection2;

    public StartUseCases() {
        try {

            connection = ConnectionManager.getConnectionManager();
            connection.setAutoCommit(false);
            connection.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

            connection2 = ConnectionManager.getConnectionManager();
            connection2.setAutoCommit(false);
            connection2.setTransactionIsolation(Connection.TRANSACTION_READ_COMMITTED);

            InitDatabaseManager.initStarterTable(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws SQLException {

        new StartUseCases();
        CRUD.insertUsers(connection2);
        CRUD.getAllUsers(connection);
    }
}

Dla poziomu izolacji transakcji TRANSACTION_READ_COMMITTED wynik programu jest następujący:

[INSERT]
[SELECT]
Exception in thread "main" org.h2.jdbc.JdbcSQLException: Timeout trying to lock table "USERS"; SQL statement:
SELECT * FROM users [50200-148]
	at org.h2.message.DbException.getJdbcSQLException(DbException.java:327)
	at org.h2.message.DbException.get(DbException.java:167)
	at org.h2.message.DbException.get(DbException.java:144)
	at org.h2.table.RegularTable.doLock(RegularTable.java:499)
	at org.h2.table.RegularTable.lock(RegularTable.java:433)
	at org.h2.table.TableFilter.lock(TableFilter.java:140)
	at org.h2.command.dml.Select.queryWithoutCache(Select.java:571)
	at org.h2.command.dml.Query.query(Query.java:257)
	at org.h2.command.dml.Query.query(Query.java:227)
	at org.h2.command.CommandContainer.query(CommandContainer.java:78)
	at org.h2.command.Command.executeQuery(Command.java:131)
	at org.h2.jdbc.JdbcStatement.executeQuery(JdbcStatement.java:77)
	at pl.edusession.db.operations.CRUD.printAllUsers(CRUD.java:10)
	at pl.edusession.db.operations.CRUD.getAllUsers(CRUD.java:19)
	at pl.edusession.StartUseCases.main(StartUseCases.java:35)

Co oznacza, że baza H2 w wersji 1.3.148 zachowuje się nieprawidłowo dla poziomu izolacji TRANSACTION_READ_COMMITTED.

Zmieńmy wersję na:

<dependencies>
       <dependency>
           <groupId>com.h2database</groupId>
           <artifactId>h2</artifactId>
           <version>1.4.199</version>
       </dependency>
</dependencies>

Wynik dla poziomu izolacji transakcji TRANSACTION_READ_COMMITTED jest teraz następujący:

[INSERT]
[SELECT]

Co oznacza, że w wersji 4 nie ma błędu:

Exception in thread "main" org.h2.jdbc.JdbcSQLException: Timeout trying to lock table "USERS"; SQL statement:

i program zachowuje się prawidłowo – czyli żeby zobaczyć zmiany jednej transakcji przez inną transakcję to muszą być one zatwierdzone poleceniem:

connection.commit();

A co w przypadku TRANSACTION_READ_UNCOMMITTED? Wynik programu jest następujący:

[INSERT]
[SELECT]

Co oznacza, że poziom izolacji transakcji nie działa prawidłowo bo dane które nie są zatwierdzone przez jedną transakcje nie są widoczne dla innych transakcji. 

Po więcej zachęcam do dyskusji:

https://stackoverflow.com/questions/58201324/h2-transactions-always-automatically-commit-why/58202597

  • Testy z użyciem bazy MySQL

Podobny obraz

Dla poziomu izolacji transakcji TRANSACTION_READ_UNCOMMITTED wynik programu jest następujący:

[INSERT]
[SELECT]
email: user@mail.com

co oznacza, że druga transakcja odczytała dane które zostały niezatwierdzone przez pierwszą transakcję!

Zmieńmy poziom izolacji na TRANSACTION_READ_COMMITTED:

[INSERT]
[SELECT]

co oznacza, że dane które nie zostały zatwierdzone nie są odczytane przez inna transkację – jest ok! Dopiero po zatwierdzeniu transakcji:

public static void insertUsers(Connection connection) throws SQLException {

    System.out.println("[INSERT]");

    String sqlInsert1 = "INSERT INTO users (email) VALUES ('user@mail.com');";
    PreparedStatement psInsert = connection.prepareStatement(sqlInsert1);
    psInsert.execute();

    connection.commit();
}

dane są odczytane:

[INSERT]
[SELECT]
email: user@mail.com

Sprawdźmy jak zachowa się parametr TRANSACTION_REPEATABLE_READ dla bazy MySQL:

W poniżej zamieszczonej metodzie w jednej transakcji odczytujemy wszystkich użytkowników, czekamy 5 sekund a następnie odczytujemy użytkowników ponownie:

public static void getAllUsers(Connection connection) throws SQLException {
    try
    {
        System.out.println("start first read");
        printAllUsers(connection);
        System.out.println("end first read");

        sleep(5000);
        System.out.println("start second read");
        printAllUsers(connection);
        System.out.println("end second read");
    }
    catch(Exception e)
    {
        connection.rollback();
    }
}

W poniżej zamieszczonej metodzie ustawiam poziom izolacji transakcji na TRANSACTION_REPEATABLE_READ oraz w jednym wątku wyświetlam wszystkich użytkowników z użyciem metody getAllUsers() a w drugim modyfikuję użytkownika który odczytywany jest w innej transakcji:

private static void startTransactionRepeatableRead() throws SQLException {
    connection.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
    connection2.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);

    CRUD.insertUsers(connection);

    Runnable r1 = new Runnable() {
        public void run() {
            try {
                CRUD.getAllUsers(connection2);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    };

    Runnable r2 = new Runnable() {
        public void run() {
            try {
                CRUD.updateUsers(connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    };

    new Thread(r1, "Thread-1").start();
    new Thread(r2, "Thread-2").start();

}

Metoda update czeka 5 sekund aby inna transakcja miała możliwość odczytu rekordu zanim zostanie on zmodyfikowany:

public static void updateUsers(Connection connection) throws SQLException {
    try {
        sleep(5000);
    } catch (InterruptedException e) {
        e.printStackTrace();
    }
    System.out.println("[UPDATE]");
    String updateSqlRowToUpdate = "UPDATE users SET email = 'example-update@example.com' WHERE email = 'user@mail.com'";

    Statement stmt = connection.createStatement();
    stmt.executeUpdate(updateSqlRowToUpdate);

    connection.commit();
}

Wynik:

[INSERT]
start first read
email: user@mail.com
end first read
[UPDATE]
start second read
email: user@mail.com
end second read

Oznacza to, że drugi odczyt zwrócił ten sam wynik mimo, że inna transakcja w między czasie go zmodyfikowała! Jest ok! Zmieńmy teraz poziom izolacji transakcji dla tego przykładu na TRANSACTION_READ_COMMITTED.

Wynik:

[INSERT]
start first read
email: user@mail.com
end first read
[UPDATE]
start second read
email: example-update@example.com
end second read

Oznacza to, że drugi odczyt zwrócił wynik który został zmodyfikowany przez inną transakcję czyli mamy anomalię – druga transakcja może odczytać dane które zostaly zmodyfikowane przez inną transakcję! A co w przypadku operacji Insert dla poziomu izolacji TRANSACTION_REPEATABLE_READ?

public static void insertUsers(Connection connection) throws SQLException {
    try {
        sleep(5000);
    } catch (InterruptedException e) {
        e.printStackTrace();
    }
    System.out.println("[INSERT]");
    String sqlInsert1 = "INSERT INTO users (email) VALUES ('user@mail.com');";
    PreparedStatement psInsert = connection.prepareStatement(sqlInsert1);
    psInsert.execute();
    connection.commit();
}
private static void startTransactionSerializable() throws SQLException {

    connection.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
    connection2.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
    
    Runnable r1 = new Runnable() {
        public void run() {
            try {
                CRUD.getAllUsers(connection2);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    };
    
    Runnable r2 = new Runnable() {
        public void run() {
            try {
                CRUD.insertUsers(connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
    };
    new Thread(r1, "Thread-1").start();
    new Thread(r2, "Thread-2").start();
}

 Wynik – brak nadmiarowych rekordów!

start first read
end first read
[INSERT]
start second read
end second read

Co oznacza, że w bazie MySQL poziom izolacji TRANSACTION_REPEATABLE_READ chroni przed anomalią Phantom records!

“This is more restrictive than the standard description of the isolation level. No phantom reads… only phantom writes!”

[źródło]https://blog.pythian.com/understanding-mysql-isolation-levels-repeatable-read/

Jeśli chcesz dowiedzieć się więcej w temacie transakcji to zapraszam  do wpisu który opisuje transakcje z użyciem JPA:

https://javaleader.pl/2019/10/04/transkacje-jpa/

Kod źródłowy do wglądu na GitHub!

Jeśli chcesz uzyskać dostęp do GitHuba na 30 dni i pobrać kod źródłowy wyślij smsa o treśći DOSTEP.EDUSESSION na numer 7943. Tyle wiedzy a koszt to tylko 9 PLN (11.07 PLN z VAT).





Leave a comment

Your email address will not be published.


*