Parent Update locks Child Insert

Discussion:

(too old to reply)

Zack Cao

2010-11-19 17:06:01 UTC

We have encountered an issue where updating a non-pk column on a row
in parent locks insert action on child which references the row just
updated in the parent.
My database(Sql Anywhere 9.0.2 EBF 3951) schema and initial value is:

CREATE TABLE "DBA"."parent" (
"ID" integer NOT NULL DEFAULT autoincrement,
"ColumnOne" integer NOT NULL,
PRIMARY KEY ( "ID" )
);
CREATE TABLE "DBA"."child" (
"ID" integer NOT NULL DEFAULT autoincrement,
"ParentID" integer NOT NULL,
"ColumnOne" integer NOT NULL,
PRIMARY KEY ( "ID" )
);
ALTER TABLE "DBA"."child" ADD NOT NULL FOREIGN KEY
"parent" ( "ParentID" ) REFERENCES "DBA"."parent" ( "ID" );

insert into parent values(1,1);
insert into child values(1,1,1);
commit;

And then: Open Interactive SQL Window 1, isolation level is default 0,
run following but don’t commit yet:
update parent set ColumnOne = 11 where Id = 1;

Open Interactive SQL Window 2, isolation level is default 0, run
following and it will be blocked forever:
insert into child values(default,1,2)

But, if we run Update on Window 2, then it works:
Update chilid set ColumnOne = 11 where Id = 1;

I don’t know why it behaves like that way and keeping transaction
short is not an option for us since Window 1 in production is actually
a Mobilink client downloading large amount of data updating parent
rows, and Window 2 is our application adding data to child. It this
behavior is as designed or not changeable through some DB
configuration, then my application cannot work when mobilink client
has a large download stream.
I’ve tried disable/enable cascade update/delete, check only on a
commit on Fk, none of them seems working.
I then tried Sql Anywhere 11.0.1 EBF2472 with exact same options as
above, same issue. And then I tried:
SET OPTION PUBLIC.allow_snapshot_isolation = 'On';
SET TEMPORARY OPTION isolation_level = 'snapshot' on Window 2
and it still doesn’t work!

Lastly, I tried on Oracle, it just simply worked. I’m puzzled.

Any reply is highly appreciated.

Thanks,
Zack

Glenn Paulley

2010-11-24 01:25:15 UTC

Permalink

Post by Zack Cao
We have encountered an issue where updating a non-pk column on a row
inparentlocks insert action onchildwhich references the row just
updated in theparent.
CREATE TABLE "DBA"."parent" (
"ID" integer NOT NULL DEFAULT autoincrement,
"ColumnOne" integer NOT NULL,
PRIMARY KEY ( "ID" )
);
CREATE TABLE "DBA"."child" (
"ID" integer NOT NULL DEFAULT autoincrement,
"ParentID" integer NOT NULL,
"ColumnOne" integer NOT NULL,
PRIMARY KEY ( "ID" )
);
ALTER TABLE "DBA"."child" ADD NOT NULL FOREIGN KEY
"parent" ( "ParentID" ) REFERENCES "DBA"."parent" ( "ID" );
insert intoparentvalues(1,1);
insert intochildvalues(1,1,1);
commit;
And then: Open InteractiveSQLWindow 1, isolation level is default 0,
updateparentset ColumnOne = 11 where Id = 1;
Open InteractiveSQLWindow 2, isolation level is default 0, run
insert intochildvalues(default,1,2)
Update chilid set ColumnOne = 11 where Id = 1;
I don’t know why it behaves like that way and keeping transaction
short is not an option for us since Window 1 in production is actually
a Mobilink client downloading large amount of data updatingparent
rows, and Window 2 is our application adding data tochild. It this
behavior is as designed or not changeable through some DB
configuration, then my application cannot work when mobilink client
has a large download stream.
I’ve tried disable/enable cascade update/delete, check only on a
commit on Fk, none of them seems working.
I then triedSqlAnywhere11.0.1 EBF2472 with exact same options as
SET OPTION PUBLIC.allow_snapshot_isolation = 'On';
SET TEMPORARY OPTION isolation_level = 'snapshot' on Window 2
and it still doesn’t work!
Lastly, I tried on Oracle, it just simply worked. I’m puzzled.
Any reply is highly appreciated.
Thanks,
Zack

