Java and MySQL Transactions

Question

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?

score 3 · Accepted Answer · answered May 11 '12 at 01:41

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.

score 1 · Answer 2 · answered May 11 '12 at 01:38

1

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

answered May 11 '12 at 01:38

Tolio

1,023
13
30

score 0 · Answer 3 · answered May 11 '12 at 01:40

0

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

answered May 11 '12 at 01:40

Edward Samson

2,395
2
26
39

score 0 · Answer 4 · edited May 23 '17 at 11:58

0

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

edited May 23 '17 at 11:58

Community

1
1

answered May 11 '12 at 07:19

PeakGen

21,894
86
261
463

Java and MySQL Transactions

4 Answers4