Isolation modes in mach11 cluster

All, We are in the process of converting from a single database server to a cluster, and we are having some confusion about transactions and isolation levels. Our cluster consists of a primary and secondary HDR that are in the same location (1gbps) and an RSS that is across a WAN link (4.5mpbs) We designed a test app to run against the cluster (not yet in production, but with a copy of the production data) to verify it is working as we expect. The program basically does this: 1) Choose a server randomly (either primary, secondary HDR, or RSS) 2) Begin transaction 3) Insert/update/delete on test table: infx_cluster_test 4) (on case of insert, do a select to get back a serial number) 5) Commit transaction Foreach server: 6) Select from infx_cluster_test and verify the data is what we just changed it too. If it doesn't match, keep trying until it does. Here, we record how many attempts were made, and the time-lapse between the initial insert/update/delete and the successful verification select. Now then, we set up this program to run from a single computer, and it works pretty well. However, when we run it on multiple computers, we get into a scenario where each clients is connected to a different server and each of them has begun a transaction, performed an insert, and is trying to perform a select (step 4). What we get here is the strange part. On one connection, we'll get error code 245 (could not position within a file via an index), and on the other we'll get error code 246 (could not do an indexed read to get the next row). It was suggested to us to change our USELASTCOMMITTED onconfig value from NONE to ALL, but we're unsure of the implications of doing so, or what side-effects it might have (the current default value within the onconfig.std is set to NONE, so that's how we left it). Is there a standard setup that is commonly in use for mach11 clusters and isolation modes? Thanks, -Justin

Hmmm.... several important points: 1-I have some doubts (sorry if I'm mistaken) that you don't see the same behavior on just one (primary) server. What is your table lock level, and how is the query accessing the table? 2- If you want to try USELASTCOMMITTED you can set it to "Committed Read". It shoudl not cause your application any harm. But your lock level must be "row" 3- Depending on your version look for CLUSTER_TXN_SCOPE ( http://pic.dhe.ibm.com/infocenter/idshelp/v117/topic/com.ibm.adref.doc/ids_adr_1 165.htm). It allows you to define when your application recieves the confirmation of the COMMIT (can be when it's completely replicated across the cluster) Regards On Thu, Aug 29, 2013 at 11:15 PM, Justin Killen < jkillen@allamericanasphalt.com> wrote: > All, > > We are in the process of converting from a single database server to a > cluster, and we are having some confusion about transactions and isolation > levels. Our cluster consists of a primary and secondary HDR that are in the > same location (1gbps) and an RSS that is across a WAN link (4.5mpbs) We > designed a test app to run against the cluster (not yet in production, but > with a copy of the production data) to verify it is working as we expect. > The > program basically does this: > > 1) Choose a server randomly (either primary, secondary HDR, or RSS) > 2) Begin transaction > 3) Insert/update/delete on test table: infx_cluster_test > 4) (on case of insert, do a select to get back a serial number) > 5) Commit transaction > Foreach server: > 6) Select from infx_cluster_test and verify the data is what we just > changed > it too. If it doesn't match, keep trying until it does. Here, we record how > many attempts were made, and the time-lapse between the initial > insert/update/delete and the successful verification select. > > Now then, we set up this program to run from a single computer, and it > works > pretty well. However, when we run it on multiple computers, we get into a > scenario where each clients is connected to a different server and each of > them has begun a transaction, performed an insert, and is trying to > perform a > select (step 4). What we get here is the strange part. On one connection, > we'll get error code 245 (could not position within a file via an index), > and > on the other we'll get error code 246 (could not do an indexed read to get > the > next row). It was suggested to us to change our USELASTCOMMITTED onconfig > value from NONE to ALL, but we're unsure of the implications of doing so, > or > what side-effects it might have (the current default value within the > onconfig.std is set to NONE, so that's how we left it). > > Is there a standard setup that is commonly in use for mach11 clusters and > isolation modes? > > Thanks, > -Justin > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a11c22e58a1772d04e51dc04e

1) table lock level is row locks, SQL for each test iteration is as follows: Begin Transaction INSERT INTO infx_cluster_test (host_name, id, data) VALUES ('JUSTINK-PC', 756, 'fp''L+h2$}vxV6NPjS;GKZZp/upua"V') SELECT serial_num FROM infx_cluster_test WHERE serial_num = (select max(serial_num) from infx_cluster_test)Commit Transaction This SQL is part of a base level data access layer that most every insert uses. It works flawlessly on our current single-system setup with USELASTCOMMITTED=NONE and SET LOCK MODE TO WAIT 2. 2) We tried setting USELASTCOMMITTED to ALL, but the result is that some of the selects are not coming back with correct data (client A does the insert 3, then client B does an insert and a select max(serial_num), then client A does a select max(serial_num), resulting in client A getting client B's data). I'm not sure exactly what the difference is between ALL and COMMITTED READ, and inline comments in onconfig seems circular - if anybody could shed some light on the differences between the 4 options, that would be helpful. 3) We like the idea of setting the CLUSTER_TXN_SCOPE option to cluster, but how does this effect things if the WAN link goes down to the RSS? -Justin -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Fernando Nunes Sent: Thursday, August 29, 2013 3:38 PM To: ids@iiug.org Subject: Re: Isolation modes in mach11 cluster [31307] Hmmm.... several important points: 1-I have some doubts (sorry if I'm mistaken) that you don't see the same behavior on just one (primary) server. What is your table lock level, and how is the query accessing the table? 2- If you want to try USELASTCOMMITTED you can set it to "Committed Read". It shoudl not cause your application any harm. But your lock level must be "row" 3- Depending on your version look for CLUSTER_TXN_SCOPE ( http://pic.dhe.ibm.com/infocenter/idshelp/v117/topic/com.ibm.adref.doc/ids_adr_1 165.htm). It allows you to define when your application recieves the confirmation of the COMMIT (can be when it's completely replicated across the cluster) Regards On Thu, Aug 29, 2013 at 11:15 PM, Justin Killen < jkillen@allamericanasphalt.com> wrote: > All, > > We are in the process of converting from a single database server to a > cluster, and we are having some confusion about transactions and isolation > levels. Our cluster consists of a primary and secondary HDR that are in the > same location (1gbps) and an RSS that is across a WAN link (4.5mpbs) We > designed a test app to run against the cluster (not yet in production, but > with a copy of the production data) to verify it is working as we expect. > The > program basically does this: > > 1) Choose a server randomly (either primary, secondary HDR, or RSS) > 2) Begin transaction > 3) Insert/update/delete on test table: infx_cluster_test > 4) (on case of insert, do a select to get back a serial number) > 5) Commit transaction > Foreach server: > 6) Select from infx_cluster_test and verify the data is what we just > changed > it too. If it doesn't match, keep trying until it does. Here, we record how > many attempts were made, and the time-lapse between the initial > insert/update/delete and the successful verification select. > > Now then, we set up this program to run from a single computer, and it > works > pretty well. However, when we run it on multiple computers, we get into a > scenario where each clients is connected to a different server and each of > them has begun a transaction, performed an insert, and is trying to > perform a > select (step 4). What we get here is the strange part. On one connection, > we'll get error code 245 (could not position within a file via an index), > and > on the other we'll get error code 246 (could not do an indexed read to get > the > next row). It was suggested to us to change our USELASTCOMMITTED onconfig > value from NONE to ALL, but we're unsure of the implications of doing so, > or > what side-effects it might have (the current default value within the > onconfig.std is set to NONE, so that's how we left it). > > Is there a standard setup that is commonly in use for mach11 clusters and > isolation modes? > > Thanks, > -Justin > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --001a11c22e58a1772d04e51dc04e ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

First, are you aware that a SELECT from the table inserted to is the WRONG way to get the serial value just inserted? It is entirely possible to get the serial value inserted by another session altogether if you do that. Instead you should be looking at the sqlca.sqlerrd[1] field or calling the DBINFO('sqlca.sqlerrd1') function for SERIAL type, or the DBINFO('bigserial' ) or DBINFO( 'serial8' ) function for BIGSERIAL and SERIAL8 type values respectively, immediately after the insert. The LAST_COMMITTED isolation option should not change the semantics of your applications, it will just cause apps to return the currently committed version of any rows that have been modified but not yet committed instead of blocking on the lock on those rows (if lock mode is set to wait) or returning a lock error. Finally, your applications, even this test app, should be setting SET LOCK MODE TO WAIT <nsecs> so that transient locks do not cause an error but instead just a brief pause. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Aug 29, 2013 at 5:15 PM, Justin Killen < jkillen@allamericanasphalt.com> wrote: > All, > > We are in the process of converting from a single database server to a > cluster, and we are having some confusion about transactions and isolation > levels. Our cluster consists of a primary and secondary HDR that are in the > same location (1gbps) and an RSS that is across a WAN link (4.5mpbs) We > designed a test app to run against the cluster (not yet in production, but > with a copy of the production data) to verify it is working as we expect. > The > program basically does this: > > 1) Choose a server randomly (either primary, secondary HDR, or RSS) > 2) Begin transaction > 3) Insert/update/delete on test table: infx_cluster_test > 4) (on case of insert, do a select to get back a serial number) > 5) Commit transaction > Foreach server: > 6) Select from infx_cluster_test and verify the data is what we just > changed > it too. If it doesn't match, keep trying until it does. Here, we record how > many attempts were made, and the time-lapse between the initial > insert/update/delete and the successful verification select. > > Now then, we set up this program to run from a single computer, and it > works > pretty well. However, when we run it on multiple computers, we get into a > scenario where each clients is connected to a different server and each of > them has begun a transaction, performed an insert, and is trying to > perform a > select (step 4). What we get here is the strange part. On one connection, > we'll get error code 245 (could not position within a file via an index), > and > on the other we'll get error code 246 (could not do an indexed read to get > the > next row). It was suggested to us to change our USELASTCOMMITTED onconfig > value from NONE to ALL, but we're unsure of the implications of doing so, > or > what side-effects it might have (the current default value within the > onconfig.std is set to NONE, so that's how we left it). > > Is there a standard setup that is commonly in use for mach11 clusters and > isolation modes? > > Thanks, > -Justin > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1133eaca1ec89904e520a177

Yeah, this: SELECT serial_num FROM infx_cluster_test WHERE serial_num = (select max(serial_num) from infx_cluster_test)Commit Transaction Is WRONG WRONG WRONG and very dangerous! You are VERY likely to get a serial value inserted by another user session, especially if you turn on the READ_COMMITTED isolation option! Instead do this: SELECT DBINFO( 'sqlca.sqlerrd1' ) FROM sysmaster:sysdual; Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Aug 29, 2013 at 8:00 PM, Justin Killen < jkillen@allamericanasphalt.com> wrote: > 1) table lock level is row locks, SQL for each test iteration is as > follows: > > Begin Transaction > > INSERT INTO infx_cluster_test (host_name, id, data) VALUES ('JUSTINK-PC', > 756, > 'fp''L+h2$}vxV6NPjS;GKZZp/upua"V')> > SELECT serial_num FROM infx_cluster_test WHERE serial_num = (select > max(serial_num) from infx_cluster_test)> Commit Transaction > > This SQL is part of a base level data access layer that most every insert > uses. It works flawlessly on our current single-system setup with > USELASTCOMMITTED=NONE and SET LOCK MODE TO WAIT 2. > > 2) We tried setting USELASTCOMMITTED to ALL, but the result is that some of > the selects are not coming back with correct data (client A does the > insert 3, > then client B does an insert and a select max(serial_num), then client A > does > a select max(serial_num), resulting in client A getting client B's data). > I'm not sure exactly what the difference is between ALL and COMMITTED READ, > and inline comments in onconfig seems circular - if anybody could shed some > light on the differences between the 4 options, that would be helpful. > > 3) We like the idea of setting the CLUSTER_TXN_SCOPE option to cluster, but > how does this effect things if the WAN link goes down to the RSS? > > -Justin > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Fernando > Nunes > Sent: Thursday, August 29, 2013 3:38 PM > To: ids@iiug.org > Subject: Re: Isolation modes in mach11 cluster [31307] > > Hmmm.... several important points: > > 1-I have some doubts (sorry if I'm mistaken) that you don't see the same > behavior on just one (primary) server. What is your table lock level, and > how is the query accessing the table? > 2- If you want to try USELASTCOMMITTED you can set it to "Committed Read". > It shoudl not cause your application any harm. But your lock level must be > "row" > 3- Depending on your version look for CLUSTER_TXN_SCOPE ( > > > > http://pic.dhe.ibm.com/infocenter/idshelp/v117/topic/com.ibm.adref.doc/ids_adr_1 165.htm > ). > It allows you to define when your application recieves the > confirmation > of the COMMIT (can be when it's completely replicated across the cluster) > > Regards > > On Thu, Aug 29, 2013 at 11:15 PM, Justin Killen < > jkillen@allamericanasphalt.com> wrote: > > > All, > > > > We are in the process of converting from a single database server to a > > cluster, and we are having some confusion about transactions and > isolation > > levels. Our cluster consists of a primary and secondary HDR that are in > the > > same location (1gbps) and an RSS that is across a WAN link (4.5mpbs) We > > designed a test app to run against the cluster (not yet in production, > but > > with a copy of the production data) to verify it is working as we expect. > > The > > program basically does this: > > > > 1) Choose a server randomly (either primary, secondary HDR, or RSS) > > 2) Begin transaction > > 3) Insert/update/delete on test table: infx_cluster_test > > 4) (on case of insert, do a select to get back a serial number) > > 5) Commit transaction > > Foreach server: > > 6) Select from infx_cluster_test and verify the data is what we just > > changed > > it too. If it doesn't match, keep trying until it does. Here, we record > how > > many attempts were made, and the time-lapse between the initial > > insert/update/delete and the successful verification select. > > > > Now then, we set up this program to run from a single computer, and it > > works > > pretty well. However, when we run it on multiple computers, we get into a > > scenario where each clients is connected to a different server and each > of > > them has begun a transaction, performed an insert, and is trying to > > perform a > > select (step 4). What we get here is the strange part. On one connection, > > we'll get error code 245 (could not position within a file via an index), > > and > > on the other we'll get error code 246 (could not do an indexed read to > get > > the > > next row). It was suggested to us to change our USELASTCOMMITTED onconfig > > value from NONE to ALL, but we're unsure of the implications of doing so, > > or > > what side-effects it might have (the current default value within the > > onconfig.std is set to NONE, so that's how we left it). > > > > Is there a standard setup that is commonly in use for mach11 clusters and > > isolation modes? > > > > Thanks, > > -Justin > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --001a11c22e58a1772d04e51dc04e > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1133aa86353ee404e520ad83

Wow... Art already wrote about this... but even so...: On Fri, Aug 30, 2013 at 2:00 AM, Justin Killen < jkillen@allamericanasphalt.com> wrote: > 1) table lock level is row locks, SQL for each test iteration is as > follows: > > Begin Transaction > > INSERT INTO infx_cluster_test (host_name, id, data) VALUES ('JUSTINK-PC', > 756, > 'fp''L+h2$}vxV6NPjS;GKZZp/upua"V')> > SELECT serial_num FROM infx_cluster_test WHERE serial_num = (select > max(serial_num) from infx_cluster_test)> Commit Transaction > Very wrong. Why do you think this will give you "your" SERIAL? In fact it probably does, but only as a side effect.., And it can be interesting to check why the behavior changes in a cluster. Let's see... I'll try do "draw" a sequence of events... S1= Session 1 S2 = Session 2 LOCK MODE WAIT 2 (as stated by you) S1: INSERT INTO infx_cluster_test -- Establishes a lock (L1) S2: INSERT INTO infx_cluster_test -- Establishes a lock (L2) S1: SELECT max(...) FROM infx_cluster_test -- Blocks on L2 S2: SELECT max(...) FROM infx_cluster_test -- Two options: Option 1: S2 is using an INDEX and is able to read it's own INSERT which is the MAX and: S2 COMMITS S1 unblocks and COMMITS Option 2: S2 is using a FULL scan and can't proceed: BLOCKs on L1 Engine detects a deadlock So, if I'm not missing anything, one of this things is hapening. Your code is wrong (Art explained you how to do it). > > This SQL is part of a base level data access layer that most every insert > uses. It works flawlessly on our current single-system setup with > USELASTCOMMITTED=NONE and SET LOCK MODE TO WAIT 2. > If the above is correct, works "flawlessly" by sort of luck... I assume you're getting in option 1. As to why it doesn't work in cluster... Maybe the lock wait is not enough. Or maybe on the secondary it's not using an index? I would have to try to simulate this to be more assertive. Of course I may be missing something.... But you're assming the "max(serial_num)" is you own session. In a multi-session, multi-cluster this only happens if you're lucky. As the number of sessions increase and the cluster delays increase, your chances are that you'll have problems. > > 2) We tried setting USELASTCOMMITTED to ALL, but the result is that some of > the selects are not coming back with correct data (client A does the > insert 3, > then client B does an insert and a select max(serial_num), then client A > does > a select max(serial_num), resulting in client A getting client B's data). > Precisely. Because the order of the COMMITTED data may not be exactly the same as the INSERT. You're dealing with a completeley parallel engine... Secondary nodes receive data sequently, but there can be several threads applying that data. So at the time you do the SELECT MAX() you do exactly what it says: MAX() at the time. Not MAX() at the INSERT time. > I'm not sure exactly what the difference is between ALL and COMMITTED READ, > and inline comments in onconfig seems circular - if anybody could shed some > light on the differences between the 4 options, that would be helpful. > The manual explains it. NONE is "do nothing". DIRTY READ is "promote dirty readers to LAST COMMITTED". COMMITTED READ is "promote COMMITTED readers to LAST COMMIT". ALL is both. > > 3) We like the idea of setting the CLUSTER_TXN_SCOPE option to cluster, but > how does this effect things if the WAN link goes down to the RSS? > Good point, but I'd have to check. I'm assuming it means "all connected secondary servers". Regards > > -Justin > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Fernando > Nunes > Sent: Thursday, August 29, 2013 3:38 PM > To: ids@iiug.org > Subject: Re: Isolation modes in mach11 cluster [31307] > > Hmmm.... several important points: > > 1-I have some doubts (sorry if I'm mistaken) that you don't see the same > behavior on just one (primary) server. What is your table lock level, and > how is the query accessing the table? > 2- If you want to try USELASTCOMMITTED you can set it to "Committed Read". > It shoudl not cause your application any harm. But your lock level must be > "row" > 3- Depending on your version look for CLUSTER_TXN_SCOPE ( > > > > http://pic.dhe.ibm.com/infocenter/idshelp/v117/topic/com.ibm.adref.doc/ids_adr_1 165.htm > ). > It allows you to define when your application recieves the > confirmation > of the COMMIT (can be when it's completely replicated across the cluster) > > Regards > > On Thu, Aug 29, 2013 at 11:15 PM, Justin Killen < > jkillen@allamericanasphalt.com> wrote: > > > All, > > > > We are in the process of converting from a single database server to a > > cluster, and we are having some confusion about transactions and > isolation > > levels. Our cluster consists of a primary and secondary HDR that are in > the > > same location (1gbps) and an RSS that is across a WAN link (4.5mpbs) We > > designed a test app to run against the cluster (not yet in production, > but > > with a copy of the production data) to verify it is working as we expect. > > The > > program basically does this: > > > > 1) Choose a server randomly (either primary, secondary HDR, or RSS) > > 2) Begin transaction > > 3) Insert/update/delete on test table: infx_cluster_test > > 4) (on case of insert, do a select to get back a serial number) > > 5) Commit transaction > > Foreach server: > > 6) Select from infx_cluster_test and verify the data is what we just > > changed > > it too. If it doesn't match, keep trying until it does. Here, we record > how > > many attempts were made, and the time-lapse between the initial > > insert/update/delete and the successful verification select. > > > > Now then, we set up this program to run from a single computer, and it > > works > > pretty well. However, when we run it on multiple computers, we get into a > > scenario where each clients is connected to a different server and each > of > > them has begun a transaction, performed an insert, and is trying to > > perform a > > select (step 4). What we get here is the strange part. On one connection, > > we'll get error code 245 (could not position within a file via an index), > > and > > on the other we'll get error code 246 (could not do an indexed read to > get > > the > > next row). It was suggested to us to change our USELASTCOMMITTED onconfig > > value from NONE to ALL, but we're unsure of the implications of doing so, > > or > > what side-effects it might have (the current default value within the > > onconfig.std is set to NONE, so that's how we left it). > > > > Is there a standard setup that is commonly in use for mach11 clusters and > > isolation modes? > > > > Thanks, > > -Justin > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use

When we do this: SELECT DBINFO( 'sqlca.sqlerrd1' ) FROM sysmaster:sysdual; We always get 0. This is on windows ODBC driver, 3.50.TC8 -Justin -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Thursday, August 29, 2013 7:07 PM To: ids@iiug.org Subject: Re: Isolation modes in mach11 cluster [31310] Yeah, this: SELECT serial_num FROM infx_cluster_test WHERE serial_num = (select max(serial_num) from infx_cluster_test)Commit Transaction Is WRONG WRONG WRONG and very dangerous! You are VERY likely to get a serial value inserted by another user session, especially if you turn on the READ_COMMITTED isolation option! Instead do this: SELECT DBINFO( 'sqlca.sqlerrd1' ) FROM sysmaster:sysdual; Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Thu, Aug 29, 2013 at 8:00 PM, Justin Killen < jkillen@allamericanasphalt.com> wrote: > 1) table lock level is row locks, SQL for each test iteration is as > follows: > > Begin Transaction > > INSERT INTO infx_cluster_test (host_name, id, data) VALUES ('JUSTINK-PC', > 756, > 'fp''L+h2$}vxV6NPjS;GKZZp/upua"V')> > SELECT serial_num FROM infx_cluster_test WHERE serial_num = (select > max(serial_num) from infx_cluster_test)> Commit Transaction > > This SQL is part of a base level data access layer that most every insert > uses. It works flawlessly on our current single-system setup with > USELASTCOMMITTED=NONE and SET LOCK MODE TO WAIT 2. > > 2) We tried setting USELASTCOMMITTED to ALL, but the result is that some of > the selects are not coming back with correct data (client A does the > insert 3, > then client B does an insert and a select max(serial_num), then client A > does > a select max(serial_num), resulting in client A getting client B's data). > I'm not sure exactly what the difference is between ALL and COMMITTED READ, > and inline comments in onconfig seems circular - if anybody could shed some > light on the differences between the 4 options, that would be helpful. > > 3) We like the idea of setting the CLUSTER_TXN_SCOPE option to cluster, but > how does this effect things if the WAN link goes down to the RSS? > > -Justin > > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > Fernando > Nunes > Sent: Thursday, August 29, 2013 3:38 PM > To: ids@iiug.org > Subject: Re: Isolation modes in mach11 cluster [31307] > > Hmmm.... several important points: > > 1-I have some doubts (sorry if I'm mistaken) that you don't see the same > behavior on just one (primary) server. What is your table lock level, and > how is the query accessing the table? > 2- If you want to try USELASTCOMMITTED you can set it to "Committed Read". > It shoudl not cause your application any harm. But your lock level must be > "row" > 3- Depending on your version look for CLUSTER_TXN_SCOPE ( > > > > http://pic.dhe.ibm.com/infocenter/idshelp/v117/topic/com.ibm.adref.doc/ids_adr_1 165.htm > ). > It allows you to define when your application recieves the > confirmation > of the COMMIT (can be when it's completely replicated across the cluster) > > Regards > > On Thu, Aug 29, 2013 at 11:15 PM, Justin Killen < > jkillen@allamericanasphalt.com> wrote: > > > All, > > > > We are in the process of converting from a single database server to a > > cluster, and we are having some confusion about transactions and > isolation > > levels. Our cluster consists of a primary and secondary HDR that are in > the > > same location (1gbps) and an RSS that is across a WAN link (4.5mpbs) We > > designed a test app to run against the cluster (not yet in production, > but > > with a copy of the production data) to verify it is working as we expect. > > The > > program basically does this: > > > > 1) Choose a server randomly (either primary, secondary HDR, or RSS) > > 2) Begin transaction > > 3) Insert/update/delete on test table: infx_cluster_test > > 4) (on case of insert, do a select to get back a serial number) > > 5) Commit transaction > > Foreach server: > > 6) Select from infx_cluster_test and verify the data is what we just > > changed > > it too. If it doesn't match, keep trying until it does. Here, we record > how > > many attempts were made, and the time-lapse between the initial > > insert/update/delete and the successful verification select. > > > > Now then, we set up this program to run from a single computer, and it > > works > > pretty well. However, when we run it on multiple computers, we get into a > > scenario where each clients is connected to a different server and each > of > > them has begun a transaction, performed an insert, and is trying to > > perform a > > select (step 4). What we get here is the strange part. On one connection, > > we'll get error code 245 (could not position within a file via an index), > > and > > on the other we'll get error code 246 (could not do an indexed read to > get > > the > > next row). It was suggested to us to change our USELASTCOMMITTED onconfig > > value from NONE to ALL, but we're unsure of the implications of doing so, > > or > > what side-effects it might have (the current default value within the > > onconfig.std is set to NONE, so that's how we left it). > > > > Is there a standard setup that is commonly in use for mach11 clusters and > > isolation modes? > > > > Thanks, > > -Justin > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > -- > Fernando Nunes > Portugal > > http://informix-technology.blogspot.com > My email works... but I don't check it frequently... > > --001a11c22e58a1772d04e51dc04e > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a1133aa86353ee404e520ad83 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

After you insert into a SERIAL column? On the primary? On Aug 30, 2013 5:46 PM, "Justin Killen" <jkillen@allamericanasphalt.com> wrote: > When we do this: > > SELECT DBINFO( 'sqlca.sqlerrd1' ) FROM sysmaster:sysdual; > > We always get 0. This is on windows ODBC driver, 3.50.TC8 > > -Justin > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art > Kagel > Sent: Thursday, August 29, 2013 7:07 PM > To: ids@iiug.org > Subject: Re: Isolation modes in mach11 cluster [31310] > > Yeah, this: > > SELECT serial_num FROM infx_cluster_test WHERE serial_num = (select > max(serial_num) from infx_cluster_test)> Commit Transaction > > Is WRONG WRONG WRONG and very dangerous! You are VERY likely to get a > serial value inserted by another user session, especially if you turn on > the READ_COMMITTED isolation option! Instead do this: > > SELECT DBINFO( 'sqlca.sqlerrd1' ) FROM sysmaster:sysdual; > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, > implicitly, or by inference. Neither do those opinions reflect those of > other individuals affiliated with any entity with which I am affiliated nor > those of the entities themselves. > > On Thu, Aug 29, 2013 at 8:00 PM, Justin Killen < > jkillen@allamericanasphalt.com> wrote: > > > 1) table lock level is row locks, SQL for each test iteration is as > > follows: > > > > Begin Transaction > > > > INSERT INTO infx_cluster_test (host_name, id, data) VALUES ('JUSTINK-PC', > > 756, > > 'fp''L+h2$}vxV6NPjS;GKZZp/upua"V')> > > > SELECT serial_num FROM infx_cluster_test WHERE serial_num = (select > > max(serial_num) from infx_cluster_test)> > Commit Transaction > > > > This SQL is part of a base level data access layer that most every insert > > uses. It works flawlessly on our current single-system setup with > > USELASTCOMMITTED=NONE and SET LOCK MODE TO WAIT 2. > > > > 2) We tried setting USELASTCOMMITTED to ALL, but the result is that some > of > > the selects are not coming back with correct data (client A does the > > insert 3, > > then client B does an insert and a select max(serial_num), then client A > > does > > a select max(serial_num), resulting in client A getting client B's data). > > I'm not sure exactly what the difference is between ALL and COMMITTED > READ, > > and inline comments in onconfig seems circular - if anybody could shed > some > > light on the differences between the 4 options, that would be helpful. > > > > 3) We like the idea of setting the CLUSTER_TXN_SCOPE option to cluster, > but > > how does this effect things if the WAN link goes down to the RSS? > > > > -Justin > > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > Fernando > > Nunes > > Sent: Thursday, August 29, 2013 3:38 PM > > To: ids@iiug.org > > Subject: Re: Isolation modes in mach11 cluster [31307] > > > > Hmmm.... several important points: > > > > 1-I have some doubts (sorry if I'm mistaken) that you don't see the same > > behavior on just one (primary) server. What is your table lock level, and > > how is the query accessing the table? > > 2- If you want to try USELASTCOMMITTED you can set it to "Committed > Read". > > It shoudl not cause your application any harm. But your lock level must > be > > "row" > > 3- Depending on your version look for CLUSTER_TXN_SCOPE ( > > > > > > > > > > > http://pic.dhe.ibm.com/infocenter/idshelp/v117/topic/com.ibm.adref.doc/ids_adr_1 165.htm > > ). > > It allows you to define when your application recieves the > > confirmation > > of the COMMIT (can be when it's completely replicated across the cluster) > > > > Regards > > > > On Thu, Aug 29, 2013 at 11:15 PM, Justin Killen < > > jkillen@allamericanasphalt.com> wrote: > > > > > All, > > > > > > We are in the process of converting from a single database server to a > > > cluster, and we are having some confusion about transactions and > > isolation > > > levels. Our cluster consists of a primary and secondary HDR that are in > > the > > > same location (1gbps) and an RSS that is across a WAN link (4.5mpbs) We > > > designed a test app to run against the cluster (not yet in production, > > but > > > with a copy of the production data) to verify it is working as we > expect. > > > The > > > program basically does this: > > > > > > 1) Choose a server randomly (either primary, secondary HDR, or RSS) > > > 2) Begin transaction > > > 3) Insert/update/delete on test table: infx_cluster_test > > > 4) (on case of insert, do a select to get back a serial number) > > > 5) Commit transaction > > > Foreach server: > > > 6) Select from infx_cluster_test and verify the data is what we just > > > changed > > > it too. If it doesn't match, keep trying until it does. Here, we record > > how > > > many attempts were made, and the time-lapse between the initial > > > insert/update/delete and the successful verification select. > > > > > > Now then, we set up this program to run from a single computer, and it > > > works > > > pretty well. However, when we run it on multiple computers, we get > into a > > > scenario where each clients is connected to a different server and each > > of > > > them has begun a transaction, performed an insert, and is trying to > > > perform a > > > select (step 4). What we get here is the strange part. On one > connection, > > > we'll get error code 245 (could not position within a file via an > index), > > > and > > > on the other we'll get error code 246 (could not do an indexed read to > > get > > > the > > > next row). It was suggested to us to change our USELASTCOMMITTED > onconfig > > > value from NONE to ALL, but we're unsure of the implications of doing > so, > > > or > > > what side-effects it might have (the current default value within the > > > onconfig.std is set to NONE, so that's how we left it). > > > > > > Is there a standard setup that is commonly in use for mach11 clusters > and > > > isolation modes? > > > > > > Thanks, > > > -Justin > > > > > > > > > > > > > > > > > > > > > ******************************************************************************* > > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > > > > > -- > > Fernando Nunes > > Portugal > > > > http://informix-technology.blogspot.com > > My email works... but I don't check it frequently... > > > > --001a11c22e58a1772d04e51dc04e > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to p

Original post: After you insert into a SERIAL column? On the primary? On Aug 30, 2013 5:46 PM, "Justin Killen" <jkillen@allamericanasphalt.com> wrote: > When we do this: > > SELECT DBINFO( 'sqlca.sqlerrd1' ) FROM sysmaster:sysdual; > > We always get 0. This is on windows ODBC driver, 3.50.TC8 > > -Justin Response: I think maybe since you say you are getting a 0 on your dbinfo, that your serial column isn't a serial, but perhaps a serial8 or big serial? If so you'd need to do select dbinfo('bigserial') from sysmaster:sysdual instead Jacques Renaut IBM Informix Advanced Support APD Team

Are you sure the column type for the serial_num column is SERIAL and not SERIAL8 or BIGSERIAL (those types would require a different DBINFO function)? The DBINFO() call should always work if it is run immediately after the INSERT to the table containing the SERIAL column. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Aug 30, 2013 at 12:46 PM, Justin Killen < jkillen@allamericanasphalt.com> wrote: > When we do this: > > SELECT DBINFO( 'sqlca.sqlerrd1' ) FROM sysmaster:sysdual; > > We always get 0. This is on windows ODBC driver, 3.50.TC8 > > -Justin > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art > Kagel > Sent: Thursday, August 29, 2013 7:07 PM > To: ids@iiug.org > Subject: Re: Isolation modes in mach11 cluster [31310] > > Yeah, this: > > SELECT serial_num FROM infx_cluster_test WHERE serial_num = (select > max(serial_num) from infx_cluster_test)> Commit Transaction > > Is WRONG WRONG WRONG and very dangerous! You are VERY likely to get a > serial value inserted by another user session, especially if you turn on > the READ_COMMITTED isolation option! Instead do this: > > SELECT DBINFO( 'sqlca.sqlerrd1' ) FROM sysmaster:sysdual; > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, > implicitly, or by inference. Neither do those opinions reflect those of > other individuals affiliated with any entity with which I am affiliated nor > those of the entities themselves. > > On Thu, Aug 29, 2013 at 8:00 PM, Justin Killen < > jkillen@allamericanasphalt.com> wrote: > > > 1) table lock level is row locks, SQL for each test iteration is as > > follows: > > > > Begin Transaction > > > > INSERT INTO infx_cluster_test (host_name, id, data) VALUES ('JUSTINK-PC', > > 756, > > 'fp''L+h2$}vxV6NPjS;GKZZp/upua"V')> > > > SELECT serial_num FROM infx_cluster_test WHERE serial_num = (select > > max(serial_num) from infx_cluster_test)> > Commit Transaction > > > > This SQL is part of a base level data access layer that most every insert > > uses. It works flawlessly on our current single-system setup with > > USELASTCOMMITTED=NONE and SET LOCK MODE TO WAIT 2. > > > > 2) We tried setting USELASTCOMMITTED to ALL, but the result is that some > of > > the selects are not coming back with correct data (client A does the > > insert 3, > > then client B does an insert and a select max(serial_num), then client A > > does > > a select max(serial_num), resulting in client A getting client B's data). > > I'm not sure exactly what the difference is between ALL and COMMITTED > READ, > > and inline comments in onconfig seems circular - if anybody could shed > some > > light on the differences between the 4 options, that would be helpful. > > > > 3) We like the idea of setting the CLUSTER_TXN_SCOPE option to cluster, > but > > how does this effect things if the WAN link goes down to the RSS? > > > > -Justin > > > > -----Original Message----- > > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of > > Fernando > > Nunes > > Sent: Thursday, August 29, 2013 3:38 PM > > To: ids@iiug.org > > Subject: Re: Isolation modes in mach11 cluster [31307] > > > > Hmmm.... several important points: > > > > 1-I have some doubts (sorry if I'm mistaken) that you don't see the same > > behavior on just one (primary) server. What is your table lock level, and > > how is the query accessing the table? > > 2- If you want to try USELASTCOMMITTED you can set it to "Committed > Read". > > It shoudl not cause your application any harm. But your lock level must > be > > "row" > > 3- Depending on your version look for CLUSTER_TXN_SCOPE ( > > > > > > > > > > > http://pic.dhe.ibm.com/infocenter/idshelp/v117/topic/com.ibm.adref.doc/ids_adr_1 165.htm > > ). > > It allows you to define when your application recieves the > > confirmation > > of the COMMIT (can be when it's completely replicated across the cluster) > > > > Regards > > > > On Thu, Aug 29, 2013 at 11:15 PM, Justin Killen < > > jkillen@allamericanasphalt.com> wrote: > > > > > All, > > > > > > We are in the process of converting from a single database server to a > > > cluster, and we are having some confusion about transactions and > > isolation > > > levels. Our cluster consists of a primary and secondary HDR that are in > > the > > > same location (1gbps) and an RSS that is across a WAN link (4.5mpbs) We > > > designed a test app to run against the cluster (not yet in production, > > but > > > with a copy of the production data) to verify it is working as we > expect. > > > The > > > program basically does this: > > > > > > 1) Choose a server randomly (either primary, secondary HDR, or RSS) > > > 2) Begin transaction > > > 3) Insert/update/delete on test table: infx_cluster_test > > > 4) (on case of insert, do a select to get back a serial number) > > > 5) Commit transaction > > > Foreach server: > > > 6) Select from infx_cluster_test and verify the data is what we just > > > changed > > > it too. If it doesn't match, keep trying until it does. Here, we record > > how > > > many attempts were made, and the time-lapse between the initial > > > insert/update/delete and the successful verification select. > > > > > > Now then, we set up this program to run from a single computer, and it > > > works > > > pretty well. However, when we run it on multiple computers, we get > into a > > > scenario where each clients is connected to a different server and each > > of > > > them has begun a transaction, performed an insert, and is trying to > > > perform a > > > select (step 4). What we get here is the strange part. On one > connection, > > > we'll get error code 245 (could not position within a file via an > index), > > > and > > > on the other we'll get error code 246 (could not do an indexed read to > > get > > > the > > > next row). It was suggested to us to change our USELASTCOMMITTED > onconfig > > > value from NONE to ALL, but we're unsure of the implications of doing > so, > > > or > > > what side-effects it might have (the current default value within the > > > onconfig.std is set to NONE, so that's how we left it). > > > > > > Is there a standard setup that is commonly in use for mach11 cluster

Hmmm. Is the application written in Java? Could it be that the INSERT and select DBINFO()... are being executed in separate session threads? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Aug 30, 2013 at 1:09 PM, JACQUES RENAUT <jrenaut@us.ibm.com> wrote: > Original post: > > After you insert into a SERIAL column? > On the primary? > On Aug 30, 2013 5:46 PM, "Justin Killen" <jkillen@allamericanasphalt.com> > wrote: > > > When we do this: > > > > SELECT DBINFO( 'sqlca.sqlerrd1' ) FROM sysmaster:sysdual; > > > > We always get 0. This is on windows ODBC driver, 3.50.TC8 > > > > -Justin > > Response: > > I think maybe since you say you are getting a 0 on your dbinfo, that your > serial column isn't a serial, but perhaps a serial8 or big serial? If so > you'd > need to do select dbinfo('bigserial') from sysmaster:sysdual instead > > Jacques Renaut > IBM Informix Advanced Support > APD Team > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c1d7e0bcdb2704e52d7a83

I ran the test using a SQL workbench type application, which is java based. Upon further inspection, the SQL works fine in dbaccess. Our actual applications are in .NET using the .NET provider - I'm working on testing on that platform, and it looks like it's working (although there is still much testing to be done). -Justin -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art Kagel Sent: Friday, August 30, 2013 10:24 AM To: ids@iiug.org Subject: Re: RE: Isolation modes in mach11 cluster [31316] Hmmm. Is the application written in Java? Could it be that the INSERT and select DBINFO()... are being executed in separate session threads? Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Aug 30, 2013 at 1:09 PM, JACQUES RENAUT <jrenaut@us.ibm.com> wrote: > Original post: > > After you insert into a SERIAL column? > On the primary? > On Aug 30, 2013 5:46 PM, "Justin Killen" <jkillen@allamericanasphalt.com> > wrote: > > > When we do this: > > > > SELECT DBINFO( 'sqlca.sqlerrd1' ) FROM sysmaster:sysdual; > > > > We always get 0. This is on windows ODBC driver, 3.50.TC8 > > > > -Justin > > Response: > > I think maybe since you say you are getting a 0 on your dbinfo, that your > serial column isn't a serial, but perhaps a serial8 or big serial? If so > you'd > need to do select dbinfo('bigserial') from sysmaster:sysdual instead > > Jacques Renaut > IBM Informix Advanced Support > APD Team > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c1d7e0bcdb2704e52d7a83 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Good. Maybe that application is closing the connection or something like that. On Fri, Aug 30, 2013 at 7:41 PM, Justin Killen < jkillen@allamericanasphalt.com> wrote: > I ran the test using a SQL workbench type application, which is java based. > Upon further inspection, the SQL works fine in dbaccess. > Our actual applications are in .NET using the .NET provider - I'm working > on > testing on that platform, and it looks like it's working (although there is > still much testing to be done). > > -Justin > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art > Kagel > Sent: Friday, August 30, 2013 10:24 AM > To: ids@iiug.org > Subject: Re: RE: Isolation modes in mach11 cluster [31316] > > Hmmm. Is the application written in Java? Could it be that the INSERT and > select DBINFO()... are being executed in separate session threads? > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, > implicitly, or by inference. Neither do those opinions reflect those of > other individuals affiliated with any entity with which I am affiliated nor > those of the entities themselves. > > On Fri, Aug 30, 2013 at 1:09 PM, JACQUES RENAUT <jrenaut@us.ibm.com> > wrote: > > > Original post: > > > > After you insert into a SERIAL column? > > On the primary? > > On Aug 30, 2013 5:46 PM, "Justin Killen" <jkillen@allamericanasphalt.com > > > > wrote: > > > > > When we do this: > > > > > > SELECT DBINFO( 'sqlca.sqlerrd1' ) FROM sysmaster:sysdual; > > > > > > We always get 0. This is on windows ODBC driver, 3.50.TC8 > > > > > > -Justin > > > > Response: > > > > I think maybe since you say you are getting a 0 on your dbinfo, that your > > serial column isn't a serial, but perhaps a serial8 or big serial? If so > > you'd > > need to do select dbinfo('bigserial') from sysmaster:sysdual instead > > > > Jacques Renaut > > IBM Informix Advanced Support > > APD Team > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a11c1d7e0bcdb2704e52d7a83 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --047d7b3432442df77c04e52dcca7

Yeah, common problem in Java. If you are not careful to configure persistent threads each SQL statement is executed by a different thread in a different database session. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Advanced DataTools, the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Fri, Aug 30, 2013 at 1:41 PM, Justin Killen < jkillen@allamericanasphalt.com> wrote: > I ran the test using a SQL workbench type application, which is java based. > Upon further inspection, the SQL works fine in dbaccess. > Our actual applications are in .NET using the .NET provider - I'm working > on > testing on that platform, and it looks like it's working (although there is > still much testing to be done). > > -Justin > -----Original Message----- > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art > Kagel > Sent: Friday, August 30, 2013 10:24 AM > To: ids@iiug.org > Subject: Re: RE: Isolation modes in mach11 cluster [31316] > > Hmmm. Is the application written in Java? Could it be that the INSERT and > select DBINFO()... are being executed in separate session threads? > > Art > > Art S. Kagel > Advanced DataTools (www.advancedatatools.com) > Blog: http://informix-myview.blogspot.com/ > > Disclaimer: Please keep in mind that my own opinions are my own opinions > and do not reflect on my employer, Advanced DataTools, the IIUG, nor any > other organization with which I am associated either explicitly, > implicitly, or by inference. Neither do those opinions reflect those of > other individuals affiliated with any entity with which I am affiliated nor > those of the entities themselves. > > On Fri, Aug 30, 2013 at 1:09 PM, JACQUES RENAUT <jrenaut@us.ibm.com> > wrote: > > > Original post: > > > > After you insert into a SERIAL column? > > On the primary? > > On Aug 30, 2013 5:46 PM, "Justin Killen" <jkillen@allamericanasphalt.com > > > > wrote: > > > > > When we do this: > > > > > > SELECT DBINFO( 'sqlca.sqlerrd1' ) FROM sysmaster:sysdual; > > > > > > We always get 0. This is on windows ODBC driver, 3.50.TC8 > > > > > > -Justin > > > > Response: > > > > I think maybe since you say you are getting a 0 on your dbinfo, that your > > serial column isn't a serial, but perhaps a serial8 or big serial? If so > > you'd > > need to do select dbinfo('bigserial') from sysmaster:sysdual instead > > > > Jacques Renaut > > IBM Informix Advanced Support > > APD Team > > > > > > > > > > > ******************************************************************************* > > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > > > --001a11c1d7e0bcdb2704e52d7a83 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --089e01175f3194f11d04e52de1ea