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

davidfowler-42596, 2019-02-07

table

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.

 

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads