Isolation level on a connection pool data source

I need to set the isolation level on a Glassfish connection pool that is hooked up to Informix 11.7. We are (apparently) running a non-ANSI database that does not have a default isolation level of LAST COMMITTED (5), so in our case we need to set it deliberately. From consulting the JDBC programmer's guide, I see lots of references to setting Informix environment variables. But in my case I'm coming from another machine, so an environment variable set on that machine wouldn't have any effect on the Informix machine (unless I'm missing something). I also see references to setting data source properties, but they make reference to Java methods (setIfxWHATEVER()). I'm in configuration land--i.e. I'm setting up a connection pool by specifying properties and values, not by calling Java methods. From banging around in Google, it looks like there is some (undocumented?) convention where if I specify a property named, say, ifxFRED on my connection pool, then the underlying DataSource will get a setIfxFRED() call somehow handed to it. So, then, summed up: would I specify a property of ifxIFX_ISOLATION_LEVEL with a value of (in my case) 5? Best, Laird -- http://about.me/lairdnelson --0016e6d5895ec22d4304c029ad7c

On Wed, May 16, 2012 at 5:19 PM, Laird Nelson <ljnelson@gmail.com> wrote: > I need to set the isolation level on a Glassfish connection pool that is > hooked up to Informix 11.7. We are (apparently) running a non-ANSI > database that does not have a default isolation level of LAST COMMITTED > (5), so in our case we need to set it deliberately. > No Informix database will have the default set to LAST COMMITTED READ. ANSI type databases will have SERIALIZABLE. > > >From consulting the JDBC programmer's guide, I see lots of references to > setting Informix environment variables. But in my case I'm coming from > another machine, so an environment variable set on that machine wouldn't > have any effect on the Informix machine (unless I'm missing something). > Not true. The variables are available to the driver and it will use them (and adjust the behavior) > > I also see references to setting data source properties, but they make > reference to Java methods (setIfxWHATEVER()). I'm in configuration > land--i.e. I'm setting up a connection pool by specifying properties and > values, not by calling Java methods. > > >From banging around in Google, it looks like there is some (undocumented?) > convention where if I specify a property named, say, ifxFRED on my > connection pool, then the underlying DataSource will get a setIfxFRED() > call somehow handed to it. > > So, then, summed up: would I specify a property of ifxIFX_ISOLATION_LEVEL > with a value of (in my case) 5? > Just set it as a variable. Make sure you use a recent JDBC version... I believe some older 3.50 versions would not recognize the value 5. After creating the connections use "onstat -g ses SID" to check it's correct. Regards. -- Fernando Nunes Portugal http://informix-technology.blogspot.com My email works... but I don't check it frequently... --0023544705ec1c97d104c029d02f

On Wed, May 16, 2012 at 12:28 PM, Fernando Nunes <domusonline@gmail.com>wrote: > > I need to set the isolation level on a Glassfish connection pool that is > > hooked up to Informix 11.7. We are (apparently) running a non-ANSI > > database that does not have a default isolation level of LAST COMMITTED > > (5), so in our case we need to set it deliberately. > > No Informix database will have the default set to LAST COMMITTED READ. ANSI > type databases will have SERIALIZABLE. > Interesting; OK; I'll be sure to let my database guys know this, since I believe they were under this impression. (This is VERY interesting to me, and perhaps is just a rookie mistake: Take some other database, like PostgreSQL or Oracle or SQL Server. By default (at least using JDBC) that database vendor driver is usually set to the default JDBC isolation level of TRANSACTION_READ_COMMITTED ( http://docs.oracle.com/javase/6/docs/api/java/sql/Connection.html#TRANSACTION_RE AD_COMMITTED). I take it you're saying this is not true of the Informix JDBC driver? In addition, the JPA specification says, in part, section 3.4, that "it assumes that the databases to which persistence units are mapped will be accessed by the implementation using read-committed isolation (or a vendor equivalent in which long-term read locks are not held)". So in our case we would need to make sure that this isolation level is set explicitly, yes?) > > >From consulting the JDBC programmer's guide, I see lots of references to > > setting Informix environment variables. But in my case I'm coming from > > another machine, so an environment variable set on that machine wouldn't > > have any effect on the Informix machine (unless I'm missing something). > > > > Not true. The variables are available to the driver and it will use them > (and adjust the behavior) > Oh, how wonderful. Good. So "environment variable" really means any of those properties can be appended to the JDBC URL? That's great. So a url of jdbc:informix-sqli://somehost/db;IFX_ISOLATION_LEVEL=5... (with other properties too, obviously) should work? I think I see what the problem is: the JDBC programmer's guide in section 2 (2-5?) contains a pretty nasty formatting error that messes up the table describing the format of JDBC URLs. The last entry in the table has a "key" of "None" (?) which I think should really be "name=value". Then its explanation column says: "A name-value pair that specifies a value for the Informix environment variable contained in the *name *variable, recognized by either IBM Informix JDBC Driver or Informix database servers "The *name *variable is not case sensitive.See 'Specify properties' on page 2-9 and 'Informix environment variables with the IBM Informix JDBC Driver' on page 2-10 for more information." Of course, if you go to the "specify properties" section on page 2-9, there's a small paragraph that refers you back to the table. Then there's a lot of stuff on how to set properties programmatically. Hence the confusion. > Thanks for your prompt response. Best, Laird -- http://about.me/lairdnelson --f46d0442879eea083804c02a1283

