January 14, 2013 at 11:45 am
Is somebody able to tell me why my Blocked Process Report is showing an INSERT statement being blocked by a SELECT statement.
Some info that may help.
* The table has just over 600k rows
* The insert is for one row using insert into table(columns)values(...) rather than a full SELECT statement for the insert.
* The blocking query only returns ~100 rows but it does do a Clustered Index Scan to get this data.
* The isolation level for both the Blocked and Blocking queries is read committed
Don't know if it makes a difference but the insert is from a java app so it will use xa transactions for the insert.
I am getting the normal waffle from the front end developers about how a SELECT should never block a UPDATE or INSERT SQL statement . This would not happen in Oracle blah blah
So I have to be clear in my understanding so I can pass this on.
Any help would be appreciated
thanks
January 14, 2013 at 12:32 pm
I don't know what your developers are smoking, but a SELECT most certainly can block an UPDATE or INSERT. Selects take shared locks. If it's doing a table scan, that's likely to be a table-level shared lock. Shared locks are compatible with other shared locks, but not exclusive locks.
To make a change (insert, update or delete), SQL requests exclusive locks. These may be at the table, page or row level. An exclusive lock is blocked by any other lock type (shared, update or exclusive) and hence it's perfectly normal for an insert to be blocked by a select.
No, it doesn't happen in Oracle, because Oracle doesn't use locks to enforce the ACID principals, it uses a form of row versioning (which SQL can do too, but it's not the default)
Have a read through at least the first section of Chapter 6 of http://www.simple-talk.com/books/sql-books/troubleshooting-sql-server-a-guide-for-the-accidental-dba/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 14, 2013 at 1:17 pm
thank you I will have a look at the article.
Would READ_COMMITTED_SNAPSHOT be row versioning in SQL?
I see their are a few articles about the possible overheads associated with this but is this something you think can be turned on to minimise the locks or is it a case by case basis whether turning this on is beneficial or not?
January 14, 2013 at 1:55 pm
UncleBoris (1/14/2013)
Would READ_COMMITTED_SNAPSHOT be row versioning in SQL?
Yes. As is snapshot isolation.
I see their are a few articles about the possible overheads associated with this but is this something you think can be turned on to minimise the locks or is it a case by case basis whether turning this on is beneficial or not?
Personally I don't just turn it on everywhere. I have used in in some systems, but after analysis and investigation.
The chapter I referenced has some info on reducing blocking. Comes down to optimising queries and adding indexes. In your case, the fact that there's a table scan suggests that there's a possibility for the query to be optimised. Optimised queries generally take fewer locks and hold them for shorter periods of time.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply