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

On Nov 19, 1:06*pm, Zack Cao <zack.... (AT) gmail (DOT) com> wrote:
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

On Nov 23, 8:25*pm, Glenn Paulley <paul... (AT) ianywhere (DOT) com> wrote:
Thanks Glenn, that's a clear answer and I'll give it a try in version
12.
Really appreciate the clarification.
Zack

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

Glenn

On Nov 24, 9:11*am, Zack Cao <zack.... (AT) gmail (DOT) com> wrote:

On Nov 24, 8:29*pm, Glenn Paulley <paul... (AT) ianywhere (DOT) com> wrote:
Hi, Glenn
I've tried in version 12 and it worked just as you said.

Thanks,
Zack