In releases of SQL Anywhere up to and including Version 11, the server
did not distinguish between the update of a PK column and the update
of a non-PK column on the primary row. Because of this, the
uncommitted update of the "parent" row would cause the insert of the
child row to block - the coarse locking would assume worst-case
behaviour that the PK value of the parent row had been modified.

With snapshot isolation in SQL Anywhere, updates are handled by row
locks just as with other ANSI isolation levels. This is so locking
conflicts are detected/handled by the application at update time,
rather (as with Oracle) than at COMMIT time, where a conflicting
transaction may have a COMMIT statement get a failure and be forced to
ROLLBACK the entire transaction. Again, because of the coarse-grained
locking in SQL Anywhere, your test with snapshot isolation yielded the
same result.

In SQL Anywhere version 12, the server now has separate lock classes
for PK and non-PK columns, eliminating this problem. With a version 12
server your test example works as you expect it to.

HTH

Glenn

Zack Cao

2010-11-24 14:11:44 UTC

Permalink

In releases ofSQLAnywhereup to and including Version 11, the server
did not distinguish between the update of a PK column and the update
of a non-PK column on the primary row. Because of this, the
uncommitted update of the "parent" row would cause the insert of thechildrow to block - the coarse locking would assume worst-case
behaviour that the PK value of theparentrow had been modified.
With snapshot isolation inSQLAnywhere, updates are handled by row
locks just as with other ANSI isolation levels. This is so locking
conflicts are detected/handled by the application at update time,
rather (as with Oracle) than at COMMIT time, where a conflicting
transaction may have a COMMIT statement get a failure and be forced to
ROLLBACK the entire transaction. Again, because of the coarse-grained
locking inSQLAnywhere, your test with snapshot isolation yielded the
same result.
InSQLAnywhereversion 12, the server now has separate lock classes
for PK and non-PK columns, eliminating this problem. With a version 12
server your test example works as you expect it to.
HTH
Glenn

Thanks Glenn, that's a clear answer and I'll give it a try in version
12.
Really appreciate the clarification.
Zack

Glenn Paulley

2010-11-25 01:29:17 UTC

Permalink

You're quite welcome, feel free to post any followup questions.

Glenn

Post by Zack Cao

In releases ofSQLAnywhereup to and including Version 11, the server
did not distinguish between the update of a PK column and the update
of a non-PK column on the primary row. Because of this, the
uncommitted update of the "parent" row would cause the insert of thechildrow to block - the coarse locking would assume worst-case
behaviour that the PK value of theparentrow had been modified.
With snapshot isolation inSQLAnywhere, updates are handled by row
locks just as with other ANSI isolation levels. This is so locking
conflicts are detected/handled by the application at update time,
rather (as with Oracle) than at COMMIT time, where a conflicting
transaction may have a COMMIT statement get a failure and be forced to
ROLLBACK the entire transaction. Again, because of the coarse-grained
locking inSQLAnywhere, your test with snapshot isolation yielded the
same result.
InSQLAnywhereversion 12, the server now has separate lock classes
for PK and non-PK columns, eliminating this problem. With a version 12
server your test example works as you expect it to.
HTH
Glenn

Thanks Glenn, that's a clear answer and I'll give it a try in version
12.
Really appreciate the clarification.
Zack

Zack Cao

2010-11-25 21:01:08 UTC

Permalink

Post by Glenn Paulley
You're quite welcome, feel free to post any followup questions.
Glenn

Post by Zack Cao

In releases ofSQLAnywhereup to and including Version 11, the server
did not distinguish between the update of a PK column and the update
of a non-PK column on the primary row. Because of this, the
uncommitted update of the "parent" row would cause the insert of thechildrow to block - the coarse locking would assume worst-case
behaviour that the PK value of theparentrow had been modified.
With snapshot isolation inSQLAnywhere, updates are handled by row
locks just as with other ANSI isolation levels. This is so locking
conflicts are detected/handled by the application at update time,
rather (as with Oracle) than at COMMIT time, where a conflicting
transaction may have a COMMIT statement get a failure and be forced to
ROLLBACK the entire transaction. Again, because of the coarse-grained
locking inSQLAnywhere, your test with snapshot isolation yielded the
same result.
InSQLAnywhereversion 12, the server now has separate lock classes
for PK and non-PK columns, eliminating this problem. With a version 12
server your test example works as you expect it to.
HTH
Glenn

Thanks Glenn, that's a clear answer and I'll give it a try in version
12.
Really appreciate the clarification.
Zack

Hi, Glenn
I've tried in version 12 and it worked just as you said.

Thanks,
Zack