SELECT statement blocking UPDATE statement on a different table

Question

Post reply

SELECT statement blocking UPDATE statement on a different table

Viewing 12 posts - 16 through 26 (of 26 total)

You must be logged in to reply to this topic. Login to reply

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 1

Transaction isolation level?

repeatable reads would cause this.

Devendrakumar Shirbad SSC-Forever Points: 42493 More actions · Answer 2

Ninja's_RGR'us (11/29/2011)
Transaction isolation level?
repeatable reads would cause this.

How?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 3

Dev (11/29/2011)
Ninja's_RGR'us (11/29/2011)
Transaction isolation level?
repeatable reads would cause this.
How?

Google broken??

There's a link somewhere, a bug in SS is very unlikely. Could just be a dev with an opened connection. Shouldn't happen but it can.

Denesh Naidoo SSCrazy Points: 2348 More actions · Answer 4

I ran dbcc useroptions to get this - isolation level = read committed

Devendrakumar Shirbad SSC-Forever Points: 42493 More actions · Answer 5

Ninja's_RGR'us (11/29/2011)
Dev (11/29/2011)
Ninja's_RGR'us (11/29/2011)
Transaction isolation level?
repeatable reads would cause this.
How?
Google broken??
There's a link somewhere, a bug in SS is very unlikely. Could just be a dev with an opened connection. Shouldn't happen but it can.

I didn't get any of what you just said.

Per http://technet.microsoft.com/en-us/library/cc546518.aspx

In the repeatable read isolation level, SQL Server not only guarantees that dirty reads do not happen in your transaction, but it also guarantees that if you issue two DML statements against the same table with the same WHERE clause (one query could be a select and the next could be an update), both queries will return the same results. This is not entirely true because the latter queries might return more rows than the first query. A repeatable read protects against deletes and updates of accessed rows, but not against inserts that match the specific WHERE clause, which is the phantom rows concurrency problem. Note that phantom rows might also occur if you use aggregate functions, although it is not as easy to detect.
The first query might, for example, include the sum over four rows, while the second query includes the sum over five rows. One important thing to note is that, because the repeatable read isolation level is achieved using blocking of the other transaction, the use of this isolation level greatly increases the number of locks held for the duration of the transaction. This, in turn, can cause serious performance problems due to blocked transactions. It also greatly increases the risk for deadlocks. A deadlock is encountered if different connections are waiting for locks held by each other. In the repeatable read isolation level, shared locks are acquired for read operations and are also held for the duration of the transaction, not allowing changes to the data by other transactions.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 6

Denesh Naidoo (11/29/2011)
I ran dbcc useroptions to get this - isolation level = read committed

This is for the current connection, not the 2 external ones.

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 7

Trying to think outside the box.

Calculated fieds with functions?

Schema bound views?

Long open tran?

Denesh Naidoo SSCrazy Points: 2348 More actions · Answer 8

I've used the DMV sys.dm_exec_sessions and they both (the SELECT and UPATE) have an isolation level of Repeatable

It seems this is where I need to start investigating

NickDBA Hall of Fame Points: 3684 More actions · Answer 9

Does the "nolock" table hint you're using in the select statement negate the isolation level in this case?

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 10

NickBalaam (11/29/2011)
Does the "nolock" table hint you're using in the select statement negate the isolation level in this case?

No idea what takes precedence here. "Should", "might" make sens to see nolock take over for that one but I don't know how it is implemented.

The other point is we don't know the whole statement so we might be seeing only part of the batch or the statement.

Denesh Naidoo SSCrazy Points: 2348 More actions · Answer 11

Hi All,

The issue I experienced has been resolved. The afternoon of the day I posted this topic, there was an issue on that server that required my colleague to run the following commands. Since the cache has been cleared I've not seen that behaviour. I'm not sure how to explain this

DBCC FREESESSIONCACHE

DBCC FREESYSTEMCACHE ('ALL')

DBCC FREEPROCCACHE

Ninja's_RGR'us SSC Guru Points: 294069 More actions · Answer 12

1 or more of the "bad" plans have been dropped and rebuilt a different way.

This is a accidental fix. I'd be very surprised to NOT see the issue come back.