referential constraints / error -691 -107 : back to referential integrity basics

[BeGooden-IT Vercelletto]

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

[Jonathan Leffler]

On Sat, Jan 28, 2012 at 07:50, BeGooden-IT Vercelletto <begoo...@gmail.com> wrote:

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

So the change in session A is uncommitted.

 

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.

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.

 

In my mind ( I can be wrong though), I do not expect this error

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
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?

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...@gmail.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."

[BeGooden-IT Vercelletto]

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.

> Jonathan Leffler <jonathan.leff...@gmail.com>  #include <disclaimer.h>
> Guardian of DBD::Informix - v2011.0612 -http://dbi.perl.org

[Jonathan Leffler]

On Sat, Jan 28, 2012 at 11:32, BeGooden-IT Vercelletto <begoo...@gmail.com> wrote:

Jonathan,

I know that the commit work is missing :-). I left this transaction

uncommitted 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 no-one can insert

a row in the detail table.

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
row lock on table A for value 100.

 

OK - I didn't read carefully enough.
 

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.

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,
and not on the rows contents...
therefore my concern, and this scenario was possible more than 10
years ago.

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...@gmail.com>  #include <disclaimer.h>
Guardian of DBD::Informix - v2011.0612 - http://dbi.perl.org

[BeGooden-IT Vercelletto]

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...@gmail.com> wrote:
> On Sat, Jan 28, 2012 at 11:32, BeGooden-IT Vercelletto <

> Jonathan Leffler <jonathan.leff...@gmail.com>  #include <disclaimer.h>
> Guardian of DBD::Informix - v2011.0612 -http://dbi.perl.org

[Fernando Nunes]

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...@gmail.com> wrote:

On Sat, Jan 28, 2012 at 07:50, BeGooden-IT Vercelletto <begoo...@gmail.com> wrote:

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

So the change in session A is uncommitted.

 

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.

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.

 

In my mind ( I can be wrong though), I do not expect this error

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
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?

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...@gmail.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."

_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

--
Fernando Nunes
Portugal

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

[Art Kagel]

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 <domus...@gmail.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...@gmail.com> wrote:

On Sat, Jan 28, 2012 at 07:50, BeGooden-IT Vercelletto <begoo...@gmail.com> wrote:

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

So the change in session A is uncommitted.

 

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.

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.

 

In my mind ( I can be wrong though), I do not expect this error

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
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?

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...@gmail.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."

_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list

[BeGooden-IT Vercelletto]

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

[Fernando Nunes]

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.

_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list