SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

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

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.

 

SQL Undercover

David Fowler and Adrian Buckman, two database nerds who love nothing more than to spend their time, reading about, researching and sharing all things SQL Server. David is a DBA with over 15 years production experience of SQL Server, from version 6.5 through to 2016. He has worked in a number of different settings and is currently the technical lead at one of the largest software companies in the UK. After working in the motor trade for over 11 years Adrian decided to give it all up to persue a dream of working in I.T. Adrian has over 3 years of experience working with SQL server and loves all things SQL, Adrian currently works as a Database Administrator for one of the UK’s Largest Software Companies.

Comments

Leave a comment on the original post [sqlundercover.com, opens in a new window]

Loading comments...