COMMIT: transaction is aborted because of concurrency conflicts, will ROLLBACK instead

[swingbit]

Describe the bug
A wrong transaction conflict is detected when a simple script is executed while another session has simply opened a transaction (without doing anything else)

To Reproduce
On an empty database:

Session 1:

START TRANSACTION;

Session 2:

START TRANSACTION;
CREATE TABLE a(s string, i int, b1 bigint);
COMMIT;

START TRANSACTION;
ALTER TABLE a ADD UNIQUE (s, b1);
COMMIT;

START TRANSACTION;
CREATE TABLE b(i int);
UPDATE a SET i = 1;
DROP TABLE b;
COMMIT;

This produces:

COMMIT: transaction is aborted because of concurrency conflicts, will ROLLBACK instead

Expected behavior
No concurrency conflicts

Software versions

• MonetDB v11.41.13 (Jul2021-SP2)
• OS and version: Fedora 35
• Installed from release package

[kutsurak]

Do you know which of the four transactions fail? It used to be the case (but I am not sure if it still is in Jul2021-SP2) that transactions that affected the SQL catalog (i.e. and DDL statements) would conflict with any other write transactions.

[sjoerdmullender]

It's the last commit in session 2 that fails._

[PedroTadim]

This is more of a missing feature than a bug :) I can add the optimization to check for conflict only if the transaction started after the dependencies were committed. Another optimization is to not add dml dependencies if updates/inserts/deletes affect no rows, but that one is more tricky because how currently MAL plans are generated.

[yzchang]

I don't understand why this is "more of a missing feature"? Since session 1 doesn't do anything, I'd expect all transactions of session 2 to succeed.

After that, even session 1 should be able to commit (if it doesn't do anything conflicting in the meantime).

[PedroTadim]

Transanctions 2's changes cannot be garbage collected because session 1's transaction has not finished yet and it may conflict with those. I am running tests...

[swingbit]

Thanks for the fix.
This script is the minimal test that I found failing from a very long script (I just tried, the fix seems to work on the real scenario).
Changes such as removing parts of it, e.g. create / drop table b, or merging transactions into one, do not trigger the conflict.

So I still find it difficult to understand what specifically in this example created the conflict.

[PedroTadim]

The alter statement could conflict with the update, that's why. After the fix I can add more optimazations such as looking at empty insert/update/deletes, do a column grained dependency check instead at table level. This is not optimized yet because DDL statements are rare, usually only used during table creation, so it has low priority.

[swingbit]

I see, thanks.

[yzchang]

The transactions in Session 2 shouldn't conflict with each other because they are executed sequentially, right?

If the transaction of Session 1 turned out to conflict with the transactions in Session 2 later, shouldn't the transaction of Session 1 be the one aborted?

So, I still don't understand why the alter statement would conflict with the update? Thanks

[PedroTadim]

Session 1's transaction can still be aborted. The issue was I was not checking the timing of the transactions, ie if a transaction started after a finished one, it will never conflict. Currently the conflict checks are made for the whole table, so we can improve this in many ways. However as DDL statements are rare, I don't expect this to be an urgent issue.