Hello. Yes, you could use an additional parameter, directly on your connection string, example is below: Connection conn = DriverManager.getConnection( "jdbc:Informixsqli://cleo:1550:INFORMIXSERVER=cleo_921;IFXHOST=cleo;PORTNO=1550; user=rdtest;password=my_passwd; IFX_ISOLATION_LEVEL=1U";); The options available are described here: http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.jdbc_pg.doc/ ids_jdbc_040.htm?resultof=%22%49%46%58%5f%49%53%4f%4c%41%54%49%4f%4e%5f%4c%45%56 %45%4c%22%20 Just do a simple connection, and check on your Informix side, if your session is right. Tip: be careful to check sysdbpen procedures, they might change your configurations without your "intents". Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 IBM Information Management Informix Technical Professional IBM Infosphere DataStage Technical Professional Database Administrator > To: ids@iiug.org > From: ljnelson@gmail.com > Subject: Re: Isolation level on a connection pool data .... [27147] > Date: Wed, 16 May 2012 12:47:31 -0400 > > On Wed, May 16, 2012 at 12:28 PM, Fernando Nunes <domusonline@gmail.com>wrote: > > > > I need to set the isolation level on a Glassfish connection pool that is > > > hooked up to Informix 11.7. We are (apparently) running a non-ANSI > > > database that does not have a default isolation level of LAST COMMITTED > > > (5), so in our case we need to set it deliberately. > > > > No Informix database will have the default set to LAST COMMITTED READ. ANSI > > type databases will have SERIALIZABLE. > > > > Interesting; OK; I'll be sure to let my database guys know this, since I > believe they were under this impression. > > (This is VERY interesting to me, and perhaps is just a rookie mistake: Take > some other database, like PostgreSQL or Oracle or SQL Server. By default > (at least using JDBC) that database vendor driver is usually set to the > default JDBC isolation level of TRANSACTION_READ_COMMITTED ( > > http://docs.oracle.com/javase/6/docs/api/java/sql/Connection.html#TRANSACTION_RE AD_COMMITTED). > I take it you're saying this is not true of the Informix JDBC driver? In > addition, the JPA specification says, in part, section 3.4, that "it > assumes that the databases to which persistence units are mapped will be > accessed by the implementation using read-committed isolation (or a vendor > equivalent in which long-term read locks are not held)". So in our case we > would need to make sure that this isolation level is set explicitly, yes?) > > > > >From consulting the JDBC programmer's guide, I see lots of references to > > > setting Informix environment variables. But in my case I'm coming from > > > another machine, so an environment variable set on that machine wouldn't > > > have any effect on the Informix machine (unless I'm missing something). > > > > > > > Not true. The variables are available to the driver and it will use them > > (and adjust the behavior) > > > > Oh, how wonderful. Good. So "environment variable" really means any of > those properties can be appended to the JDBC URL? That's great. So a url > of jdbc:informix-sqli://somehost/db;IFX_ISOLATION_LEVEL=5... (with other > properties too, obviously) should work? > > I think I see what the problem is: the JDBC programmer's guide in section 2 > (2-5?) contains a pretty nasty formatting error that messes up the table > describing the format of JDBC URLs. The last entry in the table has a > "key" of "None" (?) which I think should really be "name=value". Then its > explanation column says: > > "A name-value pair that specifies a value for the Informix environment > variable contained in the *name *variable, recognized by either IBM > Informix JDBC Driver or Informix database servers > > "The *name *variable is not case sensitive.See 'Specify properties' on page > 2-9 and 'Informix environment variables with the IBM Informix JDBC Driver' > on page 2-10 for more information." > Of course, if you go to the "specify properties" section on page 2-9, > there's a small paragraph that refers you back to the table. Then there's > a lot of stuff on how to set properties programmatically. Hence the > confusion. > > > Thanks for your prompt response. > > Best, > Laird > > -- > http://about.me/lairdnelson > > --f46d0442879eea083804c02a1283 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

