CODEWITHSUNDEEP
sqlsql-serverreplicationalter-tableisolation-level
vk_muse
vk_muse

Reputation: 614

SQL Server 2008 - adding a column to a replicated table fails

We have scenario:

Stored procedure fails with error:

You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels

Questions:

UPDATE: I believe this is very common problem so I'm wonder why there is no good explanations why replication causes this issue

Upvotes: 2

Views: 2062

Answers (3)

Brandon Williams
Brandon Williams

Reputation: 3755

Are you sure isolation level is set to READ COMMITTED?

I've seen this error when isolation is set to serializable and you use ALTER TABLE on a table published for replication. This is because some replication stored procedures use the READPAST hint to avoid blocking which can only be used in READ COMMITTED or REPEATABLE READ isolation levels.

If you are sure that the isolation level is set to READ COMMITTED, then I would recommend contacting Microsoft PSS on this one as it should not be happening. They will be able to assist you better.

Upvotes: 1

Diego
Diego

Reputation: 36166

You cant only specify READPAST when reading from committed data.

Reason is because readpast ignores locked rows, so when you use it, you are pretty much saying to sql server, give me everything that has not been touched by any other transaction. Example from BOL:

For example, assume table T1 contains a single integer column with the values of 1, 2, 3, 4, 5. If transaction A changes the value of 3 to 8 but has not yet committed, a SELECT * FROM T1 (READPAST) yields values 1, 2, 4, 5.

It doesnt make much sense saying that on a read uncommitted isolation level, which by default, brings back uncommitted values. Its kinda requesting two opposite things.

Upvotes: 1

broguyman
broguyman

Reputation: 1426

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
//REST OF QUERY ADD WITH (READPAST) after the table names Ex. SELECT FOO.* FROM dbo.foobar FOO WITH (READPAST)

And/or this should help you.

http://support.microsoft.com/kb/981995

Upvotes: 1

Related Questions