IDS Forum

RE: Isolation modes in mach11 cluster

Posted By: Fernando Nunes
Date: Friday, 30 August 2013, at 12:49 p.m.

In Response To: RE: Isolation modes in mach11 cluster (Justin Killen)

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_1165.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.
>
>
>
> *******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>

--089e011772355434f304e52d029c

Messages In This Thread

• Isolation modes in mach11 cluster
  Justin Killen -- Thursday, 29 August 2013, at 5:15 p.m.
  • Re: Isolation modes in mach11 cluster
    Fernando Nunes -- Thursday, 29 August 2013, at 6:37 p.m.
    • RE: Isolation modes in mach11 cluster
      Justin Killen -- Thursday, 29 August 2013, at 8:00 p.m.
      • Re: Isolation modes in mach11 cluster
        Art Kagel -- Thursday, 29 August 2013, at 10:07 p.m.
        • RE: Isolation modes in mach11 cluster
          Justin Killen -- Friday, 30 August 2013, at 12:46 p.m.
          • RE: Isolation modes in mach11 cluster
            Fernando Nunes -- Friday, 30 August 2013, at 12:49 p.m.
            • Re: RE: Isolation modes in mach11 cluster
              JACQUES RENAUT -- Friday, 30 August 2013, at 1:09 p.m.
              • Re: RE: Isolation modes in mach11 cluster
                Art Kagel -- Friday, 30 August 2013, at 1:24 p.m.
                • RE: RE: Isolation modes in mach11 cluster
                  Justin Killen -- Friday, 30 August 2013, at 1:41 p.m.
                  • Re: RE: Isolation modes in mach11 cluster
                    Fernando Nunes -- Friday, 30 August 2013, at 1:46 p.m.
                  • Re: RE: Isolation modes in mach11 cluster
                    Art Kagel -- Friday, 30 August 2013, at 1:52 p.m.
          • Re: Isolation modes in mach11 cluster
            Art Kagel -- Friday, 30 August 2013, at 1:22 p.m.
      • Re: Isolation modes in mach11 cluster
        Fernando Nunes -- Friday, 30 August 2013, at 2:40 a.m.
  • Re: Isolation modes in mach11 cluster
    Art Kagel -- Thursday, 29 August 2013, at 10:03 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.