`iceberg-rest-fixture` errors with SQLiteException under high concurrent load

[kevinjqliu]

Apache Iceberg version

None

Query engine

None

Please describe the bug 🐞

The iceberg-rest-fixture REST catalog server uses JdbcCatalog and specifically sqlite, jdbc:sqlite::memory: as the underlying database.

Under high concurrent load, the catalog server errors with SQLiteException such as,

Caused by: org.sqlite.SQLiteException: [SQLITE_ERROR] SQL error or missing database (no such table: iceberg_tables)

This issue arises because jdbc:sqlite::memory: creates a distinct, ephemeral in-memory database for each new JDBC connection. The JdbcClientPool defaults to client_pool size of 2.
When managing more than 2 connections, the JdbcClientPool can create new connections and thus new in-memory new database, without running the initialization logic. Subsequent db operations will fail due to missing tables in these newly created database.

Reproduce

Start a IRC server using iceberg-rest-fixture. I'm using pyiceberg's integration docker compose file by running

make test-integration-setup

in the pyiceberg repo.

Create concurrent requests to the IRC. In poetry run python, run this script

from pyiceberg.catalog import load_catalog
from concurrent.futures import ThreadPoolExecutor

def run_rest_catalog():
    catalog = load_catalog(
        "rest",
        **{
            "type": "rest",
            "uri": "http://localhost:8181",
            "s3.endpoint": "http://localhost:9000",
            "s3.access-key-id": "admin",
            "s3.secret-access-key": "password",
        },
    )
    try:
        ns = catalog.list_namespaces()
        print(ns)
    except Exception as e:
        print(e)

def run_catalog_in_parallel(workers, runs):
    # Run in parallel using threads
    with ThreadPoolExecutor(max_workers=workers) as executor:
        futures = [executor.submit(run_rest_catalog) for _ in range(runs)]
        results = {}
        for future in futures:
            _ = future.result()


run_catalog_in_parallel(20, 200)

Solution

In iceberg-rest-fixture's Dockerfile, set
CATALOG_URI=jdbc:sqlite:file::memory:?cache=shared

• Use file::memory: to explicitly indicate an in-memory database that supports URI parameters.
• Add the ?cache=shared parameter to allow SQLite to use a shared in-memory cache so that all connections will access the same underlying in-memory database.

EDIT: Fokko suggested a better solution by limiting sqlite to just 1 client connection

Additionally, we should replace all subproject using the original jdbc:sqlite::memory: connection string.

Willingness to contribute

• I can contribute a fix for this bug independently
• I would be willing to contribute a fix for this bug with guidance from the Iceberg community
• I cannot contribute a fix for this bug at this time

[kevinjqliu]

jdbc:sqlite usage in iceberg repos, https://grep.app/search?f.repo.pattern=apache%2Ficeberg&q=jdbc%3Asqlite
Only in apache/iceberg and apache/iceberg-rust

[Fokko]

Interesting. I was under the assumption that SQLite would handle concurrency from the same process. Another option would be to limit the concurrency to 1 to avoid race conditions completely, since SQLite is purely for testing purposes.

[kevinjqliu]

I was under the assumption that SQLite would handle concurrency from the same process.

Yea i think this is a quirk of the sqlite in-memory db. From https://www.sqlite.org/inmemorydb.html,

Every :memory: database is distinct from every other. So, opening two database connections each with the filename ":memory:" will create two independent in-memory databases.

The database is created per connection.

However, the same in-memory database can be opened by two or more database connections as follows:

rc = sqlite3_open("file::memory:?cache=shared", &db);
Or,
ATTACH DATABASE 'file::memory:?cache=shared' AS aux1;
This allows separate database connections to share the same in-memory database.

But with "shared cache" enabled, multiple connections can access the same database

🤷

Another option would be to limit the concurrency to 1 to avoid race conditions completely, since SQLite is purely for testing purposes.

thats a good option too. let me test that out

[kevinjqliu]

Another option would be to limit the concurrency to 1 to avoid race conditions completely, since SQLite is purely for testing purposes.

Setting concurrency to 1 solves this issue too

CATALOG_CLIENTS=1

[kevinjqliu]

Setting CATALOG_URI=jdbc:sqlite:file::memory:?cache=shared still has issues when concurrently updating the sqlite db.

For example, when concurrently dropping and recreating namespaces.

from pyiceberg.catalog import load_catalog
from concurrent.futures import ThreadPoolExecutor
import random
import string

def run_rest_catalog():
    catalog = load_catalog(
        "rest",
        **{
            "type": "rest",
            "uri": "http://localhost:8181",
            "s3.endpoint": "http://localhost:9000",
            "s3.access-key-id": "admin",
            "s3.secret-access-key": "password",
        },
    )
    random_string = ''.join(random.choices(string.ascii_letters + string.digits, k=10))
    try:
        catalog.create_namespace(random_string)
        ns = catalog.list_namespaces()
        print(ns)
        catalog.drop_namespace(random_string)
    except Exception as e:
        print(e)

def run_catalog_in_parallel(workers, runs):
    # Run in parallel using threads
    with ThreadPoolExecutor(max_workers=workers) as executor:
        futures = [executor.submit(run_rest_catalog) for _ in range(runs)]
        for future in futures:
            _ = future.result()


run_catalog_in_parallel(20, 200)

Errors with

UncheckedSQLException: Failed to execute: DELETE FROM iceberg_namespace_properties WHERE catalog_name = ? AND namespace = ?

Setting CATALOG_CLIENTS=1 resolves this consistency issue.

Updating #13367 to do that instead, thanks Fokko!

And there's no reason to have both changes. Setting client pool to 1 means the sqlite db does not need to be shared.

[Claudenw]

I opened an adjacent issue #13343
The solution for that one, where we need to test concurrent access to the DB was to use Derby.
PR for this change has example Derby code: #13345