René's URL Explorer Experiment

{"@context":"https://schema.org","@type":"DiscussionForumPosting","headline":"Thread blocking problem with \"firebird-driver\"","articleBody":"We are developing a database application based on Firebird and Python\nwhich highly uses threads to run many jobs in parallel. Originally, we\ndecided to use the \"fdb\" module to connect to the database. Unfortunately,\nfdb's support for Firebird versions bigger than 3 is limited, so a switch\nto \"firebird-driver\" is recommended.\n\nTo ensure data consistency the database is operating \"repeatable read\",\nthis means if transactions of different threads touch the same data, the\ntransaction is throwing an exception and the application tries to repeat.\nUnfortunately, \"firebird-driver\" tend to block some threads much more than\nothers so much that they exceed the tolerance of repetitions and give up.\nBut this nearly never happened with \"fdb\".\n\nTo demonstrate this, I can recommend a Python software we developed nearly\na decade ago to make database benchmarks with PostgreSQL and Firebird.\nFor PostgreSQL we used \"psycopg2\" module, for Firebird originally \"fdb\" and\n\"firebirdsql\". In the last days I also added \"firebird-driver\" support.\n\nThe name of this benchmark software is \"dsbench\". It makes TPC-B transactions\nto measure the database performance in a way like pgbench is doing it.\nThis means, dsbench generates databases of logarithmically rising sizes up to\nabout 150GB and is carrying out transactions then. Finally, the software\nis creating several diagrams with results and calculates a performance\nfactor (we called that DSI - dsbench index) to give a single comparable\nnumber.\n\nOne \"transaction block\" lasts about 1 minute. In this minute many threads\n(default 16) connects to the database and try to execute as many transactions\nas possible. If the database is small, the probability of a collision is high\nand transactions must be rolled back. Each block gives you a table of results,\nespecially numbers of successful and failed transactions for each thread.\nIf \"fdb\" is used all threads show comparable numbers of successful and failed\ntransactions. Otherwise, if \"firebird-driver\" is used, mainly one thread\nis doing all transactions and all the others are more or less blocked\nand can't do anything. Let me show this with the dsbench log itself:\n\nFirst: \"fdb\" - as expected\nNumber of \"done\" (successful transactions) and \"errors\" (rolled back due to collision) done by different threads is comparable.\n\n--- Begin dsbench measuring cycle 1/10 ---\n2025-12-11 21:42:33,477 -- [INFO] (Worker-03 ): cycle=1, scale=1, dbsize=0.017670, done=408, errors=1009, residence=4.58, elapsed=60.00, tpsR=88.99, tpsE=6.80, tpsD=6.80\n2025-12-11 21:42:33,479 -- [INFO] (Worker-05 ): cycle=1, scale=1, dbsize=0.017670, done=565, errors=1338, residence=6.07, elapsed=59.99, tpsR=93.14, tpsE=9.42, tpsD=9.42\n2025-12-11 21:42:33,483 -- [INFO] (Worker-11 ): cycle=1, scale=1, dbsize=0.017670, done=481, errors=1452, residence=4.92, elapsed=59.96, tpsR=97.75, tpsE=8.02, tpsD=8.02\n2025-12-11 21:42:33,485 -- [INFO] (Worker-09 ): cycle=1, scale=1, dbsize=0.017670, done=459, errors=1495, residence=5.47, elapsed=60.00, tpsR=83.93, tpsE=7.65, tpsD=7.65\n2025-12-11 21:42:33,488 -- [INFO] (Worker-14 ): cycle=1, scale=1, dbsize=0.017670, done=705, errors=1806, residence=7.66, elapsed=59.79, tpsR=92.09, tpsE=11.79, tpsD=11.75\n2025-12-11 21:42:33,490 -- [INFO] (Worker-12 ): cycle=1, scale=1, dbsize=0.017670, done=374, errors=1283, residence=4.39, elapsed=59.68, tpsR=85.13, tpsE=6.27, tpsD=6.23\n2025-12-11 21:42:33,492 -- [INFO] (Worker-16 ): cycle=1, scale=1, dbsize=0.017670, done=396, errors=1058, residence=4.04, elapsed=59.73, tpsR=98.03, tpsE=6.63, tpsD=6.60\n2025-12-11 21:42:33,492 -- [INFO] (Worker-04 ): cycle=1, scale=1, dbsize=0.017670, done=597, errors=1412, residence=5.90, elapsed=59.73, tpsR=101.24, tpsE=9.99, tpsD=9.95\n2025-12-11 21:42:33,496 -- [INFO] (Worker-13 ): cycle=1, scale=1, dbsize=0.017670, done=593, errors=1592, residence=6.64, elapsed=59.61, tpsR=89.37, tpsE=9.95, tpsD=9.88\n2025-12-11 21:42:33,496 -- [INFO] (Worker-01 ): cycle=1, scale=1, dbsize=0.017670, done=498, errors=1770, residence=5.78, elapsed=59.69, tpsR=86.20, tpsE=8.34, tpsD=8.30\n2025-12-11 21:42:33,516 -- [INFO] (Worker-15 ): cycle=1, scale=1, dbsize=0.017670, done=696, errors=2122, residence=7.18, elapsed=58.92, tpsR=96.90, tpsE=11.81, tpsD=11.60\n2025-12-11 21:42:33,520 -- [INFO] (Worker-08 ): cycle=1, scale=1, dbsize=0.017670, done=484, errors=1634, residence=5.80, elapsed=58.55, tpsR=83.49, tpsE=8.27, tpsD=8.07\n2025-12-11 21:42:33,520 -- [INFO] (Worker-10 ): cycle=1, scale=1, dbsize=0.017670, done=440, errors=1235, residence=5.05, elapsed=58.91, tpsR=87.16, tpsE=7.47, tpsD=7.33\n2025-12-11 21:42:34,486 -- [INFO] (Worker-02 ): cycle=1, scale=1, dbsize=0.017670, done=727, errors=1759, residence=7.07, elapsed=60.00, tpsR=102.89, tpsE=12.12, tpsD=12.12\n2025-12-11 21:42:34,486 -- [INFO] (Worker-07 ): cycle=1, scale=1, dbsize=0.017670, done=468, errors=1226, residence=4.79, elapsed=59.99, tpsR=97.68, tpsE=7.80, tpsD=7.80\n2025-12-11 21:42:34,487 -- [INFO] (Worker-06 ): cycle=1, scale=1, dbsize=0.017670, done=599, errors=1639, residence=6.53, elapsed=59.99, tpsR=91.66, tpsE=9.98, tpsD=9.98\n\nSecond: \"firebird-driver\" - some threads blocked\nFor instance, Worker-15 did not try any transaction (successful or failed) within one minute!\n\n--- Begin dsbench measuring cycle 1/10 ---\n2025-12-11 21:45:49,128 -- [INFO] (Worker-07 ): cycle=1, scale=1, dbsize=0.014465, done=0, errors=10324, residence=0.00, elapsed=60.00, tpsR=0.00, tpsE=0.00, tpsD=0.00\n2025-12-11 21:45:49,134 -- [INFO] (Worker-08 ): cycle=1, scale=1, dbsize=0.014465, done=0, errors=3, residence=0.00, elapsed=0.02, tpsR=0.00, tpsE=0.00, tpsD=0.00\n2025-12-11 21:45:49,135 -- [INFO] (Worker-03 ): cycle=1, scale=1, dbsize=0.014465, done=0, errors=4, residence=0.00, elapsed=0.03, tpsR=0.00, tpsE=0.00, tpsD=0.00\n2025-12-11 21:45:49,137 -- [INFO] (Worker-11 ): cycle=1, scale=1, dbsize=0.014465, done=0, errors=14, residence=0.00, elapsed=60.00, tpsR=0.00, tpsE=0.00, tpsD=0.00\n2025-12-11 21:45:49,141 -- [INFO] (Worker-05 ): cycle=1, scale=1, dbsize=0.014465, done=0, errors=7, residence=0.00, elapsed=0.04, tpsR=0.00, tpsE=0.00, tpsD=0.00\n2025-12-11 21:45:49,142 -- [INFO] (Worker-13 ): cycle=1, scale=1, dbsize=0.014465, done=0, errors=1766, residence=0.00, elapsed=32.10, tpsR=0.00, tpsE=0.00, tpsD=0.00\n2025-12-11 21:45:49,146 -- [INFO] (Worker-14 ): cycle=1, scale=1, dbsize=0.014465, done=0, errors=3, residence=0.00, elapsed=0.02, tpsR=0.00, tpsE=0.00, tpsD=0.00\n2025-12-11 21:45:49,146 -- [INFO] (Worker-16 ): cycle=1, scale=1, dbsize=0.014465, done=0, errors=7, residence=0.00, elapsed=0.03, tpsR=0.00, tpsE=0.00, tpsD=0.00\n2025-12-11 21:45:49,146 -- [INFO] (Worker-09 ): cycle=1, scale=1, dbsize=0.014465, done=0, errors=4, residence=0.00, elapsed=0.03, tpsR=0.00, tpsE=0.00, tpsD=0.00\n2025-12-11 21:45:49,147 -- [INFO] (Worker-02 ): cycle=1, scale=1, dbsize=0.014465, done=4029, errors=0, residence=26.45, elapsed=26.61, tpsR=152.34, tpsE=151.39, tpsD=67.15\n2025-12-11 21:45:49,149 -- [INFO] (Worker-10 ): cycle=1, scale=1, dbsize=0.014465, done=0, errors=2028, residence=0.00, elapsed=44.95, tpsR=0.00, tpsE=0.00, tpsD=0.00\n2025-12-11 21:45:49,152 -- [INFO] (Worker-12 ): cycle=1, scale=1, dbsize=0.014465, done=0, errors=0, residence=0.00, elapsed=0.00, tpsR=0.00, tpsE=0.00, tpsD=0.00\n2025-12-11 21:45:49,152 -- [INFO] (Worker-04 ): cycle=1, scale=1, dbsize=0.014465, done=0, errors=2, residence=0.00, elapsed=0.02, tpsR=0.00, tpsE=0.00, tpsD=0.00\n2025-12-11 21:45:49,152 -- [INFO] (Worker-15 ): cycle=1, scale=1, dbsize=0.014465, done=0, errors=0, residence=0.00, elapsed=0.00, tpsR=0.00, tpsE=0.00, tpsD=0.00\n2025-12-11 21:45:50,126 -- [INFO] (Worker-06 ): cycle=1, scale=1, dbsize=0.014465, done=0, errors=12669, residence=0.00, elapsed=60.00, tpsR=0.00, tpsE=0.00, tpsD=0.00\n2025-12-11 21:45:50,132 -- [INFO] (Worker-01 ): cycle=1, scale=1, dbsize=0.014465, done=0, errors=2, residence=0.00, elapsed=0.02, tpsR=0.00, tpsE=0.00, tpsD=0.00\n\nTo reproduce:\n\n- Take the dsbench software and unpack\n- Look at INSTALL instructions - at the end I added some notes to use a venv\n  instead of installing system packages\n- MANUAL gives you more info about dsbench\n- Activate your venv (source __venv__/bin/activate)\n- Execute dsbench using fdr.conf configuration file (which uses \"firebird-diver\")\n  python dsbench.py -f fdr.conf\n- To activate \"fdb\" instead, open fdr.conf and change the line:\n  self.job.fbdriver = \"fdr\"\n  to:\n  self.job.fbdriver = \"fdb\"\n\nRegards\nMathias\n\n[dsbench.tar.gz](https://github.com/user-attachments/files/24125481/dsbench.tar.gz)","author":{"url":"https://github.com/mbllo-gh","@type":"Person","name":"mbllo-gh"},"datePublished":"2025-12-12T10:45:23.000Z","interactionStatistic":{"@type":"InteractionCounter","interactionType":"https://schema.org/CommentAction","userInteractionCount":8},"url":"https://github.com/62/python3-driver/issues/62"}