Solving the "Cannot lock Table XXX" Error message
Orixa Systems use multi-user databases in our Apps. With this type of database multiple users can access the data simultaneously. The database uses "row-level-locking", this means that multiple users can all access the database at the same time and edit records at the same time.
If two users try to access and edit the same record at the same time an error will be displayed.
Some data-operations run by Orixa Apps connect to the database exclusively. While these operations are underway users trying to access data may also see "locking" error messages.
These errors do not indicate that anything bad has happened, simply that some session is accessing the record, data-table or database, meaning other users will not be allowed to access it.
Sometimes data-operations may fail part way through running or users may start to edit a record, and then leave their desk. In these sitations a record, data-table or database can be left in a "locked" state. This situation is rare. The following document details how to deal with this type of issue.
How the error may display in your Orixa App
The App will either display a database error "#300 Cannot lock the table XXX in the schema default for transaction access" or Orixa may show an error internal to the App "CANNOT EDIT RECORD OTHER USERS ARE ACCESSING IT: ..." followed by details of the name of the user-session. Both these variations of error message are shown in the images below.
"Cannot Edit Record" error message |
"Cannot Lock Table" error message |
Removing expired or disconnected sessions
When a record in a table is locked due to other sessions accesing it, the troublesome lock, created by another person's session needs to be cleared. The easiest way to do this is just to check who is editing data in the data-table, and ask them to post the record they are editing. If your App has show the "CANNOT EDIT RECORD" error message, it is usually possible to identify the user from the part of the message that names the user.
However it is also possible that the lock has been created by an automatic process or by a user who's session has now been disconnected or timed out, but not yet expired. In these cases it is better to find the expired session and disconnect it on the server, the image below shows how to do this.
EDB Server showing expired or disconnected session |
To remove and expired session Open the EDB Server programme on the server.
|
Checking for the mode of data-connection of your Orixa App
It is possible for an Orixa App to connect to the database in "local mode". In this situation multi-user access is not possible, and locking or editing errors are much more likely. In "local mode" no IP-Address is used by the App during connection, and connection is made directly to the data-folder which contains the database configuration files.
It is possible that some of the users using an Orixa App or one of the Orixa database management utilities is connecting in "local mode", while others connect in "multi-user" mode. This should not happen, but it can occur when a system is poorly managed.
If your system is a multi-user system, all users should connect in multi-user mode. If locking or editing errors occur, a first piece of work to undertake to assess the cause of the problem is to check all users connection mode into the system. This should be done for all Apps and for Orixa database management utilities, as all of these count as connections to the database.
Checking the connection in EDB Manager |
Review and check data-connections in EDB Manager Click on the heading for your main database session, as shown in the image. The details of the connection will be shown in the "Details" panel as shown. If the detail panel shows an IP Address (usually 127.0.0.1 or 192.168.XXX.XXX) then the system is correct. If the panel shows a data-folder (such as "C:\MySys\Database") then the system is incorrect. To fix this, click on the "Edit Session" link in the Tasks panel, and update the details of the database connections to add an IP Address, and set the Sessionn Type to "Remote". |
Testing how an App is connected to the database |
Viewing details for an App's connection to the database Run your Orixa App
|
Updating / changing the connection of an App to the database |
Changing how an App connects to the database Orixa Apps use settings saved in the Registry to control how they access the database. If you have found an App which uses a local connection you should update it to use an IP Address. Find the OrxRegistryMgr.exe, run it and you should see the screen on the left. Update the "Server IP Address" (highlighted) and the "Port" to match the values used by other users. Server IP addresses used in LANs are usually in the range 192.168.100.001 to 192.168.254.254
|