On Wed, May 16, 2012 at 12:47 PM, Laird Nelson <ljnelson@gmail.com> wrote: > On Wed, May 16, 2012 at 12:28 PM, Fernando Nunes <domusonline@gmail.com > >wrote: > > Not true. The variables are available to the driver and it will use them > > (and adjust the behavior) > For those following along, I've found that the "ifx" prefix is necessary, unfortunately, when these variables are being set as "additional properties" on Glassfish. I suspect that's because there are setter-method calls being invoked under the covers. So setting an "additional property" of, for example, IFXHOST doesn't work, but setting an ifxIFXHOST "additional property" does. Best, Laird -- http://about.me/lairdnelson --f46d0442835cbf72cf04c02a638a

Informix's default isolation level for non-ANSI databases is COMMITTED READ, which is the equivalent to the TRANSACTION_READ_COMMITTED isolation level for JDBC that you are asking about. What Fernando is noting is that the LAST COMMITTED option to COMMITTED READ isolation is not active by default. Without this option, you still will only see committed data, but locks will be used to enforce that if another user is modifying the row you want to examine. With the LAST COMMITTED option active then you will see a copy of the last committed value even if the row has been modified but not yet committed. Note that either way, Optimistic Concurrency Control protocols must be used to guarantee data consistency among multiple users. 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 Wed, May 16, 2012 at 12:47 PM, Laird Nelson <ljnelson@gmail.com> wrote: > On Wed, May 16, 2012 at 12:28 PM, Fernando Nunes <domusonline@gmail.com > >wrote: > > > > I need to set the isolation level on a Glassfish connection pool that > is > > > hooked up to Informix 11.7. We are (apparently) running a non-ANSI > > > database that does not have a default isolation level of LAST COMMITTED > > > (5), so in our case we need to set it deliberately. > > > > No Informix database will have the default set to LAST COMMITTED READ. > ANSI > > type databases will have SERIALIZABLE. > > > > Interesting; OK; I'll be sure to let my database guys know this, since I > believe they were under this impression. > > (This is VERY interesting to me, and perhaps is just a rookie mistake: Take > some other database, like PostgreSQL or Oracle or SQL Server. By default > (at least using JDBC) that database vendor driver is usually set to the > default JDBC isolation level of TRANSACTION_READ_COMMITTED ( > > > http://docs.oracle.com/javase/6/docs/api/java/sql/Connection.html#TRANSACTION_RE AD_COMMITTED > ). > I take it you're saying this is not true of the Informix JDBC driver? In > addition, the JPA specification says, in part, section 3.4, that "it > assumes that the databases to which persistence units are mapped will be > accessed by the implementation using read-committed isolation (or a vendor > equivalent in which long-term read locks are not held)". So in our case we > would need to make sure that this isolation level is set explicitly, yes?) > > > > >From consulting the JDBC programmer's guide, I see lots of references > to > > > setting Informix environment variables. But in my case I'm coming from > > > another machine, so an environment variable set on that machine > wouldn't > > > have any effect on the Informix machine (unless I'm missing something). > > > > > > > Not true. The variables are available to the driver and it will use them > > (and adjust the behavior) > > > > Oh, how wonderful. Good. So "environment variable" really means any of > those properties can be appended to the JDBC URL? That's great. So a url > of jdbc:informix-sqli://somehost/db;IFX_ISOLATION_LEVEL=5... (with other > properties too, obviously) should work? > > I think I see what the problem is: the JDBC programmer's guide in section 2 > (2-5?) contains a pretty nasty formatting error that messes up the table > describing the format of JDBC URLs. The last entry in the table has a > "key" of "None" (?) which I think should really be "name=value". Then its > explanation column says: > > "A name-value pair that specifies a value for the Informix environment > variable contained in the *name *variable, recognized by either IBM > Informix JDBC Driver or Informix database servers > > "The *name *variable is not case sensitive.See 'Specify properties' on page > 2-9 and 'Informix environment variables with the IBM Informix JDBC Driver' > on page 2-10 for more information." > Of course, if you go to the "specify properties" section on page 2-9, > there's a small paragraph that refers you back to the table. Then there's > a lot of stuff on how to set properties programmatically. Hence the > confusion. > > > Thanks for your prompt response. > > Best, > Laird > > -- > http://about.me/lairdnelson > > --f46d0442879eea083804c02a1283 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7b339c11d0698d04c02a6e7a

On Wed, May 16, 2012 at 1:13 PM, Art Kagel <art.kagel@gmail.com> wrote: > Informix's default isolation level for non-ANSI databases is COMMITTED > READ, which is the equivalent to the TRANSACTION_READ_COMMITTED isolation > level for JDBC that you are asking about. Excellent. We've found that with this level activated (that is, relying on defaults only) a batch job that inserts (note: inserts, not updates), say, a gazillion new rows into a table prevents another connection from reading from that table. This is really strange to me, but no doubt I'm just missing something obvious. This does not appear in the other databases we support. Our working theory is that Informix's default isolation level is more restrictive than that of any other database we support (i.e. that they all default to an isolation like COMMITTED READ LAST COMMITTED). Does this sound reasonable, or are we off wandering dazed and stupid in the weeds somewhere? > What Fernando is noting is that > the LAST COMMITTED option to COMMITTED READ isolation is not active by > default. Right, understood. > Without this option, you still will only see committed data, but > locks will be used to enforce that if another user is modifying the row you > want to examine. I didn't parse this. What if another user is inserting rows into the same table? (That's our case.) User A is inserting; user B is doing a select over the table at the same time. Obviously we don't want user B to get *uncommitted* changes--I get that; I'm at least that smart :-)--but where I'm stupid is that I would assume that user B would be able to see the rows that already existed in the table before User A started his (long-running) insertions. Instead we get lock violations. > With the LAST COMMITTED option active then you will see a > copy of the last committed value even if the row has been modified but not > yet committed. Right; in our example (detailed above) it surprises me that I would need to explicitly set this behavior. I'm sure I'm just missing something. Best, Laird > -- http://about.me/lairdnelson --f46d043d67edc0e5a204c02a8db1

