Tracking down a transaction isolation issue

I am trying to fully understand a scenario in Informix that has been bedeviling me for a long time. To be clear I am sure the error is mine but I want to understand why I am seeing the behavior I'm seeing. In the following scenario I am using an isolation level of "5" (READ LAST COMMITTED, the JDBC driver guide tells me). I've verified this on the database that indeed the connections attached to this database are at this isolation level. The database is set up with unbuffered logging, so transactions are enabled. I am interacting with Informix via an Enterprise Java Bean and JPA, so when I start talking about transactions they are started for me by the EJB container in conjunction with the Informix JDBC driver by way of JPA. Standard (Java) stuff. My scenario is this: I start a transaction. In this transaction I select a bunch of records from a table set up with row locking. These records come from "type" tables--tables referred to by foreign keys in a master table somewhere else. Then for various reasons some of which are valid and some of which are highly suspect :-) in a new transaction I go about updating the master table. The old transaction is at this point (again, for good and bad reasons) still in effect. Kaboom (-107; record locked). Despite the fact that these two transactions are isolated from one another (isn't that what it means to be transactional? Isn't that the "I" in ACID?), the second one fails because a detail record in the first one is locked, even though my isolation level should let me read committed values from the detail table when a lock is encountered. Specifically, the second one fails because when I attempt to insert the master row, during the foreign key referential integrity check to see if I've tied my new master record to an appropriate detail record, Informix cannot find a suitable target (a suitable detail row), because in order to do so it would need to read the detail row, and the first transaction has that row locked. My Java stack trace shows me that at the bottom is a -107 error (row locked), and that causes a referential constraint violation, and that causes my app to fail. This is very surprising to me. In PostgreSQL, which I've run this code on, these two transactions are isolated from one another: the fact that the first one is still in effect does not block the second from reading the detail row. The isolation in this case is READ_COMMITTED (ANSI), which is equivalent (I'm told) to Informix's READ LAST COMMITTED. This is true on SQL Server as well (and H2). It seems to me, then, that as long as the first transaction has selected some row in a table somewhere that the second transaction has to read, I am hosed unless I drop the isolation level down to something even "dirtier" (but then JPA, my technology that I'm using to read and write from the data tier, is guaranteed not to work (it requires at least a READ_COMMITTED (ANSI) isolation level)). What is the general pattern for a scenario like this? Or please, if I'm being abysmally stupid please do let me know how and why. Thanks as always, Laird -- http://about.me/lairdnelson --20cf3074b69241075104cdf345d8

On Wed, Nov 7, 2012 at 7:17 PM, Laird Nelson <ljnelson@gmail.com> wrote: > Kaboom (-107; record locked). > I should add here that I'm aware of the ifxIFX_LOCK_MODE_WAIT data source property. But I would like to know specifically how setting a lock timeout in my scenario would help. Wouldn't it just delay the inevitable? I know it wouldn't do any harm, but I can't trace out how it would help. That's the level of detail at which I would like to understand this problem. Best, Laird -- http://about.me/lairdnelson --047d7b677c8e913dd204cdf4da4a

I must be missing something... COMMITTED READ LAST COMMITTED does not lock rows that are read. Are you sure your code is not trying to "SELECT FOR UPDATE"? Some more information from the engine before the problem happens (like the locks that are set and their type) would be helpful. You can also force an assert fail when the error happens (onmode -I 107) that will dump a lot of information... Don't do this on a production system and you may want to turn off shared memory dump (onmode -wm DUMPSHMEM=0) before doing it. To turn it off just "onmode -I) The file generated will appear in online.log (onstat -m) Regards. On Thu, Nov 8, 2012 at 3:17 AM, Laird Nelson <ljnelson@gmail.com> wrote: > I am trying to fully understand a scenario in Informix that has been > bedeviling me for a long time. > > To be clear I am sure the error is mine but I want to understand why I am > seeing the behavior I'm seeing. > > In the following scenario I am using an isolation level of "5" (READ LAST > COMMITTED, the JDBC driver guide tells me). I've verified this on the > database that indeed the connections attached to this database are at this > isolation level. > > The database is set up with unbuffered logging, so transactions are > enabled. > > I am interacting with Informix via an Enterprise Java Bean and JPA, so when > I start talking about transactions they are started for me by the EJB > container in conjunction with the Informix JDBC driver by way of JPA. > Standard (Java) stuff. > > My scenario is this: > > I start a transaction. In this transaction I select a bunch of records > from a table set up with row locking. These records come from "type" > tables--tables referred to by foreign keys in a master table somewhere > else. > > Then for various reasons some of which are valid and some of which are > highly suspect :-) in a new transaction I go about updating the master > table. The old transaction is at this point (again, for good and bad > reasons) still in effect. > > Kaboom (-107; record locked). > > Despite the fact that these two transactions are isolated from one another > (isn't that what it means to be transactional? Isn't that the "I" in > ACID?), the second one fails because a detail record in the first one is > locked, even though my isolation level should let me read committed values > from the detail table when a lock is encountered. > > Specifically, the second one fails because when I attempt to insert the > master row, during the foreign key referential integrity check to see if > I've tied my new master record to an appropriate detail record, Informix > cannot find a suitable target (a suitable detail row), because in order to > do so it would need to read the detail row, and the first transaction has > that row locked. > > My Java stack trace shows me that at the bottom is a -107 error (row > locked), and that causes a referential constraint violation, and that > causes my app to fail. > > This is very surprising to me. > > In PostgreSQL, which I've run this code on, these two transactions are > isolated from one another: the fact that the first one is still in effect > does not block the second from reading the detail row. The isolation in > this case is READ_COMMITTED (ANSI), which is equivalent (I'm told) to > Informix's READ LAST COMMITTED. This is true on SQL Server as well (and > H2). > > It seems to me, then, that as long as the first transaction has selected > some row in a table somewhere that the second transaction has to read, I am > hosed unless I drop the isolation level down to something even "dirtier" > (but then JPA, my technology that I'm using to read and write from the data > tier, is guaranteed not to work (it requires at least a READ_COMMITTED > (ANSI) isolation level)). > > What is the general pattern for a scenario like this? Or please, if I'm > being abysmally stupid please do let me know how and why. > > Thanks as always, > Laird > > -- > http://about.me/lairdnelson > > --20cf3074b69241075104cdf345d8 > > > > ******************************************************************************* > 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... --047d7bdc1010b0fe4d04cdf93e34

