Re: Friendly lock mode
"Art S. Kagel" <kagel@bloomberg.net> wrote in message
news:39C0CD7D.40C20DAE@bloomberg.net...
> Peter Komanns wrote:
> >
> > Hi all!
> > The question is this: when some transaction locks a row (as for an
update)
> > other tasks can't modify that row until the modifying transaction has
been
> > closed with a commit or rollback statement.
> > So, any transaction coming after that update and before the transaction
> > owing the update has benne closed must (or may) wait for the first
> > transaction complection.
> > Informix does this using the SET LOCK MODE TO WAIT, optionally using a
> > timeout in secs.
> > The matter is that a program, and the operator behind it, does not know
it
> > is waiting for a lock due to a transaction completion.
> > So I resolved that situation using a SET LOCK MODE TO NOT WAIT and
trapping
> > the SQL and ISAM error showing a wait lock, popping a dialog to the
operator
> > and letting him choose: leave his transaction o retry the locked
operation.
> > For instance:
> > DECLARE cursor for update
> >
> > OPEN cursor
> >
> > FETCH cursor <-------------------------------
> > Retry---|
> > locked: what do I do now ?
> > abort --> close -
> > rollback - exit
> >
> > The problem is that with IDS rel 9.21 when the first transaction (the
> > lockinig) finishes, a retry by the second transaction results in a
> > SQLNOTFOUND ( 100 ) error!
> >
> > Why ? Is something changed in the lock mechanism ?
> > How can I trap locks without using SET LOCK MODE TO WAIT ?
>
> Are you closing and reopening the CURSOR? You have to do so to
reinitialize
> the query, just fetching again, even though the previous fetch encountered
a
> lock condition, will not refetch the offending row but try to fetch the
> NEXT row in the existing cursor. If there were only one, or that last one
> was indeed the last in the result set, then SQLNOTFOUND is the correct
> response to another FETCH.
In the meantime, I have tested a different behavior:
If you set the ISOLATION MODE TO DIRTY READ the behavior is the one I
described: the waiting process gets a "SQLNOTFOUND".
If you set the ISOLATION MODE TO CURSOR STABILITY the behavioe is that, when
the first process unlocks the row, the second gets the correct row without
any error.
You may experience this using the ESQL/C code at the end of the message:
call the program from one session and again from another one. If you use it
without any argument it uses dirty read and you get the error; if you call
at leat the second process with "-nodirty" argument, all behave OK.
The question is : why with IDS 7.xx (and before it with OnLine 5) DIRTY READ
did not throw any SQLNOTFOUND error ?
Thanks
Peter Komanns
> .......
> Art S. Kagel
============================================================================
============
/*
lock.ec - lock test
usage: lock [-nodirty]
-nodirty - toggle isolation mode from dirty read (default) to cursor
stability
------- CUT ------- ------- CUT ------- ------- CUT ------- -------
CUT -------
create table foo ( foo0 char(8),foo1 char(30));
insert into foo( foo0,foo1) values ("firstone","I am the first!");
insert into foo( foo0,foo1) values ("secndone","I am the second!");
create unique index foo0i on foo(foo0);------- CUT ------- ------- CUT ------- ------- CUT ------- -------
CUT -------
compile with:
esql -o lock lock.ec
*/
#include <stdio.h>
#include <stdlib.h>
#include <unistd.h>
#include <stdarg.h>
EXEC SQL include sqlca;
EXEC SQL include sqlda;
EXEC SQL BEGIN DECLARE SECTION;
static string foo1[32];
mint exception_count,nex;
char overflow[2];
string class_origin_val[255];
string subclass_origin_val[255];
string message_text_val[8191];
mint messlength_val;
string retsql[8];
EXEC SQL END DECLARE SECTION;
static int err(long eno,char *fmt, ... );
int main(int ac,char *av[])
{
int k;
short dodirty=1;
for(k=1;k<ac;k++)
{
if(!strcmp(av[k],"-nodirty"))
dodirty=0;
}
EXEC SQL database test;
(void)err(sqlca.sqlcode,"Database test");
if(dodirty)
{
EXEC SQL set isolation to cursor dirty read;
(void)err(sqlca.sqlcode,"Set isolation (1)");
}
else
{
EXEC SQL set isolation to cursor stability;
(void)err(sqlca.sqlcode,"Set isolation (2)");
}
EXEC SQL begin work;
(void)err(sqlca.sqlcode,"Begin Work");
EXEC SQL declare c1 cursor for select
foo1 into :foo1
from foo
where foo0="firstone"
for update;
(void)err(sqlca.sqlcode,"Declare c1");
EXEC SQL open c1;
(void)err(sqlca.sqlcode,"Open c1");
Loop:;
EXEC SQL fetch c1;
switch(sqlca.sqlcode)
{
case -243L:
case -244L:
printf("**LOCK!(%ld)*\\n*",sqlca.sqlcode);
printf("ENTER=retry INTERRUPT=exit");
fflush(stdout);
k=getchar();
goto Loop;
default:
if(err(sqlca.sqlcode,"Fetch c1"))
{
printf("SQLNOTFOUND!!!!!\\n");
printf("** Program aborted! **\\n");
exit(0);
}
break;
}
printf("Fetch OK (%s) - press ENTER to continue!",foo1);
fflush(stdout);
k=getchar();
EXEC SQL update foo set foo1 = :foo1
where current of c1;
EXEC SQL close c1;
EXEC SQL rollback work;
(void)err(sqlca.sqlcode,"rollback Work");
printf("*** Program over ***\\n");
return 0;
}
/* Err */
static int err(long eno,char *fmt, ... )
{
va_list ap;
char msgbuf[4096];
mint len;
if( !eno || eno==SQLNOTFOUND )
return (int)eno;
va_start(ap,fmt);
(void)memset((void *)msgbuf,0,4096);
if(rgetlmsg(eno,msgbuf,4096,&len))
strcpy(msgbuf,"SQL message not found!");
printf("SQL Error #%ld (",eno);
printf(msgbuf,sqlca.sqlerrm);
printf(")!\\n");
printf("Program msg is \\"");
vprintf(fmt,ap);
printf("\\"\\n");
printf("Error Message is \\"%s\\"\\n",sqlca.sqlerrm);
printf("ISAM error code : %ld\\n",sqlca.sqlerrd[1]);
printf("# rows processed : %ld\\n",sqlca.sqlerrd[2]);
printf("Offset char in err.: %ld\\n",sqlca.sqlerrd[4]);
printf("ROWID after insert : %ld\\n",sqlca.sqlerrd[5]);
printf("Warnings present :[%c]\\n",sqlca.sqlwarn.sqlwarn0);
if( sqlca.sqlwarn.sqlwarn0 == 'W' )
{
printf(
"Data item truncated (database has TX log)[%c]\\n",
sqlca.sqlwarn.sqlwarn1);
printf(
"Aggregate encountered NULL (MODE ANSI database)[%c]\\n",
sqlca.sqlwarn.sqlwarn2);
printf(
"Mismatch betw.select-list/INTO (OnLine Engine)[%c]\\n",