Bug: why ladybug manual transactions is the partial commit?

[ericyuanhui]

Ladybug version

v0.16.1

What operating system are you using?

ubuntu24.04

What happened?

BEGIN TRANSACTION;
CREATE (:Person {id: 'p015', name: '测试用户', age: 25});
CREATE (:Department {id: 'd007', name: '测试部', headcount: 1});
COMMIT;

Examples showing the differences between two scenarios

Scenario A: The first statement succeeds, the second failsA manual transaction T1 is started via BEGIN.Creating the Person node succeeds, and the modification is recorded in T1 without being committed.Creating the Department node fails and triggers an exception.The transaction framework catches the exception and rolls back the current transaction.See lines 597 and 616 in client_context.cpp.
All changes within T1 (including the previously created Person node) are rolled back together.The subsequent COMMIT statement will throw the error "No active transaction".See line 57 in transaction.cpp.
As a result, the Person node does not exist. This is a standard full transaction rollback.

Scenario B: The first statement fails, the second succeedsTransaction T1 is started with BEGIN.The creation of the Person node fails first, and T1 is rolled back and cleared immediately.The connection reverts to auto-commit mode. See line 72 in transaction_context.cpp.
The script continues to execute the next CREATE Department statement. This statement runs in a brand-new auto-commit transaction and gets committed successfully.
No active transaction exists when executing COMMIT, which leads to an error.
Hence the existing Department node does not break transaction atomicity.The actual reason is that the script keeps executing subsequent statements after failure, and the following statement is no longer involved in the original transaction.

I think manual transaction should make sure all statement are All or nothing right?

Are there known steps to reproduce?

No response

[adsharma]

Can you demonstrate the problem with a test?

There is already a test fixture for this shape

The duplicate table error rolls back the first create, and CALL show_tables() returns zero rows.

What the code currently does:

• On any exception while preparing/executing a statement,
  ClientContext::TransactionHelper::runFuncInTransaction() calls context.rollback() at src/main/
  client_context.cpp:615.
• TransactionContext::rollback() rolls back the active transaction, then clearTransaction() removes it and
  switches the connection back to auto-commit at src/transaction/transaction_context.cpp:63 and src/transaction/
  transaction_context.cpp:72.
• The rollback itself undoes local storage and undo-buffer changes at src/transaction/transaction.cpp:81.

[adsharma]

Also note that in the 0.17.x (unreleased main branch) there is another change in behavior. We no longer write anything to the WAL on BEGIN tx. This avoids two fsync()s per transaction.

[ericyuanhui]

case A： I have two node table :person and department 。
persion table
lbug> MATCH (p:Person) RETURN p.id, p.name, p.age, p.department;
┌────────┬──────────────┬───────┬──────────────┐
│ p.id │ p.name │ p.age │ p.department │
│ STRING │ STRING │ INT64 │ STRING │
├────────┼──────────────┼───────┼──────────────┤
│ p001 │ 张三_updated │ 31 │ 研发部 │
│ p002 │ 李四 │ 28 │ 市场部 │
│ p003 │ 王五 │ 35 │ 产品部 │
│ p004 │ 赵六 │ 26 │ 研发部 │
│ p005 │ 钱七 │ 32 │ 市场部 │
│ p006 │ 孙八 │ 29 │ 产品部 │
│ p007 │ 周九 │ 27 │ 研发部 │
│ p008 │ 吴十 │ 33 │ 市场部 │
│ p009 │ 郑十一 │ 25 │ 产品部 │
│ p010 │ 王十二 │ 31 │ 研发部 │
│ p011 │ 王十二 │ 31 │ 研发部 │
└────────┴──────────────┴───────┴──────────────┘
department table
lbug> MATCH (d:Department) RETURN d.id, d.name, d.headcount;
┌────────┬────────┬─────────────┐
│ d.id │ d.name │ d.headcount │
│ STRING │ STRING │ INT64 │
├────────┼────────┼─────────────┤
│ d001 │ 研发部 │ 15 │
│ d002 │ 市场部 │ 10 │
│ d003 │ 产品部 │ 8 │
│ d004 │ 运维部 │ 5 │
│ d005 │ 财务部 │ 3 │
│ d006 │ 测试部 │ 1 │
│ d007 │ 测试部 │ 1 │
└────────┴────────┴─────────────┘
so when I send these trascation command :
BEGIN TRANSACTION;
CREATE (:Person {id: 'p015', name: '测试用户', age: 25});
CREATE (:Department {id: 'd007', name: '测试部', headcount: 1});
COMMIT;
person data p015 will success but department will fail . the manual trascation will rollback two table data. so they will keep same. I think it is right for manual trascation consistency .

