jOOQ复杂更新 - 如何锁定表？

Question

这是一个嵌套集模型下的“添加节点”SQL查询：

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;   

并将其映射到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();

我该如何锁定表格？这样做必要吗？

谢谢

- Charis997

Answer 1

我不太确定你的各种查询在做什么，所以我不知道在你的情况下是否需要显式表锁定。但是我可以帮你处理语法。

从你的语法来看，我猜测你正在使用MySQL作为底层数据库。你至少有三个选项将你的SQL转换为jOOQ：

1. 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");
   }
   

2. 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();
   

3. Extend jOOQ and create your own org.jooq.Query for the LOCK TABLES and UNLOCK TABLES statements.

- Lukas Eder