bug: Multi-thread recreate view gives error

Search before asking

[X] I had searched in the issues and found no similar issues.

Version

v1.2.597-nightly

What's Wrong?

When calling DROP/RECREATE same view in multiple threads, receive error

APIError: ResponseError with 2314: Drop table 'v' with drop_on time
[SQL: DROP VIEW IF EXISTS v]

How to Reproduce?

Python code to reproduce, spawn 30 threads here, but the number is required to cause the collision.


import sqlalchemy as sa
from concurrent.futures import ThreadPoolExecutor, as_completed

def recreate_view(con):
    with con.begin() as c:
        c.execute(sa.text('DROP VIEW IF EXISTS v'))
    with con.begin() as c:
        c.execute(sa.text('CREATE OR REPLACE VIEW v as SELECT a, b FROM t'))

def main():
    con = sa.create_engine('databend://root:pwd@databend-query/default?sslmode=disable')
    with con.begin() as c:
        c.execute(sa.text('DROP TABLE IF EXISTS t'))
        c.execute(sa.text('CREATE TABLE t (a int not null, b int not null)'))

    with ThreadPoolExecutor(max_workers=256) as executor:
        futures = []
        for _ in range(30):
            futures.append(executor.submit(recreate_view, con))

        for future in as_completed(futures):
            future.result()

if __name__ == '__main__':
    main()

Are you willing to submit PR?

[ ] Yes I am willing to submit a PR!

The error you're encountering, ResponseError with 2314: Drop table 'v' with drop_on time, occurs when there is an attempt to drop a table that has a drop time associated with it. This issue arises due to concurrent modifications of the same view, leading to conflicts.

Databend handles concurrent SQL command execution by defining specific plans for each operation, such as DROP VIEW and CREATE OR REPLACE VIEW, and executing them asynchronously. However, this does not inherently prevent conflicts when multiple threads attempt to modify the same view simultaneously ^[1].

To address this issue, you might consider implementing a locking mechanism or adjusting the transaction isolation level to handle concurrent modifications more gracefully. Unfortunately, I couldn't find specific documentation on locking mechanisms or transaction isolation level configurations in the Databend repository ^[1].

As a workaround, you could serialize the DROP VIEW and CREATE OR REPLACE VIEW operations to ensure that they do not overlap. This can be done by using a global lock or a similar synchronization mechanism in your application code. Here is an example using a threading lock in Python:

import sqlalchemy as sa
from concurrent.futures import ThreadPoolExecutor, as_completed
import threading

lock = threading.Lock()

def recreate_view(con):
    with lock:
        with con.begin() as c:
            c.execute(sa.text('DROP VIEW IF EXISTS v'))
        with con.begin() as c:
            c.execute(sa.text('CREATE OR REPLACE VIEW v as SELECT a, b FROM t'))

def main():
    con = sa.create_engine('databend://root:pwd@databend-query/default?sslmode=disable')
    with con.begin() as c:
        c.execute(sa.text('DROP TABLE IF EXISTS t'))
        c.execute(sa.text('CREATE TABLE t (a int not null, b int not null)'))

    with ThreadPoolExecutor(max_workers=256) as executor:
        futures = []
        for _ in range(30):
            futures.append(executor.submit(recreate_view, con))

        for future in as_completed(futures):
            future.result()

if __name__ == '__main__':
    main()

This approach ensures that only one thread can execute the DROP VIEW and CREATE OR REPLACE VIEW commands at a time, preventing the conflict and the resulting error.

To continue talking to Dosu, mention @dosu.

sundy-li / databend