Как вставить запись в базу данных в потоках Java?

Question

Я пытаюсь разобраться с многогранностью в Java.

Я прочитал много статей и вопросов (здесь, на Stackru), но не нашел четких примеров, как его использовать.

У меня есть таблица Unique_Numbers в базе данных HsqlDB. Есть 2 столбца: NUMBER и QTY. Моя задача - проверить, существует ли номер, и увеличить QTY номера, если да, и вставить этот номер, если нет.

Итак, что я получил.

Это моя конфигурация базы данных

private final ComboPooledDataSource dataSource;

public Database(String url, String userName, String password) throws PropertyVetoException {
    dataSource = new ComboPooledDataSource();
    dataSource.setDriverClass("org.hsqldb.jdbcDriver");
    dataSource.setJdbcUrl(url);
    dataSource.setUser(userName);
    dataSource.setPassword(password);
    dataSource.setMaxPoolSize(10);
    dataSource.setMaxStatements(180);
    dataSource.setMinPoolSize(5);
    dataSource.setAcquireIncrement(5);
}

Это моя логика:

public void insertRow(String number) throws SQLException {
    int cnt = getCount(number);
    if (cnt == 0) {
        insert(number);
    } else if (cnt > 0) {
        update(number);
    }
}

Получить количество в таблице

private int getCount(String number) {
        int cnt = 0;
        String sql = "select count(number) as cnt from \"PUBLIC\".UNIQUE_NUMBER where number='" + number + "'";
        try {
            Statement sta;
            try (Connection connection = dataSource.getConnection()) {
                sta = connection.createStatement();
                ResultSet rs = sta.executeQuery(sql);
                if (rs.next()) {
                    cnt = rs.getInt("cnt");
                }
            }
            sta.close();
        } catch (Exception e) {
            LOGGER.error("error select cnt by number" + e.toString());
        }

        return cnt;
    }

Вставить и обновить

private boolean insert(String number) throws SQLException {
    String sql = "insert into \"PUBLIC\".UNIQUE_NUMBER (number, qty) values(?, ?)";
    try (Connection connection = dataSource.getConnection()) {
        connection.setAutoCommit(false);
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setString(1, number);
            ps.setInt(2, 0);
            ps.addBatch();
            ps.executeBatch();
            try {
                connection.commit();
            } catch (Exception e) {
                connection.rollback();
                LOGGER.error(e.toString());
                return false;
            }
        }
    }
    return true;
}

private boolean update(String number) throws SQLException {
    String sql = "update \"PUBLIC\".UNIQUE_NUMBER set (qty) = (?) where number = ?";
    int qty = selectQtyByNumber(number) + 1;
    try (Connection connection = dataSource.getConnection()) {
        connection.setAutoCommit(false);
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setInt(1, qty);
            ps.setString(2, number);
            ps.executeUpdate();
            try {
                connection.commit();
            } catch (Exception e) {
                connection.rollback();
                LOGGER.error(e.toString());
                return false;
            }
        }
    }
    return true;
}

Пока я читаю, я должен использовать Pool Connection. Важно дать одно соединение каждому потоку. Когда я запускаю свое приложение, я получаю исключение или исключение ограничения с Rollback: сериализация не удалась.

Что я делаю неправильно?

Вот мои логи

[INFO] [generate38] ERROR se.homework.hwbs.tasks.un.server.threads.InsertRowThread - exception while inserting numberintegrity constraint violation: check constraint; SYS_CT_10114 table: UNIQUE_NUMBER

[INFO] [generate38] ERROR se.homework.hwbs.tasks.un.server.database.Database - error select cnt by number java.sql.SQLTransactionRollbackException: transaction rollback: serialization failure
[INFO] [generate38] ERROR se.homework.hwbs.tasks.un.server.threads.InsertRowThread - exception while inserting numbertransaction rollback: serialization failure

[INFO] [generate38] ERROR se.homework.hwbs.tasks.un.server.database.Database - error select cnt by number java.sql.SQLTransactionRollbackException: transactionrollback: serialization failure

Answer 1

нетранзакционный способ

Делать прирост первым

update UNIQUE_NUMBER set qty = qty + 1 where number = ?

Проверьте, обновил ли он какую-либо строку, введите номер, если он не обновил

int rowsMatched = ps.executeUpdate();
if(rowsMatched == 0) {
    try {
        insert into UNIQUE_NUMBER (number, qty) values(?, 0)
    } catch(Exception e) {
        // the insert will fail if another thread has already
        // inserted the same number. check if that's the case
        // and if so, increment instead.
        if(isCauseUniqueConstraint(e)) {
            update UNIQUE_NUMBER set qty = qty + 1 where number = ?
        } else {throw e;}
    }
}

Нет обработки транзакций (setAutoCommit(false), commit() или же rollback()) требуется.

транзакционный путь

Если вы все еще хотите сделать это транзакционным способом, вам нужно выполнить все шаги в пределах одной транзакции, как предложено @EJP:

connection.setAutoCommit(false);
// check if number exists
// increment if it does
// insert if it doesn't
// commit, rollback & repeat in case of error
connection.setAutoCommit(true);

Установите для автоматической фиксации обратно значение true, если этот код использует общий пул соединений с другим кодом (так как это состояние по умолчанию, в котором другие будут ожидать подключения), или дайте понять, что соединения в пуле всегда будут в транзакционном режиме.

В вашем коде getCount иногда получает соединение в режиме автоматической фиксации (первое использование), а иногда получает соединение в транзакционном режиме (повторно используется после insert и / или update) - вот почему вы видите исключения отката в getCount,