jOOQ complex update - how to lock the table?

here is a "add node" SQL query for nested set model

LOCK TABLE mytestdb.tbltree WRITE;

SELECT @myRight := rgt FROM mytestdb.tbltree
WHERE name = 'apples';

UPDATE mytestdb.tbltree SET rgt = rgt + 2 WHERE rgt > @myRight;
UPDATE mytestdb.tbltree SET lft = lft + 2 WHERE lft > @myRight;

INSERT INTO mytestdb.tbltree(name, lft, rgt) 
VALUES('beans', @myRight + 1, @myRight + 2);

UNLOCK TABLES;

and the mapping into jOOQ

Record record = create.select(Tbltree.RGT)
    .from(Tbltree.TBLTREE)
    .where(Tbltree.NAME.equal("apples"))
    .fetchOne();

int myright = record.getValue(Tbltree.RGT);

create.update(Tbltree.TBLTREE).set(Tbltree.RGT, Tbltree.RGT.add(2))
      .where(Tbltree.RGT.greaterThan(myright)).execute();

create.update(Tbltree.TBLTREE).set(Tbltree.LFT, Tbltree.LFT.add(2))
      .where(Tbltree.LFT.greaterThan(myright)).execute();

TbltreeRecord record2 = (TbltreeRecord) create 
    .insertInto(Tbltree.TBLTREE, Tbltree.NAME, Tbltree.LFT, Tbltree.RGT)
    .values("cherries",myright+1,myright+2)
    .returning(Tbltree.ID)
    .fetchOne();

How should I lock the table? Should I?

thx

Solution

I'm not quite sure what your various queries are doing, so I don't know whether explicit table locking is necessary in your case. But I can help you with the syntax.

From your syntax, I'm guessing that you're using MySQL as the underlying database. You have at least three options to translate your SQL into jOOQ:

Use the MySQL LOCK TABLES statement also in jOOQ:

try {
  create.execute("LOCK TABLES mytestdb.tbltree WRITE");
  // [...] your jOOQ code
}

// Be sure to unlock your tables again, in case of failure!
finally {
  create.execute("UNLOCK TABLES");
}

Use the FOR UPDATE clause, which is also specified by the SQL-1992 specification (for cursors). This solution might be a bit expensive, as MySQL will actually prepare a cursor for the Result:
```
create.selectFrom(TBLTREE).forUpdate().execute();
```
Extend jOOQ and create your own org.jooq.Query for the LOCK TABLES and UNLOCK TABLES statements.