Locks in tables

[thein]

We often get this error when we run our programs

Could not position within a table (thein.tabname).
See error number -243.

This is because of table locks. Sometimes the tables lock permanently and we have to release them with
onmode -z sessionid#

Does anyone know if it is better to change the lock mode to lock mode ROW the default is lock mode PAGE and what could be the cost in performance.

 

[olded]

Thein:

In my company, part of normal procedure, is to set lock mode row on all tables. If there is a drop in performance, it's small.

Of course, this is just my opinion, and I could be wrong.

Regards,

Ed

 

[vpshriyan]

Hi thein,

Identify the tables which are busy most of the time or in other words which are traffic oriented. For such tables selectively change the locklevel to ROW. By doing so you will be able to reduce the lock contentions with neglegible amount of overhead on performance.

One more solution for this would be setting the proper granularity of locks using isolation levels. If your report/query/lookups etc. are not 100% crucial in nature, please set the isolation to dirty read so that you get unhindered access to the information. The SQL syntax is:
SET ISOLATION TO DIRTY READ.

If you feel that your application can wait till the lock gets released and if you are using Informix 4GL, you can use: SET LOCK MODE TO WAIT statement.

Regards,
Shriyan