Problem with transactions

[Tomasz Dubiel]

Hello. In one of our customers server we have a recurring problem of firebird processess not visible in MON$ATTACHMENTS. The processess cause problem with transactions, for example:

Oldest transaction      91 977 488
Oldest active                91 977 489
Oldest snapshot          91 977 489
 Next transaction         92 639 406

Firebird 2.5.9 ClassicServer on Debian 8. Either we have to stop Firebird, kill processess and start Firebird, or we have to identify those processess and kill them manually.

When we kill them, oldest active and oldest snapshot are close to next transaction, we perform sweep and everything is ok then, all transactions parameters have small difference.

Those processess for sure are not listen in MON$ATTACHMENTS. It looks like firebird process is created to one database, some editing transactions are created, but none of them are visible in MON$ATTACHMENTS, MON$TRANSACTIONS. In our example, all attachments had timestamp equal or bigger than 12th May, but we killed 2 processess from 10th May and problem was solved.

How can we fix this problem?

Best regards,

Tomasz Dubiel.

[Dimitry Sibiryakov]

13.05.2021 09:38, Tomasz Dubiel wrote:
> How can we fix this problem?

Use MON$TRANSACTIONS to find which attachment keeps so old transaction then fix the
transaction management in the application that established this attachment.

--
WBR, SD.

[Tomasz Tyrakowski]

On 13.05.2021 at 09:38, Tomasz Dubiel wrote:
> Hello. In one of our customers server we have a recurring problem of
> firebird processess not visible in MON$ATTACHMENTS. The processess cause
> problem with transactions, for example:
> Oldest transaction 91 977 488
> Oldest active 91 977 489
> Oldest snapshot 91 977 489
> Next transaction 92 639 406
>
> Firebird 2.5.9 ClassicServer on Debian 8. Either we have to stop Firebird,
> kill processess and start Firebird, or we have to identify those processess
> and kill them manually.

[...]

Are you sure you've got attachments to the database, which are not
listed in MON$ATTACHMENTS? That's strange. I've just checked FB 2.5.9
classic on Debian 10, and even if I connect using different protocols
(e.g. a local isql connection using the db file path and a TCP/IP
connection from a remote client) I can still see all attached clients in
MON$ATTACHMENTS (via either connection). Is it possible you've got
embedded connections (bypassing the server process) mixed with regular
server connections? Are all your clients remote, or do some of them run
on the same machine as your FB server?
That's the only possibility that comes to my mind.
Attachments not visible in MON$ATTACHMENTS and transactions not visible
in MON$TRANSACTIONS smell to me like a broken server setup, but maybe
Mark or Dimitry could cast some light on whether such case is possible
(it would be nice to know for the future).

cheers
Tomasz
________________________________


Ta wiadomość zawiera poufne informacje przeznaczone tylko dla adresata. Jeżeli nie jesteście Państwo jej adresatem, bądź otrzymaliście ją przez pomyłkę, prosimy o powiadomienie o tym nadawcy oraz trwałe jej usunięcie.

[Omacht András]

Hi!

Limbo transactions are not listed in mon$transactions.

https://www.firebirdsql.org/pdfmanual/html/gfix-transactions.html

András

--
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 on the web, visit https://groups.google.com/d/msgid/firebird-support/0743a7be-2a26-bf2c-7234-fc67568636e4%40sol-system.pl.



__________ Information from ESET Mail Security, version of virus signature database 23288 (20210513) __________

The message was checked by ESET Mail Security.
http://www.eset.com

[Tomasz Dubiel]

Hello. You didnt read my question. The process doesnt exist in MON$ATTACHMENTS, but I know for sure its connected to database, because I kill it and finally sweep can make its job. As I said previously, today all connections in MON$ATTACHMENTS was started 12th May or later. I found two firebird processess on Debian from 10th May, killed them and I solved my problem temporarily.

[Tomasz Dubiel]

I'm 100% sure. Once I killed those 2 processess, OT, OAT etc immediately refreshed and finally sweep could do its job.

Its database for ERP system. Most of connections come from users logging to system, but there are also other connections for integrations with other programms (and those connection are visible of course in MON$ATTACHMENTS) Every local connection (isql process, backup, etc) is visible as well.

It looks like MON$ATTACHMENT doesnt see some kind of processess and we are unable to do anything. If server works for a long time and there are many processess connected to database, its not possible to detect the one not listed in MON$ATTACHMENTS.

