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

asked 05 Nov '12, 15:39

bgreiman's gravatar image

bgreiman
400181927
accept rate: 20%

2

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?

(06 Nov '12, 09:41) Reg Domaratzki

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?

(06 Nov '12, 09:49) bgreiman
1

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.

(07 Nov '12, 10:53) Reg Domaratzki
Replies hidden

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.

(07 Nov '12, 11:46) Volker Barth
1

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

(07 Nov '12, 12:37) Reg Domaratzki

Reg - Thanks for the clarification.

(09 Nov '12, 17:26) bgreiman
showing 4 of 6 show all flat view

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.

permanent link

answered 07 Nov '12, 10:50

Reg%20Domaratzki's gravatar image

Reg Domaratzki
7.7k343118
accept rate: 37%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×78
×19

question asked: 05 Nov '12, 15:39

question was seen: 2,363 times

last updated: 09 Nov '12, 17:26