lock wait time out exceeded in java jdbc transaction

Question

I created this code for adding data for 3 tables. But I got an error, lock wait time out exceeded. In my database, I'm adding values for takerslist, q_enrolls and test tables. I'm getting some data from qBank and question tables. takerlist foreign key is stdId. I think it's not important here. q_enroll's foreign keys are qId(References question table), qBankId(references QBank) and testId(references test).

@Override
public int add(Test e, Connection connection) throws SQLException, ClassNotFoundException, RemoteException {
    String query = "INSERT INTO Test VALUES(?,?,?,?,?,?,?)";
    Object[] data = {e.getTestId(), e.getTestName(), e.getTestFrom(), e.getTestTo(), e.getTotalQuestions(), e.getTestDate(), e.getPassMark()};
    try {
        connection.setAutoCommit(false);
        int res = DBHandle.setData(connection, query, data);
        if (res > 0) {
            List<TakerList> list = e.getListTakers();
            TakerListManagementModel takerListModel = new TakerListManagementModel();
            QuestionEnrollManagementModel enrollModel = new QuestionEnrollManagementModel();
            takerListModel.setAddCommonBehavior(true);
            enrollModel.setAddCommonBehavior(true);
            List<List<BankQuestion>> selQs = null;
            String testId = null;
            for (TakerList tl : list) {
                int resTakers = takerListModel.performAdd(tl);
                if (resTakers > 0) {
                    selQs = e.getListQBanks();
                    testId = tl.getTestId();
                } else {
                    connection.rollback();
                    return 0;
                }
            }
            for (List<BankQuestion> list1 : selQs) {
                for (BankQuestion bankQuestion : list1) {
                    QuestionEnroll enroll = new QuestionEnroll(bankQuestion.getqId(), testId, bankQuestion.getqBankId());
                    int resQList = enrollModel.performAdd(enroll);
                    if (resQList==0) {
                        return 0;
                    }
                }
            }
            connection.commit();
            return 1;
        } else {
             connection.rollback();
            return 0;
        }
    } catch (SQLException ex) {
        connection.rollback();
        throw ex;
    } finally {
        connection.setAutoCommit(true);
    }
}

MySQL and I'm using mysql jdbc library – RSST Aug 28 '13 at 14:23 — RSST, Aug 28 '13 at 14:23

score 0 · Answer 1 · edited May 23 '17 at 12:05

0

You can set the lock timeout for your database explicitly (as probably your updates keep the transaction open for too long). For MySQL you can find the detailed instructions here

Basically you set it up with the command like this in /etc/my.cnf permanently with this line

[mysqld]
innodb_lock_wait_timeout=120

and restart mysql. If you cannot restart mysql at this time, run this:

SET GLOBAL innodb_lock_wait_timeout = 120;

You could also just set it for the duration of your session

SET innodb_lock_wait_timeout = 120;

edited May 23 '17 at 12:05

Community

1
1

answered Aug 28 '13 at 14:24

Kris

5,714
2
27
47

I've execute SET GLOBAL innodb_lock_wait_timeout = 120; and SET innodb_lock_wait_timeout = 120; in mySQL command line client. But i'm still in my problem – RSST Aug 28 '13 at 14:30
Maybe you need a bigger value? – Kris Aug 28 '13 at 14:31
I've set 1000; but still i'm in my problem – RSST Aug 28 '13 at 14:34
Also, if you want to use connection.setAutoCommit(true) use it before you execute your statements not in the finally block – Kris Aug 28 '13 at 14:34
@Kris He is restoring the auto commit status **after** he is done with his updates – Mark Rotteveel Aug 28 '13 at 14:36
I removed finally block. But i'm still in my problem – RSST Aug 28 '13 at 14:39
Mark R. you are right, haven't noticed earlier. @RSST have you tried the option with my.cnf and restarting you db? – Kris Aug 28 '13 at 15:03
Yes. I restarted. But after I removed testId foreign key, I't working well. If I added testId as a foreign key again, I got same error – RSST Aug 28 '13 at 15:11

lock wait time out exceeded in java jdbc transaction

1 Answers1