On Wed, May 16, 2012 at 1:09 PM, Alexandre Marini <alexandre@briug.org>wrote: > Yes, you could use an additional parameter, directly on your connection > string, example is below: > > Connection conn = DriverManager.getConnection( > > "jdbc:Informixsqli://cleo:1550:INFORMIXSERVER=cleo_921;IFXHOST=cleo;PORTNO=1550; user=rdtest;password=my_passwd; > IFX_ISOLATION_LEVEL=1U";); > Thank you. In my case, I was attempting to set these properties on the Additional Properties tab in the Glassfish connection pool setup area. I believe that what may be going on is that if you set them *there*, then Glassfish turns them into setter calls on the underlying data source. So it might be that it's OK to set them as you advise above in the URL, but if you set them in other places, then they might need the "ifx" prefix. I have an open question out to the Glassfish guys on this. The options available are described here: > > > http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.jdbc_pg.doc/ ids_jdbc_040.htm?resultof=%22%49%46%58%5f%49%53%4f%4c%41%54%49%4f%4e%5f%4c%45%56 %45%4c%22%20 Yes; thank you; very familiar with that. For future reference, here are the sentences from there that messed me up: The following table lists most of the IBM® Informix® environment variables supported by the client JDBC driver. For server-side JDBC, use property settings in the database URL rather than setting environment variables, because the environment variables would apply to all programs running in the database server. For more information about properties, see Specify properties<http://publib.boulder.ibm.com/infocenter/idshelp/v117/topic/com.ibm.j dbc_pg.doc/ids_jdbc_039.htm#ids_jdbc_039> . Note that it says "for server-side JDBC, use property settings in the database URL", leading me to believe that if I am using client-side JDBC--which I am--that I should NOT be specifying these as property settings in the database URL. It also mentions that setting environment variables "would apply to all programs running in the database server", which of course makes sense, and further led me to believe that *however* one sets environment variables in client-side JDBC, it would be the wrong thing to do. Clearly that is not the case, but I would submit this bit of documentation could use a scrub. :-) Best, Laird > -- http://about.me/lairdnelson --f46d04374a0f1c608904c02a9f13

