Transaction hanging, I would like to understand why

[Einar Hjortdal]

In my application I am experiencing a hanging transaction: what that means, is that the statement in the transaction does not complete, the firebird server does not seem to respond to my client.

Here is the source, of course the application is complex so I'll try unwrap information for clarity here:

The tables involved are described here: image, product_image

When the function is invoked with one item in the urls array, the following queries are generated and run in one transaction created by the parent function. In a debug environment no other transactions are running in the background.

In this transaction the first query being executed is:
DELETE FROM image i
WHERE EXISTS (
SELECT 1
FROM product_image pi
WHERE pi.product_id = ?
AND pi.image_id = i.id
)

Then the second query being executed is:
INSERT INTO image (id, url) SELECT
                        CAST(? AS BINARY(16)),
                        CAST(? AS BLOB SUB_TYPE TEXT)
                        FROM RDB$DATABASE

And the third query being executed is:
INSERT INTO product_image (product_id, image_id, image_rank) SELECT
                        CAST(? AS BINARY(16)),
                        CAST(? AS BINARY(16)),
                        CAST(? AS INTEGER)
                        FROM RDB$DATABASE

This third query never ends.
I have been looking at the documentation to figure out what is happening. What am I doing wrong?
I am trying to keep these queries within one transaction because I do not want to implement some app-level transactioning logic.

[Gerdus van Zyl]

Usually the problem is one transaction waiting for another to commit to see if conflict has resolved.
Try using a no wait transaction option and you should at least get an error message.

[Einar Hjortdal]

I do agree that this looks like a deadlock, which is why I mentioned that there are no other concurrent running transactions.

Unfortunately I can't switch to nowait because I haven't implemented it in my client, and implementing it is a bit of a headscratcher.

Because there are no other transaction, the issue must be with the queries I have shown. But I don't understand what it is.
I have attempted to get help by chatgpt/gemini/grok but of course those are absolutely useless.

I take this opportunity to add some more details I have missed in my first message:
The transaction is in `read committed` mode.
I am running Firebird 5.0.3 on docker.
I am providing the correct number of parameters when executing the query, the firebird server is no waiting for me to provide some parameter, of that I am sure.

[Dimitry Sibiryakov]

Einar Hjortdal wrote 21.09.2025 21:11:
> I do agree that this looks like a deadlock, which is why I mentioned that there
> are no other concurrent running transactions.

Use Trace and Audit Service to confirm that and see what really happen.

> The transaction is in `read committed` mode.

Switch to `concurrency` or at least add `record version` option.

--
WBR, SD.

[Einar Hjortdal]

On Monday, September 22, 2025 at 10:53:51 AM UTC+2 sd wrote:

Use Trace and Audit Service to confirm that and see what really happen.

I am very confident there are no other transactions running, I know my application well.

Additionally, I have verified by querying the MON$TRANSACTIONS table before the execution of the third query.

I do not know how to use the trace and audit service.

Switch to `concurrency` or at least add `record version` option.

record version is set

Is there the possibility that the sequence of these specific queries can cause an intra-transaction deadlock?

 

[Dimitry Sibiryakov]

Einar Hjortdal wrote 22.09.2025 14:02:
> I do not know how to use the trace and audit service.

https://www.firebirdsql.org/file/documentation/release_notes/html/en/2_5/rnfb25-trace.html

> Is there the possibility that the sequence of these specific queries can cause
> an intra-transaction deadlock?

No. Intra-transaction deadlocks is something that was never reported for
Firebird: MVCC rely on transaction number to handle update conflicts so
conflicts inside of the same transaction is explicitly impossible.

--
WBR, SD.

[Einar Hjortdal]

https://www.firebirdsql.org/file/documentation/release_notes/html/en/2_5/rnfb25-trace.html

I have seen this page but I don't understand it.
Do I run fbsvcmgr? I assume fbsvcmgr is available in the docker container.

 

No. Intra-transaction deadlocks is something that was never reported for
Firebird: MVCC rely on transaction number to handle update conflicts so
conflicts inside of the same transaction is explicitly impossible.

This is valuable information.

The llm had made me suspect this was possible.

Given that there are no other transactions running, then it cannot be a deadlock.

But then I don't know what it could be.

 

[Dimitry Sibiryakov]

Einar Hjortdal wrote 22.09.2025 14:16:
> Do I run fbsvcmgr? I assume fbsvcmgr is available in the docker container.

Audit is simpler in development environment. All you need is to modify
fbtrace.conf and firebird.conf. Then restart server, run your application till
the lock and then analyse produced log file.

> Given that there are no other transactions running, then it cannot be a deadlock.
> But then I don't know what it could be.

If you are ready for a hard way, you can put PDB files along Firebird files,
install WinDBG or other debugger and then attach to the server process to see
what exactly each thread is busy with.

--
WBR, SD.

[Tomasz Tyrakowski]

On 22.09.2025 at 14:16, Einar Hjortdal wrote:
>
> Given that there are no other transactions running, then it cannot be a
> deadlock.
> But then I don't know what it could be.
>

Any chance you've got active triggers on your tables with some complex
logic, e.g. loops that for some reason never end, or some inserts /
updates that fire the triggers again and again (possibly for different
records or even tables)?
When your query hangs indefinitely, what's the CPU and disk usage of the
firebird process handling the query? Does it wait idly or puts some
strain on the CPU / disk?
Maybe checking those things will give you some clues as to what is
actually happening (deadlock is just a hypothesis, as far as I
understand from your posts).

regards
Tomasz

[liviuslivius]

Hi

How did you checked that there are no other transactions? Run select * FROM MON$transactions

Do above as sysdba and you got it.

Regards,

Karol Bieniaszewski

-------- Oryginalna wiadomość --------

Od: Einar Hjortdal <einar.h...@gmail.com>

Data: 22.09.2025 14:16 (GMT+01:00)

Do: firebird-support <firebird...@googlegroups.com>

Temat: Re: [firebird-support] Re: Transaction hanging, I would like to understand why

--
Support the ongoing development of Firebird! Consider donating to the Firebird Foundation and help ensure its future. Every contribution makes a difference. Learn more and donate here:
https://www.firebirdsql.org/donate
---
You received this message because you are subscribed to the Google Groups "firebird-support" group.
To unsubscribe from this group and stop receiving emails from it, send an email to firebird-suppo...@googlegroups.com.
To view this discussion, visit https://groups.google.com/d/msgid/firebird-support/f8c1ed18-7d5b-43db-addb-0137b78691c3n%40googlegroups.com.