ALTER TABLE Fails on Replicated Tables With Isolation Level SERIALIZABLE or READ UNCOMMITTED on SQL2012 and Earlier

It’s 4am in the morning and my phone starts ringing.

A blury eyed, me picks it up to hear the voice of one of our application guys at the other end saying something about an upgrade he was trying to carry out failing.

So it turns out that he was trying to run an ALTER TABLE statement to add a new column but it was failing out with the following error,

Msg 650, Level 16, State 1, Procedure sp_MSreinit_article, Line 66 [Batch Start Line 5]
You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels.

Ok, so what’s your isolation level?

The script sets it to serializable at the beginning.

In that case you can’t use READPAST hints.  (me thinking, that’s job done and I can get back to bed)

But I’m not using any READPAST hints.

Ok, that’s strange.  So we dig into triggers and the procs that they’re calling in but still can’t find that READPAST.  Then I take another look at the error message and spot something that I hadn’t seen before, (look, I’d only just been dragged out of bed remember).

Msg 650, Level 16, State 1, Procedure sp_MSreinit_article, Line 66

sp_MSreinit_article, that’s a system stored proc that deals with replication.  Let’s check if the database has replication on the table that we’re trying to alter, the answer to that was a yes.

So is there something going on in that proc?  I struggled to find the proc definition (I was still struggling with the effects of my rude awakening so perhaps it is there somewhere but I just wasn’t seeing it) so I decided to look at a trace to see what it’s running and guess what I saw…

SELECT @artid = artid
FROM sysarticles with (READPAST)
WHERE name = @article
AND pubid = @pubid

So there it is, our READPAST!

The quick and dirty fix was to change the isolation level to READ COMMITTED.  It doesn’t look like there’s any other way around it and although there are a few mentions of this issue out on the net, the explanations are all a little fuzzy.

In this particular instance, the SQL Server was a 2012 server.

I’ve managed to recreate this on SQL2008R2 but it looks like the issue’s fixed in SQL2016 (I don’t have a SQL2014 instance handy to test on, if anyone out there want’s to test it out on SQL2014, please feel free to let me know the outcome). 

A trace on SQL2016 shows similar code running but without READPAST

SELECT @artid = artid 
FROM sysarticles 
WHERE name = @article
AND pubid = @pubid

Incidentally, since READPAST is also not allowed with READ UNCOMMITTED, ALTER TABLE statements also fail with that isolation level.