But for case:B
when I send these trascation command :
BEGIN TRANSACTION;
CREATE (:Person {id: 'p011', name: '测试用户', age: 25});
CREATE (:Department {id: 'd008', name: '测试部', headcount: 1});
COMMIT;
the person data will fail but department will success. the manual trascation only rollback person table data. the department data will save and commit. it is not right trascation consistency for DB. Because postgres or mysql will keep manual trascation all to success or fail. not partial commit.

lbug> CREATE (:Person {id: 'p011', name: '测试用户', age: 25});
Error: Runtime exception: Found duplicated primary key value p011, which violates the uniqueness constraint of the primary key column.
lbug> CREATE (:Department {id: 'd008', name: '测试部', headcount: 1});
(0 tuples)
(0 columns)
Time: 0.22ms (compiling), 1.13ms (executing)
lbug> COMMIT;
Error: No active transaction for COMMIT.

lbug> MATCH (p:Person) RETURN p.id, p.name, p.age, p.department;
┌────────┬──────────────┬───────┬──────────────┐
│ p.id │ p.name │ p.age │ p.department │
│ STRING │ STRING │ INT64 │ STRING │
├────────┼──────────────┼───────┼──────────────┤
│ p001 │ 张三_updated │ 31 │ 研发部 │
│ p002 │ 李四 │ 28 │ 市场部 │
│ p003 │ 王五 │ 35 │ 产品部 │
│ p004 │ 赵六 │ 26 │ 研发部 │
│ p005 │ 钱七 │ 32 │ 市场部 │
│ p006 │ 孙八 │ 29 │ 产品部 │
│ p007 │ 周九 │ 27 │ 研发部 │
│ p008 │ 吴十 │ 33 │ 市场部 │
│ p009 │ 郑十一 │ 25 │ 产品部 │
│ p010 │ 王十二 │ 31 │ 研发部 │
│ p011 │ 王十二 │ 31 │ 研发部 │
└────────┴──────────────┴───────┴──────────────┘
(11 tuples)
(4 columns)
Time: 0.50ms (compiling), 0.55ms (executing)
lbug> MATCH (d:Department) RETURN d.id, d.name, d.headcount;
┌────────┬────────┬─────────────┐
│ d.id │ d.name │ d.headcount │
│ STRING │ STRING │ INT64 │
├────────┼────────┼─────────────┤
│ d001 │ 研发部 │ 15 │
│ d002 │ 市场部 │ 10 │
│ d003 │ 产品部 │ 8 │
│ d004 │ 运维部 │ 5 │
│ d005 │ 财务部 │ 3 │
│ d006 │ 测试部 │ 1 │
│ d007 │ 测试部 │ 1 │
│ d008 │ 测试部 │ 1 │
└────────┴────────┴─────────────┘
(8 tuples)
(3 columns)
Time: 0.36ms (compiling), 1.54ms (executing)

[adsharma]

This is not a bug in the query engine. More of a shell UI issue. If you paste the whole transaction block as a single statement, the behavior should be right.

The engine is aborting the manual transaction. The observed partial commit is caused by the shell continuing to
submit later statements after the transaction has already been aborted and cleared. For shell scripts or pasted
transaction blocks, the shell should stop processing the block/file after an error, or explicitly track an
aborted transaction block and suppress later statements until ROLLBACK/COMMIT.

Do you want to submit a fix for the shell so it becomes more stateful about transaction context?

[ericyuanhui]

This is not a bug in the query engine. More of a shell UI issue. If you paste the whole transaction block as a single statement, the behavior should be right.

The engine is aborting the manual transaction. The observed partial commit is caused by the shell continuing to
submit later statements after the transaction has already been aborted and cleared. For shell scripts or pasted
transaction blocks, the shell should stop processing the block/file after an error, or explicitly track an
aborted transaction block and suppress later statements until ROLLBACK/COMMIT.

Do you want to submit a fix for the shell so it becomes more stateful about transaction context?

ok I will try it by python api then fix shell issue. ok ?