Publication Changes - exclusive access to all tables

[bgreiman]

We are using v12.01.3554. We are using both DBREMOTE and MobiLink in our setup. We occassionally have to add new tables to our publications. It seems that we always get record locks when applying these if all users are not logged out of the database.

I don't remember this being an issue in prior versions. We could make changes as long as table we are adding to publication was not already in use.

Does anyone have any insight on this behaviour?

Thanks, Brian

[regdomaratzki]

According to the v12.0.1 documentation, in order to execute alter publication, you only require exclusive access to the tables referenced in the publication. This doesn't appear to be true anymore. Assume the admin table is a member of the p2 pubication.

Connection 1 :

update admin set data='new'

Connection 2 :

create table t1 ( pkey integer primary key, c1 integer );
alter publication p1 add table t1;

Connection 2 is blocked until Connection 1 commits.

I'll look into this. The end result will likely be a doc change BTW, but I can likely highlight what we did that caused this change in behaviour.

You mentioned that you didn't see this in "prior versions". Which versions were those?

[bgreiman]

We migrated from version 10 and (9, 6 prior to that).

If I have a publication with 10 tables in it and I want to add another table, I can alter if anyone of the 10 tables in the publication is already in use?

[regdomaratzki]

In v12, you now need exclusive access to all the tables in the publication, as well as all the tables referenced in the command when executing an ALTER PUBLICATION command.

[VolkerBarth]

So this does affect both publications for MobiLink and SQL Remote (as they use the same ALTER PUBLICATION statement)? - I'm asking because the START SYNCHRONIZATION SCHEMA CHANGE command does only relate to MobiLink.

[regdomaratzki]

The change was to the ALTER PUBLICATION statement and affects all publications, regardless of how (or even whether) they are used.

[bgreiman]

Reg - Thanks for the clarification.

[regdomaratzki]

Thanks for pointing this out Brian.

The behaviour change was introduced in v12 when we added the START/STOP SYNCHRONIZATION SCHEMA CHANGE commands, which allowed you to make schema changes to a SQL Anywhere MobiLink client without the need to have just completed a successful synchronization.

I've added a comment to the documentation for ALTER PUBLICATION on DocCommentXchange, and the documentation for the next major version should reflect the additional locks needed to perform the ALTER PUBLICATION command.