Usually the lock mode set to wait N will delay the error for as long as N seconds. The idea is that the other transactions should be able to terminate in that time. Regarding your comparison with other databases... PostgresSQL is a "multi-version" database. That makes thing completely different... Nevertheless with LAST COMMITTED you should give you the closest to that we can get. Regards. On Thu, Nov 8, 2012 at 5:10 AM, Laird Nelson <ljnelson@gmail.com> wrote: > On Wed, Nov 7, 2012 at 7:17 PM, Laird Nelson <ljnelson@gmail.com> wrote: > > > Kaboom (-107; record locked). > > > > I should add here that I'm aware of the ifxIFX_LOCK_MODE_WAIT data source > property. But I would like to know specifically how setting a lock timeout > in my scenario would help. Wouldn't it just delay the inevitable? I know > it wouldn't do any harm, but I can't trace out how it would help. That's > the level of detail at which I would like to understand this problem. > > Best, > Laird > -- > http://about.me/lairdnelson > > --047d7b677c8e913dd204cdf4da4a > > > > ******************************************************************************* > 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... --00032557cd2eb275a004cdf94cbc

On Wed, Nov 7, 2012 at 7:17 PM, Laird Nelson <ljnelson@gmail.com> wrote: > Kaboom (-107; record locked). > For COMMITTED READ LAST COMMITTED to work the tables involved have to be at row level locking, not page level. Are there serial columns involved in the transactions? When we first turned on transactions we had a number of locking issues due to serial columns, changed them to integer and used a sequence to populate it. I think it something to do with not being able to assign the next serial while waiting to see what current transaction is going to do with it... Regards, Bryce Stenberg.

On Thu, Nov 8, 2012 at 12:38 PM, BRYCE STENBERG <bryce@hrnz.co.nz> wrote: > For COMMITTED READ LAST COMMITTED to work the tables involved have to be at > row level locking, not page level. > Yep; that's taken care of. > Are there serial columns involved in the transactions? When we first > turned on > transactions we had a number of locking issues due to serial columns, > changed > them to integer and used a sequence to populate it. I think it something > to do > with not being able to assign the next serial while waiting to see what > current transaction is going to do with it... > No, but good thought. Best, Laird -- http://about.me/lairdnelson --0016e68ee9e495917c04ce0434b4