[markbrown]

I am running informix ver 7.

i have two tables one of which is being locked only for that record,

whereas another table is being locked to all records, when updating just one record

can anyone offer some suggestions

[thedafna]

To change lock mode:
alter table XXX lock mode (page|row);

For row lock mode you need to define more locks in the onconfig - then you have less chances for waitings. The management of the locks costs some. If you have many users to the same table on the same time - think about row.

[markbrown]

I have since found the solution since I posted this question.

If you create a unique index on the table
you will overcome the error
eg
create unique index ixtabname on tabname (field)

Regards
Mark

[thedafna]

Can you explain why it works? doesn't make sence to me.

[dananio]

Hahaha ...
Create index always table lock like Alter table
if you want to read you can 'set isolation to diry read ' for the reading session

[eduardob]

can i do a question in this place of de dbforum? if don't , where can i do it?

[eduardob]

can anybody give some solution on the following:

how can i avoid within the application (4gl) the programm abort when it gives the error -244 associated to ISAM error -143.

i have the table locked to row level

i have a unique index on the table

i have made a controll within the sqlca.sqlcode be different of zero

but the programm abort even with these controlls.

[WiccaChic]

thedafna,
Is there a way to determine how lock level was set at table creation?

[lloydnwo]

Originally posted by WiccaChic
thedafna,
Is there a way to determine how lock level was set at table creation?

Hi,

To determine the lock level, enter the foll. command at unix prompt

dbschema -d <databasename> -t <tablename> -ss
When you create a table, you can also specify the lock mode


create table test
(
col1 char(1)
)
lock mode row


Regards,

Lloyd

[vpshriyan]

Originally posted by WiccaChic
thedafna,
Is there a way to determine how lock level was set at table creation?

Hi,

One of the easiest way to get the infomation is the execute the following SQL via dbaccess or so:

select tabname,locklevel from systables where tabid>99 ;

Regards,
Shriyan

[RobP]

Some extra info, on a lot of realted questions:

- Default lock mode can be changed in the newer engines
(IDS 9.3 and 9.4) with the following parameter:
DEF_TABLE_LOCKMODE
This parameter can be set to page or row (where page is the default)

- You can avoid a 4GL prgram to crash at an error with the statement:
WHENEVER ERROR CONTINUE

- Why it works with an index and not without is also quite logical.
Without an index IDS has to search/investigate all rows for satisfying
conditions. Table is read sequentially, everything is read. By default (in
a logged database) the isolation level is commited read. So every
record that is in an open transaction may not be read. So Informix will
say: Record locked.
With an index you scan the tree (which is not locked) and only some
rows, so lock contention is less likely. So this works.

- When raising such an issue always take a look at:
+ Lock level (page/row)
+ ISOLATION LEVEL
+ LOCK MODE
+ Error capturing in your application
These four things must be set correctly to get the desired goal.

I hope this extra info was meaningfull.

Rob Prop