CODEWITHSUNDEEP
javamysqljdbctransactions
Gotenks
Gotenks

Reputation: 377

Java and MySQL Transactions

I have a java app that runs on multiple computers and they all connect to the same MySQL database. I need transactions to make sure database is updated correctly.

I have a problem with releasing locks. Here is the code:

public boolean bookSeats(int numeroSala, Calendar dataOra, List<Posto> posti) {
    JDBConnection connection = JDBConnection.getDbConnection();
    Connection conn = connection.conn;
    java.sql.Date javaSqlDate = new java.sql.Date(dataOra.getTime().getTime());
    java.sql.Time javaSqlTime = new java.sql.Time(dataOra.getTime().getTime());

    try {
        conn.setAutoCommit(false);
        conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
    } catch (SQLException e) {
        e.printStackTrace();
    }

    try
    {
        Posto p = null;
        ListIterator<Posto> itr = posti.listIterator();
        PreparedStatement stmt = null;
        stmt = conn.prepareStatement(
            "INSERT INTO sala?_tickets " + 
            "(giornoSpettacolo, orarioSpettacolo, fila, posto) " + 
            "VALUES (?, ?, ?, ?)");

        stmt.setInt(1, numeroSala);
        while(itr.hasNext())
        {
            p = itr.next();
            stmt.setString(2, javaSqlDate.toString()); // giornoSpettacolo
            stmt.setString(3, javaSqlTime.toString()); // orarioSpettacolo
            stmt.setInt(4, p.getFila()); // fila
            stmt.setInt(5, p.getNumero()); // posto
            stmt.addBatch();
        }

        stmt.executeBatch();

        conn.commit();
        return true;
    }
    catch (SQLException e) {
        e.printStackTrace();
    }

    return false;
}

This method works fine but when I try to execute the this on another computer at the same time, I get a DEADLOCK error even though the conn.commit() was completed.

As soon as I close the first application, the other application can run the method without Deadlock error. It seems like the COMMIT doesn't release the lock?

Upvotes: 3

Views: 10134

Answers (4)

PeakGen
PeakGen

Reputation: 23025

Yes, close the connection as the first answer says. However, in addition, if you can put the above code into an "syncronized" method, it will make sure only one thread can access it at a time.

Apart from that, just have a look at the following link, which discuss about deadlock handling in JDBC

Deadlock detection in Java

Upvotes: 0

ESG
ESG

Reputation: 9425

Per MySQL documentation, about SERIALIZABLE:

This level is like REPEATABLE READ, but InnoDB implicitly converts all plain SELECT statements to SELECT ... LOCK IN SHARE MODE if autocommit is disabled.

You might want to try re-enabled auto-commit after your commit, or try Connection. REPEATABLE_READ instead.

Upvotes: 3

Edward Samson
Edward Samson

Reputation: 2425

You should always do a conn.close() in a finally block to ensure that any resources used by your method are released.

Upvotes: 0

Tolio
Tolio

Reputation: 1033

You can refer to this documentantion on how to do transactions with bare JDBC. Hope it helps.

Upvotes: 1

Related Questions