cancel
Showing results for 
Search instead for 
Did you mean: 

Publication Changes - exclusive access to all tables

bgreiman
Participant
2,569

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
Product and Topic Expert
Product and Topic Expert

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
Participant
0 Kudos

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
Product and Topic Expert
Product and Topic Expert

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
Contributor
0 Kudos

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
Product and Topic Expert
Product and Topic Expert

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

bgreiman
Participant
0 Kudos

Reg - Thanks for the clarification.

Accepted Solutions (0)

Answers (1)

Answers (1)

regdomaratzki
Product and Topic Expert
Product and Topic Expert

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.