Hi Folks,

I have been submitted a challenge from a customer, which is the
following:
* we have a master table "item" with item_num as primary key
* we have a detail table "operation, with operation_num as primary
key, and item_num as foreign key, referencing
item (item_num)

session A initiates a session with
begin work;
update item set ( some item colums EXCEPT THE PRIMARY KEY ) = ( some
values )
where item_num = 100 ;
-- leave sesssion "as is" and open another session on other terminal

session B opens a new session and attempts:
insert into operation VALUES ( operation_num_value,100,more values)

==> Session B receives error
691: Missing key in referenced table for referential constraint
(PKconstraint).
107: ISAM error: record is locked.

In my mind ( I can be wrong though), I do not expect this error
because in the UPDATE statement, I have not
modified the primary key value. In opposition, I would have expected
the constraint violation
if I had modified the primary key value in the update statement.

It seems that the foreign key constraint does not refer to the primary
key index, but only on the fact that the row
is locked.

In my same mind, I think this scenario would have worked in 7.XX
versions, for the reason I stated just above,
provided the condtion that I do not modify the primary key value.

I have a simple and easy to execute test case with data available on
request by email.

I may have forgotten some referential constraint basics, my age would
allow it :-)

Any simple suggestion to achieve such a scenario?

Cheers,

Eric

On Sat, Jan 28, 2012 at 07:50, BeGooden-IT Vercelletto <
begooden.it (AT) gmail (DOT) com> wrote:




This means that there wasn't a committed value 100 that could be
referenced, so the statement should fail.

This is the most basic requirement for COMMITTED READ or higher isolation.
I'm not sure it should work under DIRTY READ isolation even - although the
value might exist at the moment, if the DR session (session B) was allowed
to commit, session A might rollback, leaving the row inserted by B not
referencing anything - a state of semantic disintegrity that referential
integrity is supposed to avoid.





This error should be expected - as much because of isolation issues as
because of referential integrity issues.

because in the UPDATE statement, I have not
Add a COMMIT to Session A. That is the best, most reliable way to deal
with it. Pretty much anything else is at best dubious.


--
Jonathan Leffler <jonathan.leffler (AT) gmail (DOT) com> #include <disclaimer.h>
Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."

Jonathan,

I know that the commit work is missing :-). I left this transaction
uncommited to simulate the real situation
happening at my customer, which is:
someone modifies the master table( but not the primary key ), goes for
lunch, and noone can insert
a row in the detail table.

I also know that when the commit will be issues, it will release the
row lock on table A for value 100.

What is really happening here is that the master table row is locked
for primary key = 100 , which is expectable,
but since the key is not modified ( ie the primary is not part of the
SET (....) = ( ...) ) list by the update statement, there is for me no
point in preventing an insert in the detail
table for the foreign key = 100. I would accept it if the update
statement modified the primary key, which would put
this value "at risk" for the match foreign key.

As far as I remember, referential constraints were based on indexes,
and not on the rows contents...
therefore my concern, and this scenario was possible more than 10
years ago.


On 28 jan, 17:48, Jonathan Leffler <jonathan.leff... (AT) gmail (DOT) com> wrote:

On Sat, Jan 28, 2012 at 11:32, BeGooden-IT Vercelletto <
begooden.it (AT) gmail (DOT) com> wrote:


Correct - don't let the slaves go to lunch! :-D

More accurately, design your systems so that long transactions like that
are not a problem.


I also know that when the commit will be issues, it will release the

OK - I didn't read carefully enough.


What is really happening here is that the master table row is locked

The DBMS is good at queries about the state of things as they are, not
about the state of things as they will be. Predicting the future is hard!

The DBMS cannot tell whether the locked row will be updated (so that the PK
value is changed) after the insert is permitted; the row is locked because
it has been changed, but until the new row value is committed, the DBMS
cannot tell whether the referential constraint will be satisfied when the
row is committed. Indeed, the row could be deleted after the update but
before the commit.

It still comes down to isolation. Until the update is committed, it is not
safe to allow newly added rows in the second table to reference the updated
row in the first table.

