Isolation Level
folks,
I have a stored procedure that all it does is a join lookup read:
CREATE PROCEDURE my_procedure (
in_parameter VARCHAR(25,0) ) returning
INTEGER; -- returns some count I am interested imDEFINE _count INTEGER;
select
count(tabel1.my_id)
into
_count
from
table1,table2,table3
where
table1.some_key1 matches in_parameter and
table1.my_id = table2.some_key2 and
table2.some_col1_value = 1 and
table2.some_col2_value = table3.some_col2_value and
table3.come_col3_value=1;
return
_count;
END PROCEDURE;
The problem I am facing is that for a first time in the history of
application(java application) it has thrown the below error and hence
roled back the transaction:
"java.sql.SQLException: Could not position within a table
(informix.table1) SQL Error Code: -243"
My DBA tells me that this could be because someone is locking a row on
table1 but he can't tell me any more information than that.
What I was asking him was to look into the logs (transaction logs,
etc.) and tell me the history of transactions that build up to this
error occuring so that I could try and reproduce this problem.
He informs me that this is not possible. The logs won't tell him which
process locked this table right prior to my error occuring or any other
such information that can tell me who tried to lock it.
My question to you experts out there :
1) is what he is saying accurate ? Is there no way for me to try and
figure out what was occuring to that table right prior to the error
-243 ?
2) if above is true, is there any parameters on DB to turn on
permenantly(without DB performance degradation) to be able to
troubleshoot this kind of "locking" problem if indeed that is the
problem ?
env:
Informix Dynamic Server 9.40.UC5
IBM Informix JDBC Driver for IBM Informix Dynamic Server version
2.21.JC5
thanks much for any info.
J.
Jlukar wrote:
> folks,
>
> I have a stored procedure that all it does is a join lookup read:
>
> CREATE PROCEDURE my_procedure (
> in_parameter VARCHAR(25,0) ) returning
> INTEGER; -- returns some count I am interested im> DEFINE _count INTEGER;
> select
> count(tabel1.my_id)
> into
> _count
> from
> table1,table2,table3
> where
> table1.some_key1 matches in_parameter and
> table1.my_id = table2.some_key2 and
> table2.some_col1_value = 1 and
> table2.some_col2_value = table3.some_col2_value and
> table3.come_col3_value=1;
> return
> _count;
> END PROCEDURE;
>
>
> The problem I am facing is that for a first time in the history of
> application(java application) it has thrown the below error and hence
> roled back the transaction:
>
> "java.sql.SQLException: Could not position within a table
> (informix.table1) SQL Error Code: -243"
>
>
> My DBA tells me that this could be because someone is locking a row on
> table1 but he can't tell me any more information than that.
>
> What I was asking him was to look into the logs (transaction logs,
> etc.) and tell me the history of transactions that build up to this
> error occuring so that I could try and reproduce this problem.
>
> He informs me that this is not possible. The logs won't tell him which
> process locked this table right prior to my error occuring or any other
> such information that can tell me who tried to lock it.
>
>
> My question to you experts out there :
>
> 1) is what he is saying accurate ? Is there no way for me to try and
> figure out what was occuring to that table right prior to the error
> -243 ?
I don't know of any way of doing it. I think the logical log only
records changes and not select statements in any case and it could have
been locked by another user's select statement.
> 2) if above is true, is there any parameters on DB to turn on
> permenantly(without DB performance degradation) to be able to
> troubleshoot this kind of "locking" problem if indeed that is the
> problem ?
Run "onstat -k" and cross-check with "onstat -u" and "onstat -g sql"
when the problem occurs before clearing any error messages. There are
also utilities on the IIUG web site for this kind of thing.
Your subject line reads "isolation level" but this isn't mentioned in
your post anywhere else. You can of course avoid locks by executing "SET
ISOLATION TO DIRTY READ;" somewhere near the start of your procedure,
but of course this has to suit you application. Another option is to put
in a lock wait.
Ben.
Yes ..As Ben says..It always depends on the type of your application on
which isolation to be used..?
If you use SET ISOLATION TO DIRTY READ ..it will ignore all the Locks..But
the drawback is that we will get the UNCOMMITTED data
If we want consistent data in our application we can use SET LOCK MODE TO
WAIT which will wait indefinitely for lock to be released.
we can also specify the number of seconds to wait by
SET LOCK MODE TO WAIT 20;
There will be new isolation level "COMMITTED READ LAST COMMITTED" which
will be introduced as Vnext Feature which will overcome many locking
problems.
The main advantage is that it also ignores all the locks and gets us the
LAST COMMITTED Data.
Thanks,
Radhika.
"YOUR LIFE IS NOT A COINCIDENCE... IT'S A REFLECTION OF YOU!!"
Ben Thompson
<ben@nomonitorsof
tspam.com> To
Sent by: informix-list@iiug.org
informix-list-bou cc
nces@iiug.org
Subject
Re: Isolation Level
22/08/2006 13:47
Jlukar wrote:
> folks,
>
> I have a stored procedure that all it does is a join lookup read:
>
> CREATE PROCEDURE my_procedure (
> in_parameter VARCHAR(25,0) ) returning
> INTEGER; -- returns some count I am interested im> DEFINE _count INTEGER;
> select
> count(tabel1.my_id)
> into
> _count
> from
> table1,table2,table3
> where
> table1.some_key1 matches in_parameter and
> table1.my_id = table2.some_key2 and
> table2.some_col1_value = 1 and
> table2.some_col2_value = table3.some_col2_value and
> table3.come_col3_value=1;
> return
> _count;
> END PROCEDURE;
>
>
> The problem I am facing is that for a first time in the history of
> application(java application) it has thrown the below error and hence
> roled back the transaction:
>
> "java.sql.SQLException: Could not position within a table
> (informix.table1) SQL Error Code: -243"
>
>
> My DBA tells me that this could be because someone is locking a row on
> table1 but he can't tell me any more information than that.
>
> What I was asking him was to look into the logs (transaction logs,
> etc.) and tell me the history of transactions that build up to this
> error occuring so that I could try and reproduce this problem.
>
> He informs me that this is not possible. The logs won't tell him which
> process locked this table right prior to my error occuring or any other
> such information that can tell me who tried to lock it.
>
>
> My question to you experts out there :
>
> 1) is what he is saying accurate ? Is there no way for me to try and
> figure out what was occuring to that table right prior to the error
> -243 ?
I don't know of any way of doing it. I think the logical log only
records changes and not select statements in any case and it could have
been locked by another user's select statement.
> 2) if above is true, is there any parameters on DB to turn on
> permenantly(without DB performance degradation) to be able to
> troubleshoot this kind of "locking" problem if indeed that is the
> problem ?
Run "onstat -k" and cross-check with "onstat -u" and "onstat -g sql"
when the problem occurs before clearing any error messages. There are
also utilities on the IIUG web site for this kind of thing.
Your subject line reads "isolation level" but this isn't mentioned in
your post anywhere else. You can of course avoid locks by executing "SET
ISOLATION TO DIRTY READ;" somewhere near the start of your procedure,
but of course this has to suit you application. Another option is to put
in a lock wait.
Ben.
_______________________________________________
Informix-list mailing list
Informix-list@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list