[Tomasz Dubiel]

Can they affect the database so that sweep is not possible? If so, how can we handle this?

[Omacht András]

Yes, they can block the garbage collection.

 

List them:

gfix -l[ist] database_name

 

And fix them if you have:

gfix -l[ist] -p[rompt] database_name

 

as the linked manual says.

 

CÉGÜNK A LIBRA CSOPORT TAGJA

OMACHT ANDRÁS fejlesztési igazgató LIBRA Szoftver Zrt. 1113 Budapest, Karolina út 65. +36 (1) 255-3939 in...@libra.hu | www.libra.hu

To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/958d38e9-8274-417e-a0d6-11ffde7fc988n%40googlegroups.com.

[Tomasz Dubiel]

Thank you very much. When problem repeats, I will do it and see if it worked.

[Dimitry Sibiryakov]

13.05.2021 12:37, Tomasz Dubiel wrote:
> The process doesnt exist in MON$ATTACHMENTS

Did you select from MON$ATTACHMENTS as SYSDBA?

> I found two firebird processess on Debian from 10th May, killed them and I solved my problem temporarily.

Next time before killing of Firebird processes attach to them with GDB and dump call
stacks of all threads. It would help to find out what these processes did.

In following document the relevant part is about using debugging info package to get
meaningful output: www.ibphoenix.com/resources/documents/development/doc_36


--
WBR, SD.

[Tomasz Dubiel]

Yes, I was connected as SYSDBA.

Thank you for answers.

[Tomasz Dubiel]

The change of parameter's value in firebird.conf and attaching to process with GDB is safe on production server?

czwartek, 13 maja 2021 o 13:39:00 UTC+2 sd napisał(a):

[Dimitry Sibiryakov]

13.05.2021 13:48, Tomasz Dubiel wrote:
> The change of parameter's value in firebird.conf and attaching to process with GDB is safe
> on production server?

You don't need to change the parameter because your server doesn't crash.
Attaching may block whole work so you'd better do it quickly. But in any case it is not
more dangerous than killing of Firebird processes.
BTW, which signal you used to kill them? SIGTERM or SIGKILL?

--
WBR, SD.

[Tomasz Dubiel]

I simply killed firebird process on Debian - kill <process_PID>.

OK, as I see its better to arrange a little break in work.

[Tomasz Tyrakowski]

On 13.05.2021 at 13:10, Omacht András wrote:
> Hi!
>
> Limbo transactions are not listed in mon$transactions.
>
> https://www.firebirdsql.org/pdfmanual/html/gfix-transactions.html
>
> András

That still doesn't explain server processes handling connections, which
are not listed in MON$ATTACHMENTS. Besides, limbo transactions are
related to multi-db operations, and the OP didn't mention performing
multi-db transactions.

[Mark Rotteveel]

On 13-05-2021 13:10, Omacht András wrote:
> Limbo transactions are not listed in mon$transactions.
>
> https://www.firebirdsql.org/pdfmanual/html/gfix-transactions.html

Although true, this wouldn't explain why the problem was resolved when
the server process were killed. Limbo transactions remain prepared until
explicitly committed or rolled back, even after server restarts.

Mark
--
Mark Rotteveel

[Mark Rotteveel]

On 13-05-2021 14:36, Tomasz Tyrakowski wrote:
> That still doesn't explain server processes handling connections, which
> are not listed in MON$ATTACHMENTS. Besides, limbo transactions are
> related to multi-db operations, and the OP didn't mention performing
> multi-db transactions.

That is not necessarily the case. Limbo transactions are created for all
transactions that are explicitly prepared before committing or rolling
back. Usually this happens with either Firebird multi-db operations, or
with application-level distributed (XA) transactions across Firebird and
non-Firebird systems, but this is not explicitly required (though using
this in a single-db application would be odd).

Mark
--
Mark Rotteveel

[Tomasz Dubiel]

When logging to our ERP system, there are created two connections - one to database with users permissions, system configuration, etc and second to proper database with companys data. So yeah, logging to our system requires creating one connection to one database and second connection to second database. Could this have anything in common with these limbo transactions?

But anyway, I checked all attachments in this second database and there werent those connections listed as well.

[Dimitry Sibiryakov]

13.05.2021 14:52, Tomasz Dubiel wrote:
> Could this have anything in common with these limbo transactions?

No.

--
WBR, SD.