Table locking problem.
Hi, We have Informix 7.3x on HP-UX. The problem is whenever a user is trying to update a table the user is not able to query also. There are around 100 users in our system. They get error 244 when they are trying to query a row. I used to think that informix uses row label locks by default. But it seems here it is page lable. Are we doing something wrong. Do we need to set some parameter in the config file so that the locks are row label. I am getting lot of complaints because of this. Any help will be greatly appreciated. Please send a copy of your reply to "asharma@uswest.com". Thanks Arvind
Hi there,
if you are not sure about the lock level of your tables
you should test them, first.
Try this select:
set isolation to dirty read;
select tabname, locklevel
from informix.systables
where locklevel != "R"
and tabid > 99
order by 1
This should give a complete list of tables not using row level
locking.
If your table is in the desired level locking mode,
you should consider the following action:
Use 'lock mode to wait' and/or 'isolation to dirty read'
and shorten your update transactions.
I think there could be another possible problem that causes
those locks:
what about updates on indexed columns ?
Are their index pages locked, too ? If so: how can I
monitor these ?
(Comments about this appreciated !)
In article <37D7CE43.9B5866B9@uswest.com>,
Arvind Sharma <asharma@uswest.com> wrote:
> This is a multi-part message in MIME format.
> --------------2D4F658562E7DA36581A3926
> Content-Type: text/plain; charset=us-ascii
> Content-Transfer-Encoding: 7bit
>
> Hi,
> We have Informix 7.3x on HP-UX. The problem is whenever a user is
> trying to update a table the user is not able to query also. There are
> around 100 users in our system. They get error 244 when they are
trying
> to query a row. I used to think that informix uses row label locks by
> default. But it seems here it is page lable. Are we doing something
> wrong. Do we need to set some parameter in the config file so that the
> locks are row label.
ALTER TABLE tabname LOCK MODE ROW
You don't need any changes in your configuration files.
--
With best regards, Yuri Dovgart,
SAP R/3, Informix consultant,
"Telecominvest" company.
E-mail y_dovgart@tci.ukrtel.net
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
In article <37D7CE43.9B5866B9@uswest.com>,
Arvind Sharma <asharma@uswest.com> wrote:
> We have Informix 7.3x on HP-UX. The problem is whenever a user is
> trying to update a table the user is not able to query also. There are
> around 100 users in our system. They get error 244 when they are
> trying to query a row. I used to think that informix uses row label
> locks by default. But it seems here it is page lable. Are we doing
> something wrong. Do we need to set some parameter in the config file
> so that the locks are row label.
Arvind,
by default, all tables are built with "lock mode page". There is no
configuration parameter to control this. If you want row-level
locking, then you should create the table that way:
create table yada
(some_columns integer) lock mode row;
If the table was already built with page-level locking, you can later
the table:
alter table yada lock mode row;
Note:
To be able to do this you must have alter permission on the table and
must have exclusive access to it for a few seconds. This latter point
may take hours to get but if you ask everyone to get the *&^^%! off the
system for a moment, it's no problem.
Good luck!
--
+---- Jacob Salomon - DBA ---------------------------------------------+
|--------------- Obligatory sesquipedalian obfuscation: ---------------|
| An object of igneous, sedimentary or metamorphic mineral in combined |
| states of elevated linear and rotational kinetic energy acquires no |
| accumulation of bryophytic vegetation. |
+----------------------------------------------------------------------+
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
Thanks all for answers. It solved my problem.
Arvind
Christian Brauer wrote:
> Hi there,
>
> if you are not sure about the lock level of your tables
> you should test them, first.
>
> Try this select:
>
> set isolation to dirty read;
> select tabname, locklevel
> from informix.systables
> where locklevel != "R"
> and tabid > 99
> order by 1>
> This should give a complete list of tables not using row level
> locking.
>
> If your table is in the desired level locking mode,
> you should consider the following action:
>
> Use 'lock mode to wait' and/or 'isolation to dirty read'
> and shorten your update transactions.
>
> I think there could be another possible problem that causes
> those locks:
>
> what about updates on indexed columns ?
> Are their index pages locked, too ? If so: how can I
> monitor these ?
>
> (Comments about this appreciated !)