Informix users locking to enforce data isolation by default (whereas Oracle uses row versioning). If you are inserting many rows in a single transaction then those rows will all be locked (or if the table's lock mode is page then all of the pages on which those new rows reside will be locked) for the duration of the transaction. If they are singleton inserts then the locks will be transitory. Either way, normally in an app accessing Informix, you will issue a "SET LOCK MODE TO WAIT <nseconds>;" so that transitory locks do not cause errors. This improves concurrency without reducing isolation. On the other hand, if you also set LAST COMMITTED option, then, more like Oracle's versioning, Informix will ignore the locks but return the committed version of any locked row. The LAST COMMITTED behavior is not the default because it may break legacy Informix applications that depend on the locking behavior. So, either add the LAST COMMITTED option or SET LOCK MODE TO WAIT 10; (this will not help much if the inserts are part of a single huge transaction though). 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 Wed, May 16, 2012 at 1:21 PM, Laird Nelson <ljnelson@gmail.com> wrote: > On Wed, May 16, 2012 at 1:13 PM, Art Kagel <art.kagel@gmail.com> wrote: > > > Informix's default isolation level for non-ANSI databases is COMMITTED > > READ, which is the equivalent to the TRANSACTION_READ_COMMITTED isolation > > level for JDBC that you are asking about. > > Excellent. We've found that with this level activated (that is, relying on > defaults only) a batch job that inserts (note: inserts, not updates), say, > a gazillion new rows into a table prevents another connection from reading > from that table. This is really strange to me, but no doubt I'm just > missing something obvious. This does not appear in the other databases we > support. Our working theory is that Informix's default isolation level is > more restrictive than that of any other database we support (i.e. that they > all default to an isolation like COMMITTED READ LAST COMMITTED). Does this > sound reasonable, or are we off wandering dazed and stupid in the weeds > somewhere? > > > What Fernando is noting is that > > the LAST COMMITTED option to COMMITTED READ isolation is not active by > > default. > > Right, understood. > > > Without this option, you still will only see committed data, but > > locks will be used to enforce that if another user is modifying the row > you > > want to examine. > > I didn't parse this. What if another user is inserting rows into the same > table? (That's our case.) User A is inserting; user B is doing a select > over the table at the same time. Obviously we don't want user B to get > *uncommitted* changes--I get that; I'm at least that smart :-)--but where > I'm stupid is that I would assume that user B would be able to see the rows > that already existed in the table before User A started his (long-running) > insertions. Instead we get lock violations. > > > With the LAST COMMITTED option active then you will see a > > copy of the last committed value even if the row has been modified but > not > > yet committed. > > Right; in our example (detailed above) it surprises me that I would need to > explicitly set this behavior. I'm sure I'm just missing something. > > Best, > Laird > > > > -- > http://about.me/lairdnelson > > --f46d043d67edc0e5a204c02a8db1 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7b339d8fa83b6e04c02abe5c

