SDS secondary and LOCK TABLE

Hello all. I recently had an issue with a programmer on our site, regarding concurrency control and an SDS environment. The problem is, that he had a little table that was used to control an order sequence number. No serial column or sequence were used. It is a normal table and the sequence column is an integer. The table is from a local ERP package, and the program is a customer´s customization. Here follows some more information. This is an example on the table´s data (let´s call it order_seq): company user seq 01 user1 10 01 user2 10 01 user3 10 02 user1 153 02 user5 153 seq - is the column that contains the order sequence number, and in this particular customer case, no matter what the user is, the order will be the same for the company column. Other ERP customers may not use the same approach but for this particular customer, that is the behaviour. So the ERP developers defined company and user as the primary key, but for us, company is what matters. So our developer had in his customized code, something like the code that follows, in order to increment the seq column: begin work; lock table order_seq in exclusive mode; *** Here the programmer assumed no concurrency issues if (not ok) then abort the program end if select max(seq) into v_seq from order_seq where company = '01'; .... do some code ... let v_seq = v_seq + 1; update order_seq set seq = v_seq where company = '01'; ... do some other code ... if (ok) then commit work; else rollback work; end if; Without discussing the quality of the code, since there are better ways of doing it, this code worked well for years. About 10 months ago, we configured an SDS environment using the load balance capabilities (knowing in advance its limitations). But what happened? When this code ran on the secondary node, and it reached the LOCK TABLE statement, no error or warning messages were generated, but IDS did not really locked anything. We debugged the program and saw no sign of locks on the primary node, nor on the secondary. However, when this code ran on the primary, the lock was in place! When the programmer complained about two orders with the same number, I immediatelly tested the LOCK TABLE on dbaccess on the secondary node, and to my surprise, it says that the table is locked, when in fact no locks were placed at all: begin work; lock table order_seq in exclusive mode; ... Table locked. *** But no locks on syslocks and onstat -k. So, I opened a case at IBM, and the response until now is that the behaviour is expected since the LOCK TABLE statement is 'ignored' on the secondary nodes, and this was a design decision. This answer is questionable, and the issue is very dangerous, at least in my point of view! Follows some of the arguments one can pose: 1-There is no documentation on this behaviour, at least at infocenter. IBM support said they are going to correct the documentation now! 2-Shouldn´t the engine be a little more verbose about something we think it is doing, and in fact it is not? Some kind of error message or warning that the application receive and can be aware of it (the application programmer might correct the code, or at least, this program can be moved to run only the primary node)? So the load balance becomes questionable here? 3-How can dbaccess (or IDS engine) say that the table is locked, when in fact it is not? 4-Can SDS be used on a load balance environment without any risk (as other manufacturers claim their product can), since one´s company can be using a product without access to the source code? Imagine how much time we have to wait for the ERP developers guarantee that their code runs on an SDS environment correctly! I am posting this here, to share the issue with everyone, and to ask if anyone else has problems close to this one, that we might be aware of. I know of one other 'big' limitation regarding temporary tables and transaction logs, but this one is at least very well documented! But I also think that the LOCK TABLE is much more dangerous in terms of stability and consistency. As most of you, I use Informix products for a long time, I like them a lot, I fight for them almost any time, but I feel that when decisions are questionable we also have to talk about it! Thank you!

