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... (AT) gmail (DOT) 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.