Sometimes I see the following error messages appearing.

"Cannot lock the table Orders in the schema Default for transaction access"

"An error occurred with the statement at line 30 and column 22 (Cannot lock the table Orders in the schema Default for transaction access)"


Can this be cause by option "Enable row change detection"?

Would it be possible to get the source of this error? Is it a SQL statement or procedure?

By the way.

EDB server version 2.27 b1 is used.

Mike,

<< Sometimes I see the following error messages appearing.

"Cannot lock the table Orders in the schema Default for transaction access"

"An error occurred with the statement at line 30 and column 22 (Cannot lock the table Orders in the schema Default for transaction access)" >>

This is because some other user/session has the Orders table locked in a transaction.  Are you using the ElevateDB Server ?  If so, then you need to make sure that you set your dead session expiration settings on the ElevateDB Server so that dead sessions are removed as quickly as possible (30 seconds is the lowest that you can set it):

https://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Starting_Configuring_Server

Typically, this type of issue is caused by a client session manually initiating a transaction, and then disconnecting or otherwise not committing/rolling back the transaction:

https://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Locking_Concurrency

under "Table Locks".

Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

Thank you for the quick answer.

Yes ElevateDB Server is running as a service.

I will lower the dead session expiration settings to see if this helps.

Mike

I have changed the timeout period and monitored the database for a longer period.

The locking issue has been solved.

Thanks Tim!

Unfortunately the problem reoccured.

There are no transaction used so that would not be the cause.

Would updating to 2.28 help to solve this?

Mike,

<< Unfortunately the problem reoccured.

There are no transaction used so that would not be the cause. >>

It's quite possible that you're simply running into a situation where the system is busy and an UPDATE or DELETE statement is encountering a transaction lock error.  Transaction locks are used with any insert, update, or delete, and are not *just* used with manual transactions.  Manual transactions are just a case where the transactions typically take longer to complete, but you can easily also have an UPDATE statement that takes a long time to complete and causes other sessions to time out when trying to update the same table.

Tim Young
Elevate Software
www.elevatesoft.com

Hi Tim,

Thanks for the quick reply.

Should a retry command be used in this case?

Mike,

<< Should a retry command be used in this case?
>>

ElevateDB is *already* retrying quite a bit before it issues such an error.  It will retry for about 90 seconds with automatic transactions (inserts, updates, and deletes), and with manual transactions you can specify the timeout as part of the START TRANSACTION statement (or TEDBDatabase.StartTransaction method call).

Tim Young
Elevate Software
www.elevatesoft.com

Could updating to 2.28 help also to solve this?