I have a client that is using transactional replication to offload resource intensive processing from production to a standalone reporting server. Everything works flawlessly until one day during a routine application upgrade SQL Server throws the following error:
Msg 650, Level 16, State 1, Procedure sp_MSfixup_base_columns, Line 40
You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels
This error occurred when the application tries to alter a published table column. I was puzzled by the error message primarily because the production database isolation is READ COMMITTED. I’d never seen this error before as a DBA, so like all the smart people out there, I Googled it. I came across a blog post on TechNet by Claudia Silva, dated August 8, 2011.
Why is Microsoft using the READPAST hint?
Per Claudia, “the READPAST hint is used in internal stored procedures [to] resolve issues within replication. ”
So, is there a solution/workaround?
The workaround is to use either:
– The READ COMMITTED isolation level with the READ_COMMITTED_SNAPSHOT database option set to ON
– The SNAPSHOT isolation level
Did this resolve the issue?
First and foremost, I wasn’t going to test this in production. I quickly spun up a test environment so that I can have a proof of concept for the client. Unfortunately, I got the same error after setting the READ_COMMITTED_SNAPSHOT database option to ON. I was even more puzzled; the problem remained unresolved. I started to contact my troubleshooting neurons, and one of them replied, “DBA dude, check to make sure that the application is not changing the isolation level during updates.” I quickly launch SQL Server Profiler and started a simple trace using one of the default traces. I ask the client to reapply the update and immediately noticed that the application issued the following T-SQL.
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
That was the culprit. However, I don’t have access to change the application code to use READ COMMITTED isolation level and furthermore, suggesting such a change to the R&D team could take weeks to implement if approved. I need this issue resolved yesterday.
Out of curiosity, I started a trace and noticed that there was a DDL trigger in the publication database aptly named tr_MStran_altertable. Whenever there is an ALTER TABLE transaction, the trigger gets fired. Here is the trigger definition.
create trigger [tr_MStran_altertable] on database for ALTER_TABLE as
set ANSI_NULLS ON
set ANSI_PADDING ON
set ANSI_WARNINGS ON
set ARITHABORT ON
set CONCAT_NULL_YIELDS_NULL ON
set NUMERIC_ROUNDABORT OFF
set QUOTED_IDENTIFIER ON
declare @EventData xml
exec sys.sp_MStran_ddlrepl @EventData, 1
To resolved the issue, I modified the trigger and added
SET TRANSACTION ISOLATION LEVEL READ COMMITTED. However, I got the following error:
Msg 21598, Level 16, State 1, Procedure sp_MStran_ddlrepl, Line 60
Modifying DDL triggers created by replication is disallowed since these are required to track DDL changes.
Msg 3609, Level 16, State 2, Procedure tr_MStran_altertable, Line 2
The transaction ended in the trigger. The batch has been aborted.
To workaround this error I dropped the trigger and recreated it. To test the above change, I asked the client to run the updates. The application was able to modify the table, and the changes were replicated successfully to the subscriber. I was in awe!
Use this suggestion at your own risk. Microsoft does not support this.
Are there any other options?
- Upgrade to a later version of SQL Server. I tested this with SQL Server 2016 SP1 and the updates ran successfully. Either Microsoft removed the READPAST hint from the stored procedure or the internals of Replication changed
- Remove the affected articles from replication. The problem with this is that it will invalidate the current snapshot. Furthermore, you may not have any insight into what tables will be affected by the update.
- Break and recreate replication. Who wants to do that?
- Claudia’s TechNet blog post: https://blogs.technet.microsoft.com/claudia_silva/2011/08/08/replication-error-you-can-only-specify-the-readpast-lock-in-the-read-committed-or-repeatable-read-isolation-levels-generated-when-altering-published-table-columns