On Apr 22, 9:29 pm, rdtbra <reit...@gmail.com> wrote: Sorry for the top post. I'm leaving the original message intact below. Historically the secondary nodes work by providing dirty read isolation. In 11.00, we extended the isolation to support committed and last committed. This is done by letting the recovery threads take locks as they are applying the log records on the secondary. The user client threads probe the locks to see if it can read the row at the current time. Because the fundamental purpose of the secondary server is to provide increased availability, we can not let the client threads raise locks when running on the secondary. That would cause the recovery threads to block which would defeat the main purpose of the secondary. As an example, if the HDR secondary were running in SYNC mode and we placed a lock on a table on the secondary, then we would not be able to process the transaction on the primary and thus would cause the commit to hang. That's why clients running on the secondary use optimistic concurrency to detect collisions on rows. I'm sure that you realize that the technique that you're using is not the optimum way to get the next key as it results in serialization on the table lock, which means that you can only process one transaction at a time. It would be better to use either a serial column, or a sequence generator. That way you would achieve maximum parallelism. But that's not the way your application currently works. I believe that you have an open PMR with IBM (45986,228,631). I also think that we are currently working on a solution to the issue that you have raised. I believe that we are having a conference tomorrow morning with your support folks to help us address this issue and am certain that we can get a good solution in place. > Hello all. > > I recently had an issue with a programmer on our site, regarding > concurrency control and an SDS environment. The problem is, that he > had a little table that was used to control an order sequence number. > No serial column or sequence were used. It is a normal table and the > sequence column is an integer. The table is from a local ERP package, > and the program is a customer´s customization. > > Here follows some more information. This is an example on the table´s > data (let´s call it order_seq): > > company     user         seq > 01                user1        10 > 01                user2        10 > 01                user3        10 > > 02                user1        153 > 02                user5        153 > > seq - is the column that contains the order sequence number, and in > this particular customer case, no matter what the user is, the order > will be the same for the company column. Other ERP customers may not > use the same approach but for this particular customer, that is the > behaviour. So the ERP developers defined company and user as the > primary key, but for us, company is what matters. > > So our developer had in his customized code, something like the code > that follows, in order to increment the seq column: > > begin work; > > lock table order_seq in exclusive mode;     *** Here the programmer > assumed no concurrency issues > > if (not ok) then >    abort the program > end if > > select max(seq) into v_seq from order_seq where company = '01'; > > .... do some code ... > >  let v_seq = v_seq + 1; > > update order_seq set seq = v_seq where company = '01';> > ... do some other code ... > > if (ok) then >   commit work; > else >   rollback work; > end if; > > Without discussing the quality of the code, since there are better > ways of doing it, this code worked well for years. About 10 months > ago, we configured an SDS environment using the load balance > capabilities (knowing in advance its limitations). > > But what happened? When this code ran on the secondary node, and it > reached the LOCK TABLE statement, no error or warning messages were > generated, but IDS did not really locked anything. We debugged the > program and saw no sign of locks on the primary node, nor on the > secondary. However, when this code ran on the primary, the lock was in > place! > > When the programmer complained about two orders with the same number, > I immediatelly tested the LOCK TABLE on dbaccess on the secondary > node, and to my surprise, it says that the table is locked, when in > fact no locks were placed at all: > > begin work; > lock table order_seq in exclusive mode; > > ... > Table locked. > > *** But no locks on syslocks and onstat -k. > > So, I opened a case at IBM, and the response until now is that the > behaviour is expected since the LOCK TABLE statement is 'ignored' on > the secondary nodes, and this was a design decision. > > This answer is questionable, and the issue is very dangerous, at least > in my point of view! Follows some of the arguments one can pose: > > 1-There is no documentation on this behaviour, at least at infocenter. > IBM support said they are going to correct the documentation now! > 2-Shouldn´t the engine be a little more verbose about something we > think it is doing, and in fact it is not? Some kind of error message > or warning that the application receive and can be aware of it (the > application programmer might correct the code, or at least, this > program can be moved to run only the primary node)? So the load > balance becomes questionable here? > 3-How can dbaccess (or IDS engine) say that the table is locked, when > in fact it is not? > 4-Can SDS be used on a load balance environment without any risk (as > other manufacturers claim their product can), since one´s company can > be using a product without access to the source code? Imagine how much > time we have to wait for the ERP developers guarantee that their code > runs on an SDS environment correctly! > > I am posting this here, to share the issue with everyone, and to ask > if anyone else has problems close to this one, that we might be aware > of. I know of one other 'big' limitation regarding temporary tables > and transaction logs, but this one is at least very well documented! > But I also think that the LOCK TABLE is much more dangerous in terms > of stability and consistency. > > As most of you, I use Informix products for a long time, I like them a > lot, I fight for them almost any time, but I feel that when decisions > are questionable we also have to talk about it! > > Thank you!