hibernate locked table

Question

Why is this giving me a lock timeout:

for(int i = 0; i < playersCount ; i++) {
            StatUser stats = (StatUser) selectedUsers.get(i).getStatUsers().iterator().next();
            int gc = stats.getGamesPlayed();
            int gm = stats.getMakeCount();

            Session session = HibernateUtil.getSessionFactory().getCurrentSession();
            Transaction tx = session.beginTransaction();
            if(i == winnerIndex) {
                   stats.setGamesPlayed(gc++);
                   stats.setMakeCount(gm++);
                   session.update(stats);
                   session.flush();
                   tx.commit();
                   customTextBean.sendMail(selectedUsers.get(i).getEmail(), "Tillykke du har vundet");
            }
            else {
                stats.setGamesPlayed(gc++);
                session.update(stats);
                session.flush();
                tx.commit();
                customTextBean.sendMail(selectedUsers.get(i).getEmail(), "Tillykke " + winnersName + " skal lave kaffe");
            }
        }

Thank you for your help.

Answer 1

If you create a new transaction (session.beginTransaction();), then a new DB connection is created. So you have a transaction which has a read-lock on stats (from the for loop) and inside of that you try to write to the same row -> Deadlock.

To fix that, you first fetch all StatUsers with a second loop, close the first transaction and then iterate over the result in the code above. If you can't do that because you run out of memory, then Hibernate is no longer your friend and you must use custom SQL.

Other solutions: Use optimistic locking or read the data to be changed with custom SQL and instantiate the objects in the loop.