On Wed, May 16, 2012 at 1:35 PM, Art Kagel <art.kagel@gmail.com> wrote: > Informix users [sic] locking to enforce data isolation by default (whereas > Oracle > uses row versioning). If you are inserting many rows in a single > transaction then those rows will all be locked (or if the table's lock mode > is page then all of the pages on which those new rows reside will be > locked) for the duration of the transaction. Yes, OK, got that. (And thank you for bearing with me on all this.) So another transaction (B) comes along and does a SELECT * FROM the_table_where_a_huge_batch_job_is_currently_inserting_records_in_transaction_A . I understand that the **rows** that are in the middle of being inserted by transaction A should not be seen by transaction B while A is in progress. That is just what it means to be uncommitted--if B is any kind of COMMITTED READ then there should be no way it can see A's data. I get that. What I don't get is: why would A's **locks** on those rows get in B's way? Under what scenario would this *ever* be what you wanted? B shouldn't even see A's locks or be aware of A's existence, right? In my (thick) head, B being able to "see" (and hence be screwed up by) A's locks is weird. Why wouldn't it just say, oh, hey, these rows are locked by someone else doing something transactional that I shouldn't see anyway, so I'll just skip them (which is the LAST COMMITTED option behavior)? Why would B *ever* want to bomb out because A happens to be inserting rows that B shouldn't ever see anyway? > If they are singleton inserts > then the locks will be transitory. Either way, normally in an app > accessing Informix, you will issue a "SET LOCK MODE TO WAIT <nseconds>;" so > that transitory locks do not cause errors. Sure; in our case this is a huge batch insert process that must all complete in one transaction, so we'd be setting a WAIT value of {insert ginormous number here} which is impractical. > This improves concurrency > without reducing isolation. On the other hand, if you also set LAST > COMMITTED option, then, more like Oracle's versioning, Informix will ignore > the locks but return the committed version of any locked row. > OK, good, so this is indeed the approach we want. I'm still (thickly) trying to come up with some rationale for the default behavior though; I just can't see it. I'm sure it's there but a patient explanation of why it would be a good idea would be humbly welcomed. > Best, Laird -- http://about.me/lairdnelson --f46d043d67e51765dd04c02ae070

The engine doesn't know that session B doesn't want to see the rows inserted or updated by session A. Maybe it does but A is taking longer than usual and shouldn't have started yet. The engine has no way to know. When Informix was designed, indeed when most RDBMS's were designed back in the '80s and '90s, the rational went that way. Everyone did it that way for a long time, including Oracle. Many still do, including Informix. Versioning began with Interbase in the mid-90's and took off infecting Oracle and several others. There are some problems with using versioning for concurrency control which are not technical but philosophical. Application programmers who only know versioning databases get lazy. They assume that a row that a session is working on will never be modified by another user, that rows that they have read at the beginning of a transaction will never be deleted before they commit, that no new rows that should interest their transaction will ever be inserted during their transaction. In many organizations, due to work flow realities, this is ALMOST ALWAYS true. It's the almost that causes problems and can result in inconsistent data and balances that don't balance. That is the likely reason that Informix resisted going to versioning for all of these years. The LAST COMMITTED option was added about 3 years ago. It works great to reduce contention and improve concurrency, but applications still have to be properly written to avoid inconsistent data. In your case, since session A is only inserting new rows, and sessions B-Z.... are only operating on existing rows that I assume are independent of those being inserted, LAST COMMITTED will work well. If there were dependencies, you would have to stay with the default isolation model to insure consistency because of the very long/large transaction. 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 Wed, May 16, 2012 at 1:44 PM, Laird Nelson <ljnelson@gmail.com> wrote: > On Wed, May 16, 2012 at 1:35 PM, Art Kagel <art.kagel@gmail.com> wrote: > > > Informix users [sic] locking to enforce data isolation by default > (whereas > > Oracle > > uses row versioning). If you are inserting many rows in a single > > transaction then those rows will all be locked (or if the table's lock > mode > > is page then all of the pages on which those new rows reside will be > > locked) for the duration of the transaction. > > Yes, OK, got that. (And thank you for bearing with me on all this.) > > So another transaction (B) comes along and does a SELECT * FROM > > > the_table_where_a_huge_batch_job_is_currently_inserting_records_in_transaction_A . > I understand that the **rows** that are in the middle of being inserted by > transaction A should not be seen by transaction B while A is in progress. > That is just what it means to be uncommitted--if B is any kind of > COMMITTED READ then there should be no way it can see A's data. I get that. > > What I don't get is: why would A's **locks** on those rows get in B's way? > Under what scenario would this *ever* be what you wanted? B shouldn't > even see A's locks or be aware of A's existence, right? In my (thick) > head, B being able to "see" (and hence be screwed up by) A's locks is > weird. Why wouldn't it just say, oh, hey, these rows are locked by someone > else doing something transactional that I shouldn't see anyway, so I'll > just skip them (which is the LAST COMMITTED option behavior)? Why would B > *ever* want to bomb out because A happens to be inserting rows that B > shouldn't ever see anyway? > > > If they are singleton inserts > > then the locks will be transitory. Either way, normally in an app > > accessing Informix, you will issue a "SET LOCK MODE TO WAIT <nseconds>;" > so > > that transitory locks do not cause errors. > > Sure; in our case this is a huge batch insert process that must all > complete in one transaction, so we'd be setting a WAIT value of {insert > ginormous number here} which is impractical. > > > This improves concurrency > > without reducing isolation. On the other hand, if you also set LAST > > COMMITTED option, then, more like Oracle's versioning, Informix will > ignore > > the locks but return the committed version of any locked row. > > > > OK, good, so this is indeed the approach we want. I'm still (thickly) > trying to come up with some rationale for the default behavior though; I > just can't see it. I'm sure it's there but a patient explanation of why it > would be a good idea would be humbly welcomed. > > > Best, > Laird > > -- > http://about.me/lairdnelson > > --f46d043d67e51765dd04c02ae070 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --047d7b15fe0b3f027404c02b18b8