Altering Articles While Users Have Table Locks

[Former Member]

Sometimes we find a need to modify publications/articles ahead of an extraction.

Naturally, there are sometimes users still in the system, since we're normally trying to make the change in the middle of a work day.

I'm curious why altering an article, say adding a WHERE clause of 1=2, would be rejected if a user has a lock on the corresponding table? By design or bug?

[Former Member · Accepted solution]

This is by design. Shared schema locks are held by "ordinary" transactions to ensure that the schema remains fixed over the life of the transaction, to ensure that (for example) the transaction can issue a ROLLBACK and the transaction's updates will be undone correctly.

So any schema modification, including altering an ARTICLE, requires an exclusive lock so as to ensure that any existing transactions can complete before the schema is modified.