Troubleshooting
Problem
You receive the following error message when executing a Data Definition Language (DDL) statement: 106: ISAM error: non-exclusive access
Cause
This error can occur because other sessions are querying the same table with their isolation level set to 'dirty read'.
DDL commands change a table's structure and therefore require exclusive access to the table. However, this cannot be achieved if there are sessions currently accessing the table.
NOTE: Using the SET LOCK MODE command will not prevent this error from occurring.
Environment
The following products and operating systems are required in order to use the IFX_DIRTY_WAIT environment variable:
Product Name | Product Version(s) | Hardware Vendor | Operating System |
IBM Informix® Dynamic Server™ | All | Unix/Windows |
Resolving The Problem
There is an environment variable IFX_DIRTY_WAIT that is used to define to the number of seconds a DDL statement will wait for existing dirty readers to finish their access to the target table. When set, the variable also prevents new dirty readers from accessing the table.
The variable can be set in the Informix® Dynamic Server™ environment (before the database is started) or in the client environment. Setting it on the client side will override the setting on the server side.
Example:
- IFX_DIRTY_WAIT=n
- n
- is a positive integer representing the number of seconds that your session will wait for a given dirty reader to finish accessing the target table. Ifthis amount of time is not enough time, the session returns the same error as it would without the variable being set.
- Using the UNIX Korn shell and setting the timeout value to 300 seconds, you would set the environment variable as follows:
- $ export IFX_DIRTY_WAIT=300
Related Information
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21174239