Problem with duplicate values in table on SDS serv
I am running Informix 11.50FC3 on HP-UX 11.23.
We have a primary server and an updateable secondary server (SDS) running.
We encountered a problem where we have an esqlc application running which
runs multiple processes at once.
We have a table called "fileid" which consists of 6 rows which contain a
counter of the next value for certain processes.
When a process calls the routine to increment the next value and return it
to the process to use for the fields value, on the SDS server it sometimes
will return the same value for two different processes. It does not do this
on the primary, the primary has unique values.
We have found this on the production instance and have replicated it in a
development instance.
We opened a case with tech support tonight PMR 03011 122
Has anyone ran into an issue like this??
A quick review of what we are doing.
Process calls a function, function does the following and returns the next
value
Begin work;
select fid+1 from fileid where fid_key = 2 for update of fid;
update fileid set fid = ? where current of ref_cur
commit work;
Returns next value to program
Here is the ESQLC code:
#include "sysdep.h"
static char id[] __attribute__((__unused__))= " ";
EXEC SQL INCLUDE SQLCA;
#include <time.h>
#include <stdio.h>
#include <string.h>
#include <unistd.h>
#include "codes.h"
#include "sqlDefines.h"
#include "sqlFcts.h"
#include "doqual.h"
#include "commarea.h"
#include "obtainRef.h"
int obtainRef( COMMAREA *pst_comm )
{
EXEC SQL BEGIN DECLARE SECTION;
int i_tableref = 0;
EXEC SQL END DECLARE SECTION;
int i_ctr = 0;
int i_ret_code = 0;
static int i_sel_ref = NO;
static int i_upd_ref = NO;
/*
PREPARE THE CURSOR STATEMENT
*/
if( i_sel_ref == NO )
{
exec sql prepare oref_sel_fid from
'select fid from fileid where fid_key = 2 for update of
fid';
if ( sqlca.sqlcode != SQL_OK )
{
pst_comm->inqControl = -1;
fprintf(Gfp_err,"OBTAINREF: Prepare oref_sel_fid
Error\\
");
sqlErr(Gfp_err,Gs_prog_name,"obtainRef");
return FAILURE;
}
i_sel_ref = YES;
}
/*
PREPARE THE UPDATE STATEMENT
*/
if( i_upd_ref == NO )
{
exec sql prepare oref_upd_fid from
'update fileid set fid = ? where current of ref_cur';
if ( sqlca.sqlcode != SQL_OK )
{
pst_comm->inqControl = -1;
fprintf(Gfp_err,"OBTAINREF: Prepare oref_upd_fid
Error\\
");
sqlErr(Gfp_err,Gs_prog_name,"obtainRef");
return FAILURE;
}
i_upd_ref = YES;
}
/*
OPEN AND DECLARE THE CURSOR
*/
if ( ( i_ret_code = sqlBeginWork() ) == FAILURE )
{
return FAILURE;
}
exec sql declare ref_cur cursor for oref_sel_fid;
exec sql open ref_cur;
if( sqlca.sqlcode != SQL_OK )
{
pst_comm->inqControl = -1;
fprintf(Gfp_err,"OBTAINREF: Open unsuccessful\\
");
sqlErr(Gfp_err,Gs_prog_name,"obtainRef");
sqlRollbackWork();
return FAILURE;
}
for( i_ctr = 0; i_ctr <= SQL_MAX_RETRIES; i_ctr++ )
{
exec sql fetch ref_cur into :i_tableref;
if ( RECORD_IS_LOCKED )
{
/*
IF THE RETRY COUNT = SQL_MAX_RETRIES THEN RETURN
FAILURE
*/
fprintf(Gfp_err,"fetch Record Lock Condition - %d\\
",
i_ctr );
usleep( SQL_SLEEP );
if ( i_ctr == SQL_MAX_RETRIES )
{
pst_comm->inqControl = -1;
fprintf(Gfp_err,"Record Lock Condition - Fetch\\
" );
sqlErr(Gfp_err,Gs_prog_name,"obtainRef");
sqlRollbackWork();
return FAILURE;
}
}
else if ( sqlca.sqlcode != SQL_OK )
{
/*
AN ERROR OCCURRED SO REPORT THE ERROR AND RETURN
FAILURE
*/
pst_comm->inqControl = -1;
fprintf(Gfp_err,"OBTAINREF: select: sql error\\
");
sqlErr(Gfp_err,Gs_prog_name,"obtainRef");
sqlRollbackWork();
return FAILURE;
}
else
{
break;
}
}
/*
I DO NOT HAVE TO LOOP THIS (I THINK) BECAUSE
THE FETCH PUT A LOCK ON IT
*/
++i_tableref;
for( i_ctr = 0; i_ctr <= SQL_MAX_RETRIES; i_ctr++ )
{
exec sql execute oref_upd_fid using :i_tableref;
if ( RECORD_IS_LOCKED )
{
/*
IF THE RETRY COUNT = SQL_MAX_RETRIES THEN RETURN
FAILURE
*/
fprintf(Gfp_err,"update Record Lock Condition - %d\\
",
i_ctr );
usleep( SQL_SLEEP );
if ( i_ctr == SQL_MAX_RETRIES )
{
pst_comm->inqControl = -1;
fprintf(Gfp_err,"Record Lock Condition - Update\\
"
);
sqlErr(Gfp_err,Gs_prog_name,"obtainRef");
sqlRollbackWork();
return FAILURE;
}
}
else if ( sqlca.sqlcode != SQL_OK )
{
pst_comm->inqControl = -1;
fprintf(Gfp_err,"OBTAINREF: update: sql error\\
");
sqlErr(Gfp_err,Gs_prog_name,"obtainRef");
sqlRollbackWork();
return FAILURE;
}
else
{
fprintf(Gfp_err,"OBTAINREF: reference number: %d\\
",
i_tableref);
pst_comm->inqControl = i_tableref;
break;
}
} /* RETRY LOOP */
if ( ( i_ret_code = sqlCommitWork() ) == FAILURE )
{
return FAILURE;
}
return SUCCESS;
}
Thanks, Jeff
I suspect that the two apps are each retrieving the value before the update
finally occurs on the primary and is shipped to the secondary. The
solution:
Modify your application to depend on a SERIAL, SERIAL8, or BIGSERIAL column
in the table into which these 'next values' are being written as keys. Then
all sessions on all servers depend on the value actually inserted on the
primary.
Art
On Tue, Feb 17, 2009 at 10:55 PM, Jeff Filippi
<iiug@itdataconsulting.com>wrote:
> I am running Informix 11.50FC3 on HP-UX 11.23.
>
> We have a primary server and an updateable secondary server (SDS) running.
>
> We encountered a problem where we have an esqlc application running which
> runs multiple processes at once.
>
> We have a table called "fileid" which consists of 6 rows which contain a
> counter of the next value for certain processes.
>
> When a process calls the routine to increment the next value and return it
> to the process to use for the fields value, on the SDS server it sometimes
> will return the same value for two different processes. It does not do this
> on the primary, the primary has unique values.
>
> We have found this on the production instance and have replicated it in a
> development instance.
>
> We opened a case with tech support tonight PMR 03011 122
>
> Has anyone ran into an issue like this??
>
> A quick review of what we are doing.
>
> Process calls a function, function does the following and returns the next
> value
>
> Begin work;
>
> select fid+1 from fileid where fid_key = 2 for update of fid;
>
> update fileid set fid = ? where current of ref_cur>
> commit work;
>
> Returns next value to program
>
> Here is the ESQLC code:
>
> #include "sysdep.h"
>
> static char id[] __attribute__((__unused__))= " ";
>
> EXEC SQL INCLUDE SQLCA;
>
> #include <time.h>
>
> #include <stdio.h>
>
> #include <string.h>
>
> #include <unistd.h>
>
> #include "codes.h"
>
> #include "sqlDefines.h"
>
> #include "sqlFcts.h"
>
> #include "doqual.h"
>
> #include "commarea.h"
>
> #include "obtainRef.h"
>
> int obtainRef( COMMAREA *pst_comm )
>
> {
>
> EXEC SQL BEGIN DECLARE SECTION;
>
> int i_tableref = 0;
>
> EXEC SQL END DECLARE SECTION;
>
> int i_ctr = 0;
>
> int i_ret_code = 0;
>
> static int i_sel_ref = NO;
>
> static int i_upd_ref = NO;
>
> /*
>
> PREPARE THE CURSOR STATEMENT
>
> */
>
> if( i_sel_ref == NO )
>
> {
>
> exec sql prepare oref_sel_fid from
>
> 'select fid from fileid where fid_key = 2 for update of
> fid';
>
> if ( sqlca.sqlcode != SQL_OK )
>
> {
>
> pst_comm->inqControl = -1;
>
> fprintf(Gfp_err,"OBTAINREF: Prepare oref_sel_fid
> Error\\
");
>
> sqlErr(Gfp_err,Gs_prog_name,"obtainRef");
>
> return FAILURE;
>
> }
>
> i_sel_ref = YES;
>
> }
>
> /*
>
> PREPARE THE UPDATE STATEMENT
>
> */
>
> if( i_upd_ref == NO )
>
> {
>
> exec sql prepare oref_upd_fid from
>
> 'update fileid set fid = ? where current of ref_cur';
>
> if ( sqlca.sqlcode != SQL_OK )
>
> {
>
> pst_comm->inqControl = -1;
>
> fprintf(Gfp_err,"OBTAINREF: Prepare oref_upd_fid
> Error\\
");
>
> sqlErr(Gfp_err,Gs_prog_name,"obtainRef");
>
> return FAILURE;
>
> }
>
> i_upd_ref = YES;
>
> }
>
> /*
>
> OPEN AND DECLARE THE CURSOR
>
> */
>
> if ( ( i_ret_code = sqlBeginWork() ) == FAILURE )
>
> {
>
> return FAILURE;
>
> }
>
> exec sql declare ref_cur cursor for oref_sel_fid;
>
> exec sql open ref_cur;
>
> if( sqlca.sqlcode != SQL_OK )
>
> {
>
> pst_comm->inqControl = -1;
>
> fprintf(Gfp_err,"OBTAINREF: Open unsuccessful\\
");
>
> sqlErr(Gfp_err,Gs_prog_name,"obtainRef");
>
> sqlRollbackWork();
>
> return FAILURE;
>
> }
>
> for( i_ctr = 0; i_ctr <= SQL_MAX_RETRIES; i_ctr++ )
>
> {
>
> exec sql fetch ref_cur into :i_tableref;
>
> if ( RECORD_IS_LOCKED )
>
> {
>
> /*
>
> IF THE RETRY COUNT = SQL_MAX_RETRIES THEN RETURN
> FAILURE
>
> */
>
> fprintf(Gfp_err,"fetch Record Lock Condition - %d\\
",
> i_ctr );
>
> usleep( SQL_SLEEP );
>
> if ( i_ctr == SQL_MAX_RETRIES )
>
> {
>
> pst_comm->inqControl = -1;
>
> fprintf(Gfp_err,"Record Lock Condition - Fetch\\
" );
>
> sqlErr(Gfp_err,Gs_prog_name,"obtainRef");
>
> sqlRollbackWork();
>
> return FAILURE;
>
> }
>
> }
>
> else if ( sqlca.sqlcode != SQL_OK )
>
> {
>
> /*
>
> AN ERROR OCCURRED SO REPORT THE ERROR AND RETURN
> FAILURE
>
> */
>
> pst_comm->inqControl = -1;
>
> fprintf(Gfp_err,"OBTAINREF: select: sql error\\
");
>
> sqlErr(Gfp_err,Gs_prog_name,"obtainRef");
>
> sqlRollbackWork();
>
> return FAILURE;
>
> }
>
> else
>
> {
>
> break;
>
> }
>
> }
>
> /*
>
> I DO NOT HAVE TO LOOP THIS (I THINK) BECAUSE
>
> THE FETCH PUT A LOCK ON IT
>
> */
>
> ++i_tableref;
>
> for( i_ctr = 0; i_ctr <= SQL_MAX_RETRIES; i_ctr++ )
>
> {
>
> exec sql execute oref_upd_fid using :i_tableref;
>
> if ( RECORD_IS_LOCKED )
>
> {
>
> /*
>
> IF THE RETRY COUNT = SQL_MAX_RETRIES THEN RETURN
> FAILURE
>
> */
>
> fprintf(Gfp_err,"update Record Lock Condition - %d\\
",
> i_ctr );
>
> usleep( SQL_SLEEP );
>
> if ( i_ctr == SQL_MAX_RETRIES )
>
> {
>
> pst_comm->inqControl = -1;
>
> fprintf(Gfp_err,"Record Lock Condition - Update\\
"
> );
>
> sqlErr(Gfp_err,Gs_prog_name,"obtainRef");
>
> sqlRollbackWork();
>
> return FAILURE;
>
> }
>
> }
>
> else if ( sqlca.sqlcode != SQL_OK )
>
> {
>
> pst_comm->inqControl = -1;
>
> fprintf(Gfp_err,"OBTAINREF: update: sql error\\
");
>
> sqlErr(Gfp_err,Gs_prog_name,"obtainRef");
>
> sqlRollbackWork();
>
> return FAILURE;
>
> }
>
> else
>
> {
>
> fprintf(Gfp_err,"OBTAINREF: reference number: %d\\
",
> i_tableref);
>
> pst_comm->inqControl = i_tableref;
>
> break;
>
> }
>
> } /* RETRY LOOP */
>
> if ( ( i_ret_code = sqlCommitWork() ) == FAILURE )
>
> {
>
> return FAILURE;
>
> }
>
> return SUCCESS;
>
> }
>
> Thanks, Jeff
>
>
>
>
*******************************************************************************
> Forum Note: Use "
Contact me directly
Thx.
-------------------------------------
Madison Pruet, STSM
IDS Replication Architect
=
"Jeff Filippi" =
<iiug@itdataconsu =
lting.com> =
To
Sent by: ids@iiug.org =
ids-bounces@iiug. =
cc
org =
Subj=
ect
Problem with duplicate values in=
02/17/2009 09:55 table on SDS .... [14919] =
PM =
=
=
Please respond to =
ids@iiug.org =
=
=
I am running Informix 11.50FC3 on HP-UX 11.23.
We have a primary server and an updateable secondary server (SDS) runni=
ng.
We encountered a problem where we have an esqlc application running whi=
ch
runs multiple processes at once.
We have a table called "fileid" which consists of 6 rows which contain =
a
counter of the next value for certain processes.
When a process calls the routine to increment the next value and return=
it
to the process to use for the fields value, on the SDS server it someti=
mes
will return the same value for two different processes. It does not do =
this
on the primary, the primary has unique values.
We have found this on the production instance and have replicated it in=
a
development instance.
We opened a case with tech support tonight PMR 03011 122
Has anyone ran into an issue like this??
A quick review of what we are doing.
Process calls a function, function does the following and returns the n=
ext
value
Begin work;
select fid+1 from fileid where fid_key =3D 2 for update of fid;
update fileid set fid =3D ? where current of ref_cur
commit work;
Returns next value to program
Here is the ESQLC code:
#include "sysdep.h"
static char id[] __attribute__((__unused__))=3D " ";
EXEC SQL INCLUDE SQLCA;
#include <time.h>
#include <stdio.h>
#include <string.h>
#include <unistd.h>
#include "codes.h"
#include "sqlDefines.h"
#include "sqlFcts.h"
#include "doqual.h"
#include "commarea.h"
#include "obtainRef.h"
int obtainRef( COMMAREA *pst_comm )
{
EXEC SQL BEGIN DECLARE SECTION;
int i_tableref =3D 0;
EXEC SQL END DECLARE SECTION;
int i_ctr =3D 0;
int i_ret_code =3D 0;
static int i_sel_ref =3D NO;
static int i_upd_ref =3D NO;
/*
PREPARE THE CURSOR STATEMENT
*/
if( i_sel_ref =3D=3D NO )
{
exec sql prepare oref_sel_fid from
'select fid from fileid where fid_key =3D 2 for update of
fid';
if ( sqlca.sqlcode !=3D SQL_OK )
{
pst_comm->inqControl =3D -1;
fprintf(Gfp_err,"OBTAINREF: Prepare oref_sel_fid
Error\\
");
sqlErr(Gfp_err,Gs_prog_name,"obtainRef");
return FAILURE;
}
i_sel_ref =3D YES;
}
/*
PREPARE THE UPDATE STATEMENT
*/
if( i_upd_ref =3D=3D NO )
{
exec sql prepare oref_upd_fid from
'update fileid set fid =3D ? where current of ref_cur';
if ( sqlca.sqlcode !=3D SQL_OK )
{
pst_comm->inqControl =3D -1;
fprintf(Gfp_err,"OBTAINREF: Prepare oref_upd_fid
Error\\
");
sqlErr(Gfp_err,Gs_prog_name,"obtainRef");
return FAILURE;
}
i_upd_ref =3D YES;
}
/*
OPEN AND DECLARE THE CURSOR
*/
if ( ( i_ret_code =3D sqlBeginWork() ) =3D=3D FAILURE )
{
return FAILURE;
}
exec sql declare ref_cur cursor for oref_sel_fid;
exec sql open ref_cur;
if( sqlca.sqlcode !=3D SQL_OK )
{
pst_comm->inqControl =3D -1;
fprintf(Gfp_err,"OBTAINREF: Open unsuccessful\\
");
sqlErr(Gfp_err,Gs_prog_name,"obtainRef");
sqlRollbackWork();
return FAILURE;
}
for( i_ctr =3D 0; i_ctr <=3D SQL_MAX_RETRIES; i_ctr++ )
{
exec sql fetch ref_cur into :i_tableref;
if ( RECORD_IS_LOCKED )
{
/*
IF THE RETRY COUNT =3D SQL_MAX_RETRIES THEN RETURN
FAILURE
*/
fprintf(Gfp_err,"fetch Record Lock Condition - %d\\
",
i_ctr );
usleep( SQL_SLEEP );
if ( i_ctr =3D=3D SQL_MAX_RETRIES )
{
pst_comm->inqControl =3D -1;
fprintf(Gfp_err,"Record Lock Condition - Fetch\\
" );
sqlErr(Gfp_err,Gs_prog_name,"obtainRef");
sqlRollbackWork();
return FAILURE;
}
}
else if ( sqlca.sqlcode !=3D SQL_OK )
{
/*
AN ERROR OCCURRED SO REPORT THE ERROR AND RETURN
FAILURE
*/
pst_comm->inqControl =3D -1;
fprintf(Gfp_err,"OBTAINREF: select: sql error\\
");
sqlErr(Gfp_err,Gs_prog_name,"obtainRef");
sqlRollbackWork();
return FAILURE;
}
else
{
break;
}
}
/*
I DO NOT HAVE TO LOOP THIS (I THINK) BECAUSE
THE FETCH PUT A LOCK ON IT
*/
++i_tableref;
for( i_ctr =3D 0; i_ctr <=3D SQL_MAX_RETRIES; i_ctr++ )
{
exec sql execute oref_upd_fid using :i_tableref;
if ( RECORD_IS_LOCKED )
{
/*
IF THE RETRY COUNT =3D SQL_MAX_RETRIES THEN RETURN
FAILURE
*/
fprintf(Gfp_err,"update Record Lock Condition - %d\\
",
i_ctr );
usleep( SQL_SLEEP );
if ( i_ctr =3D=3D SQL_MAX_RETRIES )
{
pst_comm->inqControl =3D -1;
fprintf(Gfp_err,"Record Lock Condition - Update\\
"
);
sqlErr(Gfp_err,Gs_prog_name,"obtainRef");
sqlRollbackWork();
return FAILURE;
}
}
else if ( sqlca.sqlcode !=3D SQL_OK )
{
pst_comm->inqControl =3D -1;
fprintf(Gfp_err,"OBTAINREF: update: sql error\\
");
sqlErr(Gfp_err,Gs_prog_name,"obtainRef");
sqlRollbackWork();
return FAILURE;
}
else
{
fprintf(Gfp_err,"OBTAINREF: reference number: %d\\
",
i_tableref);
pst_comm->inqControl =3D i_tableref;
break;
}
} /* RETRY LOOP */
if ( ( i_ret_code =3D sqlCommitWork() ) =3D=3D FAILURE )
{
return FAILURE;
}
return SUCCESS;
}
Thanks, Jeff
***********************************************************************=
********
Forum Note: Use "Reply" to post a response in the discussion forum.
=
or use a sequence generator.
-------------------------------------
Madison Pruet, STSM
IDS Replication Architect
=
"Art Kagel" =
<art.kagel@gmail. =
com> =
To
Sent by: ids@iiug.org =
ids-bounces@iiug. =
cc
org =
Subj=
ect
Re: Problem with duplicate value=
s
02/17/2009 10:15 in table on .... [14921] =
PM =
=
=
Please respond to =
ids@iiug.org =
=
=
I suspect that the two apps are each retrieving the value before the up=
date
finally occurs on the primary and is shipped to the secondary. The
solution:
Modify your application to depend on a SERIAL, SERIAL8, or BIGSERIAL co=
lumn
in the table into which these 'next values' are being written as keys. =
Then
all sessions on all servers depend on the value actually inserted on th=
e
primary.
Art
On Tue, Feb 17, 2009 at 10:55 PM, Jeff Filippi
<iiug@itdataconsulting.com>wrote:
> I am running Informix 11.50FC3 on HP-UX 11.23.
>
> We have a primary server and an updateable secondary server (SDS)
running.
>
> We encountered a problem where we have an esqlc application running w=
hich
> runs multiple processes at once.
>
> We have a table called "fileid" which consists of 6 rows which contai=
n a
> counter of the next value for certain processes.
>
> When a process calls the routine to increment the next value and retu=
rn
it
> to the process to use for the fields value, on the SDS server it
sometimes
> will return the same value for two different processes. It does not d=
o
this
> on the primary, the primary has unique values.
>
> We have found this on the production instance and have replicated it =
in a
> development instance.
>
> We opened a case with tech support tonight PMR 03011 122
>
> Has anyone ran into an issue like this??
>
> A quick review of what we are doing.
>
> Process calls a function, function does the following and returns the=
next
> value
>
> Begin work;
>
> select fid+1 from fileid where fid_key =3D 2 for update of fid;
>
> update fileid set fid =3D ? where current of ref_cur>
> commit work;
>
> Returns next value to program
>
> Here is the ESQLC code:
>
> #include "sysdep.h"
>
> static char id[] __attribute__((__unused__))=3D " ";
>
> EXEC SQL INCLUDE SQLCA;
>
> #include <time.h>
>
> #include <stdio.h>
>
> #include <string.h>
>
> #include <unistd.h>
>
> #include "codes.h"
>
> #include "sqlDefines.h"
>
> #include "sqlFcts.h"
>
> #include "doqual.h"
>
> #include "commarea.h"
>
> #include "obtainRef.h"
>
> int obtainRef( COMMAREA *pst_comm )
>
> {
>
> EXEC SQL BEGIN DECLARE SECTION;
>
> int i_tableref =3D 0;
>
> EXEC SQL END DECLARE SECTION;
>
> int i_ctr =3D 0;
>
> int i_ret_code =3D 0;
>
> static int i_sel_ref =3D NO;
>
> static int i_upd_ref =3D NO;
>
> /*
>
> PREPARE THE CURSOR STATEMENT
>
> */
>
> if( i_sel_ref =3D=3D NO )
>
> {
>
> exec sql prepare oref_sel_fid from
>
> 'select fid from fileid where fid_key =3D 2 for update of
> fid';
>
> if ( sqlca.sqlcode !=3D SQL_OK )
>
> {
>
> pst_comm->inqControl =3D -1;
>
> fprintf(Gfp_err,"OBTAINREF: Prepare oref_sel_fid
> Error\\
");
>
> sqlErr(Gfp_err,Gs_prog_name,"obtainRef");
>
> return FAILURE;
>
> }
>
> i_sel_ref =3D YES;
>
> }
>
> /*
>
> PREPARE THE UPDATE STATEMENT
>
> */
>
> if( i_upd_ref =3D=3D NO )
>
> {
>
> exec sql prepare oref_upd_fid from
>
> 'update fileid set fid =3D ? where current of ref_cur';
>
> if ( sqlca.sqlcode !=3D SQL_OK )
>
> {
>
> pst_comm->inqControl =3D -1;
>
> fprintf(Gfp_err,"OBTAINREF: Prepare oref_upd_fid
> Error\\
");
>
> sqlErr(Gfp_err,Gs_prog_name,"obtainRef");
>
> return FAILURE;
>
> }
>
> i_upd_ref =3D YES;
>
> }
>
> /*
>
> OPEN AND DECLARE THE CURSOR
>
> */
>
> if ( ( i_ret_code =3D sqlBeginWork() ) =3D=3D FAILURE )
>
> {
>
> return FAILURE;
>
> }
>
> exec sql declare ref_cur cursor for oref_sel_fid;
>
> exec sql open ref_cur;
>
> if( sqlca.sqlcode !=3D SQL_OK )
>
> {
>
> pst_comm->inqControl =3D -1;
>
> fprintf(Gfp_err,"OBTAINREF: Open unsuccessful\\
");
>
> sqlErr(Gfp_err,Gs_prog_name,"obtainRef");
>
> sqlRollbackWork();
>
> return FAILURE;
>
> }
>
> for( i_ctr =3D 0; i_ctr <=3D SQL_MAX_RETRIES; i_ctr++ )
>
> {
>
> exec sql fetch ref_cur into :i_tableref;
>
> if ( RECORD_IS_LOCKED )
>
> {
>
> /*
>
> IF THE RETRY COUNT =3D SQL_MAX_RETRIES THEN RETURN
> FAILURE
>
> */
>
> fprintf(Gfp_err,"fetch Record Lock Condition - %d\\
",
> i_ctr );
>
> usleep( SQL_SLEEP );
>
> if ( i_ctr =3D=3D SQL_MAX_RETRIES )
>
> {
>
> pst_comm->inqControl =3D -1;
>
> fprintf(Gfp_err,"Record Lock Condition - Fetch\\
" );
>
> sqlErr(Gfp_err,Gs_prog_name,"obtainRef");
>
> sqlRollbackWork();
>
> return FAILURE;
>
> }
>
> }
>
> else if ( sqlca.sqlcode !=3D SQL_OK )
>
> {
>
> /*
>
> AN ERROR OCCURRED SO REPORT THE ERROR AND RETURN
> FAILURE
>
> */
>
> pst_comm->inqControl =3D -1;
>
> fprintf(Gfp_err,"OBTAINREF: select: sql error\\
");
>
> sqlErr(Gfp_err,Gs_prog_name,"obtainRef");
>
> sqlRollbackWork();
>
> return FAILURE;
>
> }
>
> else
>
> {
>
> break;
>
> }
>
> }
>
> /*
>
> I DO NOT HAVE TO LOOP THIS (I THINK) BECAUSE
>
> THE FETCH PUT A LOCK ON IT
>
> */
>
> ++i_tableref;
>
> for( i_ctr =3D 0; i_ctr <=3D SQL_MAX_RETRIES; i_ctr++ )
>
> {
>
> exec sql execute oref_upd_fid using :i_tableref;
>
> if ( RECORD_IS_LOCKED )
>
> {
>
> /*
>
> IF THE RETRY COUNT =3D SQL_MAX_RETRIES THEN RETURN
> FAILURE
>
> */
>
> fprintf(Gfp_err,"update Record Lock Condition - %d\\
",
> i_ctr );
>
> usleep( SQL_SLEEP );
>
> if ( i_ctr =3D=3D SQL_MAX_RETRIES )
>
> {
>
> pst_comm->inqControl =3D -1;
>
> fprintf(Gfp_err,"Record Lock Condition - Update\\
"
> );
>
> sqlErr(Gfp_err,Gs_prog_name,"obtainRef");
>
> sqlRollbackWork();
>
> return FAILURE;
>
> }
>
> }
>
> else if ( sqlca.sqlcode !=3D SQL_OK )
>
> {
>
> pst_comm->inqControl =3D -1;
>
> fpr
Oh, another option would be to add VERCOLS to the table holding the ids and
make sure to use the vercols to verify that the record wasn't modified by
another session.
Art
On Tue, Feb 17, 2009 at 10:55 PM, Jeff Filippi
<iiug@itdataconsulting.com>wrote:
> I am running Informix 11.50FC3 on HP-UX 11.23.
>
> We have a primary server and an updateable secondary server (SDS) running.
>
> We encountered a problem where we have an esqlc application running which
> runs multiple processes at once.
>
> We have a table called "fileid" which consists of 6 rows which contain a
> counter of the next value for certain processes.
>
> When a process calls the routine to increment the next value and return it
> to the process to use for the fields value, on the SDS server it sometimes
> will return the same value for two different processes. It does not do this
> on the primary, the primary has unique values.
>
> We have found this on the production instance and have replicated it in a
> development instance.
>
> We opened a case with tech support tonight PMR 03011 122
>
> Has anyone ran into an issue like this??
>
> A quick review of what we are doing.
>
> Process calls a function, function does the following and returns the next
> value
>
> Begin work;
>
> select fid+1 from fileid where fid_key = 2 for update of fid;
>
> update fileid set fid = ? where current of ref_cur>
> commit work;
>
> Returns next value to program
>
> Here is the ESQLC code:
>
> #include "sysdep.h"
>
> static char id[] __attribute__((__unused__))= " ";
>
> EXEC SQL INCLUDE SQLCA;
>
> #include <time.h>
>
> #include <stdio.h>
>
> #include <string.h>
>
> #include <unistd.h>
>
> #include "codes.h"
>
> #include "sqlDefines.h"
>
> #include "sqlFcts.h"
>
> #include "doqual.h"
>
> #include "commarea.h"
>
> #include "obtainRef.h"
>
> int obtainRef( COMMAREA *pst_comm )
>
> {
>
> EXEC SQL BEGIN DECLARE SECTION;
>
> int i_tableref = 0;
>
> EXEC SQL END DECLARE SECTION;
>
> int i_ctr = 0;
>
> int i_ret_code = 0;
>
> static int i_sel_ref = NO;
>
> static int i_upd_ref = NO;
>
> /*
>
> PREPARE THE CURSOR STATEMENT
>
> */
>
> if( i_sel_ref == NO )
>
> {
>
> exec sql prepare oref_sel_fid from
>
> 'select fid from fileid where fid_key = 2 for update of
> fid';
>
> if ( sqlca.sqlcode != SQL_OK )
>
> {
>
> pst_comm->inqControl = -1;
>
> fprintf(Gfp_err,"OBTAINREF: Prepare oref_sel_fid
> Error\\
");
>
> sqlErr(Gfp_err,Gs_prog_name,"obtainRef");
>
> return FAILURE;
>
> }
>
> i_sel_ref = YES;
>
> }
>
> /*
>
> PREPARE THE UPDATE STATEMENT
>
> */
>
> if( i_upd_ref == NO )
>
> {
>
> exec sql prepare oref_upd_fid from
>
> 'update fileid set fid = ? where current of ref_cur';
>
> if ( sqlca.sqlcode != SQL_OK )
>
> {
>
> pst_comm->inqControl = -1;
>
> fprintf(Gfp_err,"OBTAINREF: Prepare oref_upd_fid
> Error\\
");
>
> sqlErr(Gfp_err,Gs_prog_name,"obtainRef");
>
> return FAILURE;
>
> }
>
> i_upd_ref = YES;
>
> }
>
> /*
>
> OPEN AND DECLARE THE CURSOR
>
> */
>
> if ( ( i_ret_code = sqlBeginWork() ) == FAILURE )
>
> {
>
> return FAILURE;
>
> }
>
> exec sql declare ref_cur cursor for oref_sel_fid;
>
> exec sql open ref_cur;
>
> if( sqlca.sqlcode != SQL_OK )
>
> {
>
> pst_comm->inqControl = -1;
>
> fprintf(Gfp_err,"OBTAINREF: Open unsuccessful\\
");
>
> sqlErr(Gfp_err,Gs_prog_name,"obtainRef");
>
> sqlRollbackWork();
>
> return FAILURE;
>
> }
>
> for( i_ctr = 0; i_ctr <= SQL_MAX_RETRIES; i_ctr++ )
>
> {
>
> exec sql fetch ref_cur into :i_tableref;
>
> if ( RECORD_IS_LOCKED )
>
> {
>
> /*
>
> IF THE RETRY COUNT = SQL_MAX_RETRIES THEN RETURN
> FAILURE
>
> */
>
> fprintf(Gfp_err,"fetch Record Lock Condition - %d\\
",
> i_ctr );
>
> usleep( SQL_SLEEP );
>
> if ( i_ctr == SQL_MAX_RETRIES )
>
> {
>
> pst_comm->inqControl = -1;
>
> fprintf(Gfp_err,"Record Lock Condition - Fetch\\
" );
>
> sqlErr(Gfp_err,Gs_prog_name,"obtainRef");
>
> sqlRollbackWork();
>
> return FAILURE;
>
> }
>
> }
>
> else if ( sqlca.sqlcode != SQL_OK )
>
> {
>
> /*
>
> AN ERROR OCCURRED SO REPORT THE ERROR AND RETURN
> FAILURE
>
> */
>
> pst_comm->inqControl = -1;
>
> fprintf(Gfp_err,"OBTAINREF: select: sql error\\
");
>
> sqlErr(Gfp_err,Gs_prog_name,"obtainRef");
>
> sqlRollbackWork();
>
> return FAILURE;
>
> }
>
> else
>
> {
>
> break;
>
> }
>
> }
>
> /*
>
> I DO NOT HAVE TO LOOP THIS (I THINK) BECAUSE
>
> THE FETCH PUT A LOCK ON IT
>
> */
>
> ++i_tableref;
>
> for( i_ctr = 0; i_ctr <= SQL_MAX_RETRIES; i_ctr++ )
>
> {
>
> exec sql execute oref_upd_fid using :i_tableref;
>
> if ( RECORD_IS_LOCKED )
>
> {
>
> /*
>
> IF THE RETRY COUNT = SQL_MAX_RETRIES THEN RETURN
> FAILURE
>
> */
>
> fprintf(Gfp_err,"update Record Lock Condition - %d\\
",
> i_ctr );
>
> usleep( SQL_SLEEP );
>
> if ( i_ctr == SQL_MAX_RETRIES )
>
> {
>
> pst_comm->inqControl = -1;
>
> fprintf(Gfp_err,"Record Lock Condition - Update\\
"
> );
>
> sqlErr(Gfp_err,Gs_prog_name,"obtainRef");
>
> sqlRollbackWork();
>
> return FAILURE;
>
> }
>
> }
>
> else if ( sqlca.sqlcode != SQL_OK )
>
> {
>
> pst_comm->inqControl = -1;
>
> fprintf(Gfp_err,"OBTAINREF: update: sql error\\
");
>
> sqlErr(Gfp_err,Gs_prog_name,"obtainRef");
>
> sqlRollbackWork();
>
> return FAILURE;
>
> }
>
> else
>
> {
>
> fprintf(Gfp_err,"OBTAINREF: reference number: %d\\
",
> i_tableref);
>
> pst_comm->inqControl = i_tableref;
>
> break;
>
> }
>
> } /* RETRY LOOP */
>
> if ( ( i_ret_code = sqlCommitWork() ) == FAILURE )
>
> {
>
> return FAILURE;
>
> }
>
> return SUCCESS;
>
> }
>
> Thanks, Jeff
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--
Art S. Kagel
Oninit (www.oninit.com)
IIUG Board of Directors (art@iiug.org)
Disclaimer: Please keep in mind that my own opinions are my