Problems with locks
Hello
I am working with Informix 7.31 UC3 Dynamic Server at HP/UX 10.20. Since
I have migrated from Informix 7.14 to this Version, I have problems with
locks and deadlocks. I did a dbexport and a dbimport of all databases
and took care that lockelevel was set to row of all tables. Do you know,
if there is a known bug ?
Juerg Schaer wrote:
>
> Hello
>
> I am working with Informix 7.31 UC3 Dynamic Server at HP/UX 10.20. Since
> I have migrated from Informix 7.14 to this Version, I have problems with
> locks and deadlocks. I did a dbexport and a dbimport of all databases
> and took care that lockelevel was set to row of all tables. Do you know,
> if there is a known bug ?
No related bugs that I know of anyway. Did you change the logging mode of
the database from 7.14 to 7.31? If the old server had the database as
NON-LOGGED and the new server's database is LOGGED then the default ISOLATION
level is different and you will encounter much more lock contention. You can
try SET LOCK MODE TO WAIT.... or for readonly tasks SET ISOLATION DIRTY READ.
Art S. Kagel
Hello
I found out a work around. I had to update statistics low the table from
time to time. But this is only a work around and it seems to works for about
2 weeks and then I have to redo the update statistics on the OLTP-table. And
as I found out with onstat -uk, it seems to be the thread itself which
produces the lock problem. The deadlocks are growing in the onstat -p
statistics.
Juerg Schaer schrieb:
> Hello
>
> I am working with Informix 7.31 UC3 Dynamic Server at HP/UX 10.20. Since
> I have migrated from Informix 7.14 to this Version, I have problems with
> locks and deadlocks. I did a dbexport and a dbimport of all databases
> and took care that lockelevel was set to row of all tables. Do you know,
> if there is a known bug ?
Hello
Please change the lock for the database, change the default lock PAGE to
RECORD, this instruction is active from the SQL UPDATE DATABASE.
--
Juerg Schaer <jsc@imtf.ch> escribi' en el mensaje de noticias
3885721A.4AAFCCBB@imtf.ch...
> Hello
>
> I am working with Informix 7.31 UC3 Dynamic Server at HP/UX 10.20. Since
> I have migrated from Informix 7.14 to this Version, I have problems with
> locks and deadlocks. I did a dbexport and a dbimport of all databases
> and took care that lockelevel was set to row of all tables. Do you know,
> if there is a known bug ?
>
Carlos Lopez Campuzano wrote:
>
> Hello
>
> Please change the lock for the database, change the default lock PAGE to
> RECORD, this instruction is active from the SQL UPDATE DATABASE.
Carlos, there is no such statement as UPDATE DATABASE to set the default
lock mode of new tables. The default lock mode for ALL new Informix tables
is PAGE unless you either specify LOCK MODE ROW at table create time or
ALTER the table to change the lock mode to ROW later on.
> Juerg Schaer <jsc@imtf.ch> escribió en el mensaje de noticias
> 3885721A.4AAFCCBB@imtf.ch...
> > Hello
> >
> > I am working with Informix 7.31 UC3 Dynamic Server at HP/UX 10.20. Since
> > I have migrated from Informix 7.14 to this Version, I have problems with
> > locks and deadlocks. I did a dbexport and a dbimport of all databases
> > and took care that lockelevel was set to row of all tables. Do you know,
> > if there is a known bug ?
> >
To Juerg,
When you recreated the database did you change from a non-logged database to
a logged database? Since the ISOLATION LEVEL defaults to DIRTY READ for all
SELECTs in a non-logged database there are many fewer lock held. In
addition the DIRTY READ isolation causes updates to hold fewer locks also.
Changing to a logged database changes the default ISOLATION LEVEL so that
you will see more lock clashes and deadlocks if your applications are not
coded carefully. You must use SET LOCK MODE TO WAIT <nsecs> in your
applications (or SET ISOLATION DIRTY READ for applications that only query)
so that the locks do not cause lockout errors. In addition, be careful of
update programs that use different indexes to access the same table as this
can cause a virtual deadlock.
Art S. Kagel