Could not do a physical-order read to fetch next r
A production Java app hit "Could not do a physical-order read to fetch next row" on inserts into a heavily used table, with the session hanging until it was killed. First suggestions were to check for exclusive locks and, from the online.log (bufferpool unable to extend), to resize buffers and set a SHMTOTAL limit; that didn't fix it, and oncheck -ce then reported "Could not obtain lock for Partnum" on an sbspace. Others explained the message really means lock contention with another session, since all sessions ran in "not wait" mode: the fix is SET LOCK MODE TO WAIT n, set in the application or via a PUBLIC.SYSDBOPEN procedure (running it in dbaccess affects only that session). The poster traced the lock to an IX lock on sbspace1:LO_hdr_partn but the thread ends without confirmation that the problem was solved; opening an IBM support case was also advised.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Dear Techies,
As we are facing a serious issue in our production server this evening, which
makes the transaction stop.
Issue logged in application server:
Java.sql.SQLException: Could not do a physical-order read to fetch next row.
Impact: records are not inserting in the respective table.
There is a insert query which need to hit the database and save the
transaction. but while monitoring the onstat -g sql <sid> it remains for a
long time.
Find the sample output of the onstat -g sql command below.
Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers Explain
161 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
160 - neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
159 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
158 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
157 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
156 neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
155 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
154 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
153 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
152 - neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
151 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
150 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
149 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
148 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
147 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
146 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
145 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
144 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
143 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
142 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
141 - neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
140 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
139 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
138 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
137 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
35 sysadmin DR Wait 5 0 0 - Off
34 sysadmin DR Wait 5 0 0 - Off
32 sysadmin DR Wait 5 0 0 - Off
31 sysadmin CR Not Wait 0 0 - Off
29 - neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
In the above output the sesID of INSERT statement which is making problem is
not available, because for temporary immediate solution we are killing that
particular session id.
Can anybody look into this error why it is occurring.
Need your response soon. Thank you
Hello.
Have you searched for any exclusive locks running on that mentioned table,
before starting your java program?
Pretty sure this is a highly accessed table on your OLTP, so....
1) is this a regular insert into your program, that will be running several
times during the day?
2) is this a specific long insert operation (eg bulk insert) ?
HTH
Regards.
Alexandre Marini
IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10
IBM Informix on Cloud - Database Administrator - 2017
IBM dashDB Managed Service for Analytics and Transactions - 2017
DB2 Advanced DBA - v10.5 for LUW
IBM Information Management Informix Technical Professional
IBM Certified Developer - Informix Genero
Informix independent consultant
________________________________
De: ids-bounces@iiug.org <ids-bounces@iiug.org> em nome de MUKESH TANUKU
<mukeshbt1328@gmail.com>
Enviado: quarta-feira, 21 de março de 2018 11:33
Para: ids@iiug.org
Assunto: Could not do a physical-order read to fetch next r [40893]
Dear Techies,
As we are facing a serious issue in our production server this evening, which
makes the transaction stop.
Issue logged in application server:
Java.sql.SQLException: Could not do a physical-order read to fetch next row.
Impact: records are not inserting in the respective table.
There is a insert query which need to hit the database and save the
transaction. but while monitoring the onstat -g sql <sid> it remains for a
long time.
Find the sample output of the onstat -g sql command below.
Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers Explain
161 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
160 - neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
159 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
158 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
157 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
156 neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
155 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
154 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
153 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
152 - neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
151 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
150 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
149 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
148 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
147 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
146 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
145 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
144 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
143 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
142 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
141 - neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
140 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
139 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
138 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
137 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
35 sysadmin DR Wait 5 0 0 - Off
34 sysadmin DR Wait 5 0 0 - Off
32 sysadmin DR Wait 5 0 0 - Off
31 sysadmin CR Not Wait 0 0 - Off
29 - neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
In the above output the sesID of INSERT statement which is making problem is
not available, because for temporary immediate solution we are killing that
particular session id.
Can anybody look into this error why it is occurring.
Need your response soon. Thank you
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Forgot to ask you: your online.log file shows any abnormal error during the
insert operation attempt?
If so, please paste the lines here, ok?
Tks.
Alexandre Marini
IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10
IBM Informix on Cloud - Database Administrator - 2017
IBM dashDB Managed Service for Analytics and Transactions - 2017
DB2 Advanced DBA - v10.5 for LUW
IBM Information Management Informix Technical Professional
IBM Certified Developer - Informix Genero
Informix independent consultant
________________________________
De: Alexandre Marini <alexandre_marini@hotmail.com>
Enviado: quarta-feira, 21 de março de 2018 11:43
Para: MUKESH TANUKU; ids@iiug.org
Assunto: RE: Could not do a physical-order read to fetch next r [40893]
Hello.
Have you searched for any exclusive locks running on that mentioned table,
before starting your java program?
Pretty sure this is a highly accessed table on your OLTP, so....
1) is this a regular insert into your program, that will be running several
times during the day?
2) is this a specific long insert operation (eg bulk insert) ?
HTH
Regards.
Alexandre Marini
IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10
IBM Informix on Cloud - Database Administrator - 2017
IBM dashDB Managed Service for Analytics and Transactions - 2017
DB2 Advanced DBA - v10.5 for LUW
IBM Information Management Informix Technical Professional
IBM Certified Developer - Informix Genero
Informix independent consultant
________________________________
De: ids-bounces@iiug.org <ids-bounces@iiug.org> em nome de MUKESH TANUKU
<mukeshbt1328@gmail.com>
Enviado: quarta-feira, 21 de março de 2018 11:33
Para: ids@iiug.org
Assunto: Could not do a physical-order read to fetch next r [40893]
Dear Techies,
As we are facing a serious issue in our production server this evening, which
makes the transaction stop.
Issue logged in application server:
Java.sql.SQLException: Could not do a physical-order read to fetch next row.
Impact: records are not inserting in the respective table.
There is a insert query which need to hit the database and save the
transaction. but while monitoring the onstat -g sql <sid> it remains for a
long time.
Find the sample output of the onstat -g sql command below.
Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers Explain
161 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
160 - neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
159 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
158 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
157 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
156 neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
155 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
154 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
153 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
152 - neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
151 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
150 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
149 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
148 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
147 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
146 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
145 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
144 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
143 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
142 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
141 - neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
140 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
139 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
138 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
137 SELECT neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
35 sysadmin DR Wait 5 0 0 - Off
34 sysadmin DR Wait 5 0 0 - Off
32 sysadmin DR Wait 5 0 0 - Off
31 sysadmin CR Not Wait 0 0 - Off
29 - neura_charnock_prod_live CR Not Wait 0 0 9.28 Off
In the above output the sesID of INSERT statement which is making problem is
not available, because for temporary immediate solution we are killing that
particular session id.
Can anybody look into this error why it is occurring.
Need your response soon. Thank you
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Hello Alexandre
Thanks for your prompt response.
Have you searched for any exclusive locks running on that mentioned table,
before starting your java program?
-- I didnt find any locks running.
Pretty sure this is a highly accessed table on your OLTP,
-- Yes, exactly. 117 columns with 20 foreign keys.
1) is this a regular insert into your program, that will be running several
times during the day?
--Yes atleast for every one second.
2) is this a specific long insert operation (eg bulk insert) ?
--Not exactly a bulk insert, it access for every single transaction and also
some times user will insert some multiple 20 transactions at once.
Please find the online log of todays.
Wed Mar 21 01:00:06 2018
01:00:06 Logical Log 2208 Complete, timestamp: 0x2783de95.
01:00:06 Process exited with return code 126: /bin/sh /bin/sh -c
/informix/etc/alarmprogram.sh 2 23 "Logical Log 2208 Complete, timestamp:
0x2783de95." "Logical Log 2208 Complete, timestamp: 0x2783de95." "" 23001
01:40:07 Logical Log 2209 Complete, timestamp: 0x2786ffc7.
01:40:07 Process exited with return code 126: /bin/sh /bin/sh -c
/informix/etc/alarmprogram.sh 2 23 "Logical Log 2209 Complete, timestamp:
0x2786ffc7." "Logical Log 2209 Complete, timestamp: 0x2786ffc7." "" 23001
09:04:58 Logical Log 2210 Complete, timestamp: 0x278c6870.
09:04:58 Process exited with return code 126: /bin/sh /bin/sh -c
/informix/etc/alarmprogram.sh 2 23 "Logical Log 2210 Complete, timestamp:
0x278c6870." "Logical Log 2210 Complete, timestamp: 0x278c6870." "" 23001
10:18:37 Logical Log 2211 Complete, timestamp: 0x279095a2.
10:18:37 Process exited with return code 126: /bin/sh /bin/sh -c
/informix/etc/alarmprogram.sh 2 23 "Logical Log 2211 Complete, timestamp:
0x279095a2." "Logical Log 2211 Complete, timestamp: 0x279095a2." "" 23001
11:27:50 Logical Log 2212 Complete, timestamp: 0x27956a93.
11:27:50 Process exited with return code 126: /bin/sh /bin/sh -c
/informix/etc/alarmprogram.sh 2 23 "Logical Log 2212 Complete, timestamp:
0x27956a93." "Logical Log 2212 Complete, timestamp: 0x27956a93." "" 23001
12:24:11 Logical Log 2213 Complete, timestamp: 0x279a3c3d.
12:24:11 Process exited with return code 126: /bin/sh /bin/sh -c
/informix/etc/alarmprogram.sh 2 23 "Logical Log 2213 Complete, timestamp:
0x279a3c3d." "Logical Log 2213 Complete, timestamp: 0x279a3c3d." "" 23001
13:58:53 Logical Log 2214 Complete, timestamp: 0x27a1f756.
13:58:53 Process exited with return code 126: /bin/sh /bin/sh -c
/informix/etc/alarmprogram.sh 2 23 "Logical Log 2214 Complete, timestamp:
0x27a1f756." "Logical Log 2214 Complete, timestamp: 0x27a1f756." "" 23001
16:00:51 Logical Log 2215 Complete, timestamp: 0x27a7f1e4.
16:00:51 Process exited with return code 126: /bin/sh /bin/sh -c
/informix/etc/alarmprogram.sh 2 23 "Logical Log 2215 Complete, timestamp:
0x27a7f1e4." "Logical Log 2215 Complete, timestamp: 0x27a7f1e4." "" 23001
16:07:42 sid 2190 - informix@bfbfbf15.virtua.com.br - pid -1 terminated by
onmode -z.
.
17:22:05 sid 2279 - informix@bfbfbf15.virtua.com.br - pid -1 terminated by
onmode -z.
.17:33:14 Performance Advisory: Unable to extend bufferpool 2K.
17:33:14 Results: Bufferpool has reached the memory limit.
17:33:14 Action: Increase the amount of memory the bufferpool can utilize.
17:47:30 Checkpoint Completed: duration was 1 seconds.
17:47:30 Wed Mar 21 - loguniq 2216, logpos 0x2138018, timestamp: 0x27ada386
Interval: 894
17:47:30 Maximum server connections 93
17:47:30 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0,
Plog used 18172, Llog used 90418
17:47:31 IBM Informix Dynamic Server Stopped.
17:47:46 Parameter's user-configured value was adjusted. (DS_MAX_SCANS)
17:47:46 Parameter's user-configured value was adjusted. (ONLIDX_MAXMEM)
17:47:46 IBM Informix Dynamic Server Started.
17:47:46 Requested shared memory segment size rounded from 8308KB to 8840KB
Wed Mar 21 17:47:48 2018
17:47:48 Successfully added a bufferpool of page size 2K.
17:47:48 Successfully added a bufferpool of page size 8K.
17:47:48 Event alarms enabled. ALARMPROG = '/informix/etc/alarmprogram.sh'
17:47:48 Booting Language <c> from module <>
17:47:48 Loading Module <CNULL>
17:47:48 Booting Language <builtin> from module <>
17:47:48 Loading Module <BUILTINNULL>
17:47:53 DR: DRAUTO is 0 (Off)
17:47:53 DR: ENCRYPT_HDR is 0 (HDR encryption Disabled)
17:47:53 Event notification facility epoll enabled.
17:47:53 Entries in the surrogates file /etc/informix/allowed.surrogates are
loaded into surrogate cache.
17:47:53 CCFLAGS2 value set to 0x200
17:47:53 SQL_FEAT_CTRL value set to 0x8008
17:47:53 SQL_DEF_CTRL value set to 0x4b0
17:47:53 IBM Informix Dynamic Server Version 12.10.FC8 Software Serial Number
AAA#B000000
17:47:55 IBM Informix Dynamic Server Initialized -- Shared Memory Initialized.
17:47:55 Started 1 B-tree scanners.
17:47:55 B-tree scanner threshold set at 5000.
17:47:55 B-tree scanner range scan size set to -1.
17:47:55 B-tree scanner ALICE mode set to 6.
17:47:55 B-tree scanner index compression level set to med.
17:47:55 Physical Recovery Started at Page (2:363217).
17:47:55 Physical Recovery Complete: 334 Pages Examined, 334 Pages Restored.
17:47:55 Logical Recovery Started.
17:47:55 48 recovery worker threads will be started.
17:47:56 Logical Recovery has reached the transaction cleanup phase.
17:47:56 Logical Recovery Complete.
0 Committed, 0 Rolled Back, 0 Open, 0 Bad Locks
17:47:57 Dataskip is now OFF for all dbspaces
17:47:57 Checkpoint Completed: duration was 0 seconds.
17:47:57 Wed Mar 21 - loguniq 2216, logpos 0x213a0c0, timestamp: 0x27ada140
Interval: 895
17:47:57 Maximum server connections 0
17:47:57 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0,
Plog used 361, Llog used 1
17:47:57 On-Line Mode
17:47:59 SCHAPI: Started dbScheduler thread.
17:48:00 Booting Language <spl> from module <>
17:48:00 Loading Module <SPLNULL>
17:48:00 Auto Registration is synced
17:48:00 SCHAPI: Started 2 dbWorker threads.
17:48:02 Defragmenter cleaner thread now running
17:48:02 Defragmenter cleaner thread cleaned:0 partitions
17:48:43 Dynamically allocated new virtual shared memory segment (size
2097152KB)
17:48:43 Memory sizes:resident:8840 KB, virtual:3200000 KB, no SHMTOTAL limit
17:48:54 Dynamically added 1 cpu VP
17:48:55 ** AUTO TUNING - Added CPU VP.
17:50:45 Checkpoint Completed: duration was 0 seconds.
17:50:45 Wed Mar 21 - loguniq 2216, logpos 0x216d018, timestamp: 0x27adad40
Interval: 896
17:50:45 Maximum server connections 0
17:50:45 Checkpoint Statistics - Avg. Txn Block Time 0.000, # Txns blocked 0,
Plog used 63, Llog used 51
17:50:46 IBM Informix Dynamic Server Stopped.
17:50:50 Parameter's user-configured value was adjusted. (DS_MAX_SCANS)
17:50:50 Parameter's user-configured value was adjusted. (ONLIDX_MAXMEM)
17:50:50 IBM Informix Dynamic Server Started.
17:50:50 Requested shared memory segment size rounded from 1678
Hi. From your several online log error messages, you need to redimension your buffers, I would also suggest you to put a limit for your SHM and then, if the issue persists, trace for lock contentions on your table. Obs: are you Brazilian? What is your name? Maybe we can talk and help you on this tuning. HTH. Regards. Alexandre Marini
Sure Alexandre i will increase the buffers and set the limit for SHM And im from INDIA. My name is Mukesh. If you help me on this its great. thanks Alex.
I have increased the buffers and set the SHMTOTAL to the nearby by max value.
When i rum the oncheck -ce we got an error for sbspace
ERROR: Could not obtain lock for Partnum
I have ran the onspaces -cl sbspace but the error occurs again.
Ok, open a case with IBM support.
Regards.
Alexandre Marini
IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10
IBM Informix on Cloud - Database Administrator - 2017
IBM dashDB Managed Service for Analytics and Transactions - 2017
DB2 Advanced DBA - v10.5 for LUW
IBM Information Management Informix Technical Professional
IBM Certified Developer - Informix Genero
Informix independent consultant
________________________________
De: ids-bounces@iiug.org <ids-bounces@iiug.org> em nome de MUKESH TANUKU
<mukeshbt1328@gmail.com>
Enviado: quinta-feira, 22 de março de 2018 07:45
Para: ids@iiug.org
Assunto: Re: RE: Could not do a physical-order read to .... [40904]
I have increased the buffers and set the SHMTOTAL to the nearby by max value.
When i rum the oncheck -ce we got an error for sbspace
ERROR: Could not obtain lock for Partnum
I have ran the onspaces -cl sbspace but the error occurs again.
*******************************************************************************
Forum Note: Use "Reply" to post a response in the discussion forum.
Hi Mukesh,
"Could not do a physical order to fetch next row" means another session was
accessing the table at the same time. All your sessions appear to be set to
"not wait" which is the default.
It is common practice to set a reasonable lock wait when applications connect,
e.g.:
SET LOCK MODE TO WAIT 10;
The example means a session will wait for up to 10 seconds to acquire the
necessary locks to proceed.
To get the system to work as you expect all your sessions will need to be set
to wait on locks for a reasonable time period.
If you can't change the application code you can use a public.sysdbopen
procedure to set this every time a session logs on. Look for sysdbopen
documentation in the Informix manual for this.
Ben.
Thanks Thomas,
can i know how to set the lock mode from database end.
like im executing the SET LOCK MODE TO WAIT 5; from the dbaccess selecting the
database after restarting the server.
After executing it is showing that the "lockmode is set."
But the output of onstat -g sql shows
Sess SQL Current Iso Lock SQL ISAM F.E.
Id Stmt type Database Lvl Mode ERR ERR Vers Explain
90 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
89 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
88 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
87 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
86 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
85 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
84 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
83 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
82 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
81 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
80 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
79 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
78 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
77 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
76 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
75 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
74 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
73 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
72 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
71 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
70 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
69 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
36 - sysmaster LC Not Wait 0 0 9.28 Off
35 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
32 sysadmin DR Wait 5 0 0 - Off
31 sysadmin DR Wait 5 0 0 - Off
30 sysadmin DR Wait 5 0 0 - Off
29 sysadmin CR Not Wait 0 0 - Off
It was not set to database. May i know how to set properly and where to set.
Thanks for you.
It must be set in application.
On 22.03.2018 15:25, MUKESH TANUKU wrote:
> Thanks Thomas,
> can i know how to set the lock mode from database end.
> like im executing the SET LOCK MODE TO WAIT 5; from the dbaccess selecting
the
> database after restarting the server.
> After executing it is showing that the "lockmode is set."
>
> But the output of onstat -g sql shows
>
> Sess SQL Current Iso Lock SQL ISAM F.E.
> Id Stmt type Database Lvl Mode ERR ERR Vers Explain
> 90 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
> 89 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
> 88 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
> 87 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
> 86 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
> 85 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
> 84 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
> 83 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
> 82 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
> 81 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
> 80 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
> 79 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
> 78 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
> 77 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
> 76 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
> 75 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
> 74 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
> 73 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
> 72 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
> 71 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
> 70 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
> 69 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
> 36 - sysmaster LC Not Wait 0 0 9.28 Off
> 35 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
> 32 sysadmin DR Wait 5 0 0 - Off
> 31 sysadmin DR Wait 5 0 0 - Off
> 30 sysadmin DR Wait 5 0 0 - Off
> 29 sysadmin CR Not Wait 0 0 - Off
>
> It was not set to database. May i know how to set properly and where to set.
> Thanks for you.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
--
Untitled Document
*Ivan Zavi*
/System Administrator/
+381 21 68 98 608 | +381 69 846 99 08
*M&I Systems, Co. Group*
Bulevar vojvode Stepe 16, 21000 Novi Sad, Srbija
+381 21 68 98 602 | +381 21 68 98 604
info@mi-system.co.rs | www.mi-system.co.rs
Odricanje od odgovornosti:
Ovaj dokument namenjen je samo licima kojima je upucen i za pozivanje na
isti od stane bilo kog lica, neophodna je naknadna pismena potvrda
njegovog sadraja. Shodno tome, M&I Systems, Co. Novi Sad odrice svaku
odgovornost i ne prihvata bilo kakvu obavezu (ukljucujuci slucaj
nepanje) za posledice koje moe pretrpeti bilo koje lice zbog cinjenja
ili necinjenja na bazi takve informacije pre nego to takva lica prime
dodatnu pismenu potvrdu. Ukoliko ste grekom primili ovu elektronsku
poruku, unitite ili izbriite istu sa vaeg racunara. Svako umnoavanje,
irenje, kopiranje, obelodanjivanje, izmene, distribucija i/ili
objavljivanje ove elektronske poruke je strogo zabranjeno. Sadraj ove
elektronske poruke ne predstavlja nuno stavove M&I Systems, Co. Novi Sad
Thank you. I will set this from application end. mean while the table we have found is LO_hdr_partn (this is not a transaction table) I have executed one query to find the cause of lock, the output is the session id 72 has IX lock on the sbspace1:LO_hdr_partn. But what this is doing i cant able to find.
You should try this :
CREATE PROCEDURE PUBLIC.SYSDBOPEN()
SET LOCK MODE TO WAIT n; -- replace n by the number of seconds you wantthe session to wait for the lock to be released
END PROCEDURE;
The new sessions when they try to connect will execute first this stored
procedure. However this applies to all new sessions from the time the
session connect until the first SET LOCK TO MODE TO WAIT or TO NOT WAIT
that it encounters in the application.
If your application did not use SET LOCK MODE TO WAIT ot SET LOCK MODE
TO WAIT n or SET LOCK MODE TO NOT WAIT, the default is SET LOCK MODE TO
NOT WAIT (meaning that the session does not wait for a lock if a lock is
on an object (row, index, etc) but your application will get an error
that you should trap in your application by testing the value of SQLCODE
right after the instruction or in a centralized way in a function that
traps the errors) . If your application is in the default mode , the
sysdbopen procedure will help you otherwise, you will have to modify you
application code.
Khaled Bentebal
Mobile: 33 (0) 6 07 78 41 97
Email: khaled.bentebal@consult-ix.fr
Le 22/03/2018 à 15:43, Ivan Zavis a écrit :
> It must be set in application.
>
> On 22.03.2018 15:25, MUKESH TANUKU wrote:
>> Thanks Thomas,
>> can i know how to set the lock mode from database end.
>> like im executing the SET LOCK MODE TO WAIT 5; from the dbaccess selecting
> the
>> database after restarting the server.
>> After executing it is showing that the "lockmode is set."
>>
>> But the output of onstat -g sql shows
>>
>> Sess SQL Current Iso Lock SQL ISAM F.E.
>> Id Stmt type Database Lvl Mode ERR ERR Vers Explain
>> 90 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
>> 89 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
>> 88 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
>> 87 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
>> 86 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
>> 85 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
>> 84 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
>> 83 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
>> 82 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
>> 81 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
>> 80 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
>> 79 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
>> 78 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
>> 77 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
>> 76 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
>> 75 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
>> 74 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
>> 73 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
>> 72 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
>> 71 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
>> 70 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
>> 69 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
>> 36 - sysmaster LC Not Wait 0 0 9.28 Off
>> 35 - neura_charnock_prod_live LC Not Wait 0 0 9.28 Off
>> 32 sysadmin DR Wait 5 0 0 - Off
>> 31 sysadmin DR Wait 5 0 0 - Off
>> 30 sysadmin DR Wait 5 0 0 - Off
>> 29 sysadmin CR Not Wait 0 0 - Off
>>
>> It was not set to database. May i know how to set properly and where to set.
>> Thanks for you.
>>
>>
>>
>
*******************************************************************************
>> Forum Note: Use "Reply" to post a response in the discussion forum.
>>
Related threads
- Posting from the Informix-list
- Migrating from IDS 9.40.UC6 to 11.50.UC3
- Ip for a network session
- questions onstat -g