Strange Problem in In
Hi all,
I have a strange problem with informix.
I have two programs simultaneously accessing a table T.
T has row locking.
program 1 :
===========
set lock mode to wait;begin work;
update row T.ROW in table T;
commit work;
program 2 :
===========
set lock mode to wait;find all rows in table T ; /* actually in a cursor */
it seems that in output of program 2 ( that is in its cursor )
row T.ROW does not get selected,if at that exact moment it's locked by
program 1. ( as if that row was not there in table T )
why is it happening ? how to avoid it ? and how can i simulate the
situation for testing ? could anyone advice ?
Cheers,
Sandy.
--
Such is life and such is growing
And thru' mistakes, we end up in knowing !!!
Sent via Deja.com http://www.deja.com/
Before you buy.
Simulation:
For your update session use dbaccess. Work only with a few rows.
Start your program 1 in dbaccess but DO NOT commit.
Now start program 2 and look what happen.
I would expect that program 2 waits until you commit your update in dbaccess session.
Reinhard
SmartJaggu <sujaggu@yahoo.com> schrieb in im Newsbeitrag: 83nr8n$kc0$1@nnrp1.deja.com...
> Hi all,
>
> I have a strange problem with informix.
> I have two programs simultaneously accessing a table T.
> T has row locking.
>
> program 1 :
> ===========
> set lock mode to wait;> begin work;
> update row T.ROW in table T;
> commit work;
>
> program 2 :
> ===========
> set lock mode to wait;> find all rows in table T ; /* actually in a cursor */
>
> it seems that in output of program 2 ( that is in its cursor )
> row T.ROW does not get selected,if at that exact moment it's locked by
> program 1. ( as if that row was not there in table T )
>
> why is it happening ? how to avoid it ? and how can i simulate the
> situation for testing ? could anyone advice ?
>
> Cheers,
> Sandy.
>
>
> --
> Such is life and such is growing
> And thru' mistakes, we end up in knowing !!!
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
In article <83nr8n$kc0$1@nnrp1.deja.com>,
SmartJaggu <sujaggu@yahoo.com> wrote:
> Hi all,
>
> I have a strange problem with informix.
> I have two programs simultaneously accessing a table T.
> T has row locking.
>
> program 1 :
> ===========
> set lock mode to wait;> begin work;
> update row T.ROW in table T;
> commit work;
>
> program 2 :
> ===========
> set lock mode to wait;> find all rows in table T ; /* actually in a cursor */
>
> it seems that in output of program 2 ( that is in its cursor )
> row T.ROW does not get selected,if at that exact moment it's locked by
> program 1. ( as if that row was not there in table T )
>
> why is it happening ? how to avoid it ? and how can i simulate the
> situation for testing ? could anyone advice ?
>
> Cheers,
> Sandy.
>
It's happening because when the cursor is opened, program 1 hasn't
committed it's work and the record isn't available. To avoid it, you
can have program 1 lock the row in exclusive mode and have program 2 do
a set lock mode to wait 1 (or maybe 2). That should be more than enough
time for program 1 to finish unless the table is huge and/or you are
updating with a where clause on an unindexed column. Another option
(you'd have to test this) is to have program 2 do a 'set optimization to
dirty read'. Look it up, it might not be what you're looking for.
--
# unrm /
ksh: unrm: not found
# man cpio
Sent via Deja.com http://www.deja.com/
Before you buy.