Acquire lock for relation failed

[Marcin Bury]

Hi

My application failed at some point when using select 'statement with
lock'.

Now I cannot access this table any more. I have tried to delete
suspicious connections from MON$ATTACHMENT table, but unfortunately they
are still there.

Is there an option to bring back normal functionality without restarting
the firebird instance?

TIA
Marcin

[Dimitry Sibiryakov]

Marcin Bury wrote 04.01.2022 13:19:
> Now I cannot access this table any more.

How exactly are you trying to "access this table"? What exactly error do you
get? What transaction parameters are used?

--
WBR, SD.

[Marcin Bury]

Trying to insert new record to this table

INSERT INTO SP_FV_NR (MM, DT, NR)
VALUES (202201, 10, 12)

Executing above gives:

Unsuccessful execution caused by system error that does not preclude
successful execution of subsequent statements.
lock conflict on no wait transaction.
Acquire lock for relation (SP_FV_NR) failed.
-------------------------------------------------------------------------------------------------------------------
SQLCODE: -901
SQLSTATE: 40001
GDSCODE: 335544345

Normally it should return TRANSACTION_ID, so I can identify the
transaction and connection that is currently locking the table, but this
message is as above...

Marcin

------ Wiadomość oryginalna ------
Od: "Dimitry Sibiryakov" <s...@ibphoenix.com>
Do: firebird...@googlegroups.com
Data: 04.01.2022 13:27:47
Temat: Re: [firebird-support] Acquire lock for relation failed

>-- 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/d47b39df-eb74-2750-0666-39c7e193d36c%40ibphoenix.com.

[Dimitry Sibiryakov]

Marcin Bury wrote 04.01.2022 13:32:
> Normally it should return TRANSACTION_ID, so I can identify the transaction and
> connection that is currently locking the table, but this message is as above...

Normally it would be an update conflict on a single record but here you are
unable to lock whole table. It returns us to question: what exactly transaction
parameters of the transaction you are trying to perform the insert in and the
transaction you performed the "select for update with lock" statement.

--
WBR, SD.

[Marcin Bury]

------ Wiadomość oryginalna ------
Od: "Dimitry Sibiryakov" <s...@ibphoenix.com>
Do: firebird...@googlegroups.com

Data: 04.01.2022 13:42:20

Temat: Re: [firebird-support] Acquire lock for relation failed

Using IB Object IB_Transaction with isolation set to tiConsistency.

Actual statement from application to obtain the lock is

SELECT *
FROM SP_FV_NR
WHERE MM = :MM AND DT = :DT
WITH LOCK

In application I have proper 'lock conflict' exception management and
till yesterday it have worked for ages. Something happened that slipped
trough exception management and now the table is permanently locked :-(

Marcin

>

[Dimitry Sibiryakov]

Marcin Bury wrote 04.01.2022 13:51:
> Using IB Object IB_Transaction with isolation set to tiConsistency.

That's a bad idea.

Try to perform a query to the table in WAIT transaction.

--
WBR, SD.

[Marcin Bury]

------ Wiadomość oryginalna ------
Od: "Dimitry Sibiryakov" <s...@ibphoenix.com>
Do: firebird...@googlegroups.com

Data: 04.01.2022 14:02:04

Temat: Re: [firebird-support] Acquire lock for relation failed

Despite bad idea, is there a way to unlock the table without restarting
firebird?

MB
>

[Dimitry Sibiryakov]

Marcin Bury wrote 04.01.2022 14:07:
> is there a way to unlock the table without restarting firebird?

Try to select from the table in WAIT transaction.

--
WBR, SD.

[Marcin Bury]

Using wait requires rebuilding the application which is not an option at the moment. 

Any other options? 

MB 

-------- Oryginalna wiadomość --------
Od: Dimitry Sibiryakov <s...@ibphoenix.com>
Data: wt., 4 sty 2022, 14:21
Do: firebird...@googlegroups.com

Temat: Re: [firebird-support] Acquire lock for relation failed

Marcin Bury wrote 04.01.2022 14:07:

--
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/80eee38e-2538-27ca-4036-bc20a0b89b6f%40ibphoenix.com.

[Dimitry Sibiryakov]

Marcin Bury wrote 04.01.2022 14:44:
> Using wait requires rebuilding the application which is not an option at the
> moment.

Use isql. The query also is not important but it must access the table.
Transaction with WAIT flag is supposed to ping lock owner if it is still alive,
while NOWAIT transactions don't do it.

--
WBR, SD.

[Mark Rotteveel]

On 04-01-2022 14:44, Marcin Bury wrote:
> Using wait requires rebuilding the application which is not an option at
> the moment.
>
> Any other options?

Can't you use ISQL to execute the select in a wait transaction?
Dimitry's suggestion is a way to resolve the lock without having to
restart Firebird.

Sure, if this is a common occurrence, it is probably something you need
to address in your application, but as a one off, executing a statement
in ISQL against the same database should be good enough, I assume.

Mark
--
Mark Rotteveel

[Marcin Bury]

I have managed to connect to the database via isql.
I executed SET TRANSACTION WAIT;
then SELECT * FROM SP_FV_NR WITH LOCK;
and now I'm waiting.... so isql is also waiting...
Any ideas how long it can last - this waiting ?

MB

------ Wiadomość oryginalna ------
Od: "Dimitry Sibiryakov" <s...@ibphoenix.com>
Do: firebird...@googlegroups.com

Data: 04.01.2022 14:47:09
Temat: Re: OD: [firebird-support] Acquire lock for relation failed

>-- 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/abfc99a0-9e4b-ad85-4940-bcfeea927411%40ibphoenix.com.

[Mark Rotteveel]

On 04-01-2022 15:34, Marcin Bury wrote:
> I have managed to connect to the database via isql.
> I executed SET TRANSACTION WAIT;
> then SELECT * FROM SP_FV_NR WITH LOCK;
> and now I'm waiting.... so isql is also waiting...
> Any ideas how long it can last - this waiting ?

That would suggest that the transaction holding the lock is still alive.
Otherwise, the lock notification system would have cleared the lock.

Mark
--
Mark Rotteveel

[Marcin Bury]

So what I'd like to ask now is how I could find out the locking
transaction and/or connection and close it?

MB

------ Wiadomość oryginalna ------

Od: "Mark Rotteveel" <ma...@lawinegevaar.nl>
Do: firebird...@googlegroups.com
Data: 04.01.2022 15:39:15

Temat: Re: OD: [firebird-support] Acquire lock for relation failed

>-- 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/d35791fa-eede-3885-fc56-0a13a87dd912%40lawinegevaar.nl.

[Marcin Bury]

After certain period of time isql listed the content of the problematic
table.
Now everything works as expected. I will dig further to add 'WAIT'
option for the transaction in the application to avoid similar problems.

Dimitry, Mark thanks a lot for your help

Marcin

------ Wiadomość oryginalna ------
Od: "Marcin Bury" <marci...@studio-delfi.pl>
Do: firebird...@googlegroups.com
Data: 04.01.2022 15:41:53
Temat: Re: [firebird-support] Acquire lock for relation failed

>To view this discussion on the web, visit https://groups.google.com/d/msgid/firebird-support/em4cb94839-3287-4e04-9e9f-dc977390bd6c%40mb2.

[Mark Rotteveel]

On 04-01-2022 15:41, Marcin Bury wrote:
> So what I'd like to ask now is how I could find out the locking
> transaction and/or connection and close it?

I'm guessing that fb_lock_print could help there, but I haven't really
used it before, and when I look at its output, I find it a bit obscure.

Mark
--
Mark Rotteveel