....But what about the rows already in the second table that reference the
row in the first table?... Good question! They're integrity will be
checked in due course - and an UPDATE or DELETE that eliminates the row in
the first table will be disallowed because of the existing references.
However, Session A could be designed to go and delete those referencing
rows from the second table, and therefore new references should not be
inserted in the second table until the value in the first is committed.


As far as I remember, referential constraints were based on indexes,

Behind the scenes, there is key-value locking on indexes - yes. But that
is mostly an implementation detail. At the conceptual level, the issue is
whether the referenced row is currently committed in the DB. If it is not,
then it is not safe to allow new references to the row.

I wonder where LAST COMMITTED isolation fits into this?




--
Jonathan Leffler <jonathan.leffler (AT) gmail (DOT) com> #include <disclaimer.h>
Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org
"Blessed are we who can laugh at ourselves, for we shall never cease to be
amused."

Hi again,

I understand the point. In fact, I have tested many of the possible
combinations and here are my findings:
When session A updates the master table ( modifying the primary key
value or not ) where Primary Key = somevalue ,
session B cannot insert a row have a foreign key having session A's
primary key value.
It will receive the ISAM error "row locked", not trying to understand
whether the PK was being altered or not

On the other hand, Session B can update the detail table, even
modifying the incriminated foreign key,
as long as other constraints are respected ( PK exists, no duplicate
value etc....)

So for the initial case I related, I think the easiest and simplest
solution is to add a SET LOCK MODE TO WAIT afewseconds
and handle the error if the statement could not be succesfully
executed, if acceptable in the program scenario.

Changing ISOLATION mode will do nothing for the INSERT statement
because session B does not try to read nor update the row,
it is trying to insert a new row.

LAST COMMITTED was a good idea, but not applicable here :-
( Nevertheless, it can work if session B is updating the row.

Nice try though!

Cheers
Eric


On 29 jan, 01:24, Jonathan Leffler <jonathan.leff... (AT) gmail (DOT) com> wrote:

I believe the OP's point was that even in case of a rollback on session A,
the record with item_num = 100 would still be there.

I'd have to test this and monitor it, but I believe the problem is that
session B will try to lock the record from item. And because there is a
lock on the row, it fails.
But I'd have to check the existing locks in item to confirm this.

In practice, session B should have a reasonable LOCK WAIT time that would
allow session A to commit.
Regards.


On Sat, Jan 28, 2012 at 4:48 PM, Jonathan Leffler <
jonathan.leffler (AT) gmail (DOT) com> wrote:


--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

Yes. Also a properly designed application will use Optimistic Locking
Protocols and not perform SELECT ... FOR UPDATE, insert, update, delete,
before waiting for the user's input but will gather all locking operations
until after the user is finished with manual data manipulations so that the
locks are instantaneously held and released minimizing the effect on
concurrency.

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 Mon, Jan 30, 2012 at 5:36 AM, Fernando Nunes <domusonline (AT) gmail (DOT) com>wrote:

Thanks folks,

I was coming to the same conclusion: SET LOCK MODE TO WAIT
aReasonableNumberOfSeconds,
and have my customer think how relevant it is to keep the master row
locked for an unlimited time.

Fernando, I checked that effectively the master table was granted a
ROW LOCK on the PK row, thus
preventing from any INSERT in the detail table for the PK value.

Appartently, the PK constraint implementation cannot discriminate
whether the PK is subject to be modified by the UPDATE statement
( appearing in the SET ( columns list) = ( values )), or not to be
modified. I was expecting a higher constraint check granularity the
mere
ROW level. Why am I prevented from updating the master row if I don't
modify the primary ?

Nevertheless, you can UPDATE on detail as long as you respect the
constraint terms...

Maybe a feature request for the insert case ? :-)
Eric

I suppose it comes down to the fact that an INSERT on the detail needs an
exclusive lock on the master (to prevent it from being changed).
And this can't happen while a lock is in place. But only more detailed
insight info would allow a definitive answer.
Regards.


On Mon, Jan 30, 2012 at 12:56 PM, BeGooden-IT Vercelletto <
begooden.it (AT) gmail (DOT) com> wrote:



--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...