CODEWITHSUNDEEP
sql-serversql-server-2008transactions
Shrikey
Shrikey

Reputation: 878

Set Transaction Isolation level serializable blocks others transactions?

I am preparing for MCTS 70 - 433 and while reading the dumps I found this question.

"You are tasked to analyze blocking behavior of the following query

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

WITH customers
     AS (SELECT *
         FROM   customer),
     salestotal
     AS (SELECT customerid,
                sum(ordertotal) AS Allordertotal
         FROM   salesorder)
SELECT customerid,
       allordertotal
FROM   salestotal
WHERE  allordertotal > 10000.00

You need to determine if other queries that are using the Customer table will be blocked by this query. You also need to determine if this query will be blocked by the other queries that use the customer table.

A. The other queries will be blocked by the user. This query will be blocked by the other queries.

B. The other queries will be blocked by the user. This query will not be blocked by the other queries.

C. The other queries will not be blocked by the user. This query will be blocked by the other queries.

D. The other queries will not be blocked by the user. This query will not be blocked by the other queries."

The correct answer is given as D.

But when serializable transaction level is used, it issues a lock and stop other transactions rite.??

Please correct me if I am wrong.

Upvotes: 1

Views: 1503

Answers (1)

Mark Brackett
Mark Brackett

Reputation: 85625

It's a trick question. The query only runs on the SalesOrder table. Even though Customers is used in the CTE definition, the CTE is never accessed so SQL Server will never take a lock.

EDIT: If the CTE was used, there's not enough information to answer the question. The given answers seem to assume a table lock - but SQL Server will use row and range locks in most cases. So the only way to analyze the blocking behavior is by reviewing both queries involved.

In general, for SERIALIZABLE, any query that would modify the resultset would be blocked. This includes introducing a non repeatable read (eg., an UPDATE to a SELECTed row), and introducing a phantom read (eg., an INSERT that would satisfy the WHERE clause). The latter part is the additional guarantee that SERIALIZABLE gives over REPEATABLE READ.

Upvotes: 2

Related Questions