Replication: Error "You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels" generated when altering published table columns

  • Article

Problem:

 

Error "You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels" generated when altering published table columns in a transactional replication.

Cause:

 

Unable to alter columns on Published database if using any isolation level besides the READ COMMITTED or REPEATABLE READ isolation level.

READPAST hint is not compatible with READ UNCOMMITTED isolation level, but only with READ COMMITTED and REPEATABLE READ isolation levels.

READPAST hint is used in internal stored procedures in order to resolve issues within replication.

Solution/Workaround:

 

Other options (other isolations levels) that reduce locking:

In SQL Server, you can also minimize locking contention while protecting transactions from dirty reads of uncommitted data modifications
using either:

  • The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set to ON.
  • The SNAPSHOT isolation level.

https://msdn.microsoft.com/en-us/library/ms173763.aspx

An alternative would be to create a stored procedure that performs the alter statement, in which we specify the isolation level necessary, and call that stored procedure when the alterations to the columns is needed.