MS SQL Server - Table locks from view

Author

Table locks from view

bsg075@gmail.com

2006-02-10, 3:23 am

I am working with a system that is encountering a significant number of
blocked processes, and occassional deadlocks. I believe I have a
solution, but I first need to confirm something about locking in SQL
Server 2000.

Given an view with 5 table joins, and a SELECT statement that only
references 2 of the tables in the view, will all tables joined in the
view be locked for the duration of the SELECT?

For example:

CREATE VIEW base AS
SELECT tbl1.a, tbl2.b, tbl3.c, tbl4.d, tbl5.e
FROM tbl1 INNER JOIN tbl2 on tbl2.id = tbl1.id
INNER JOIN tbl3 on tbl3.id = tbl2.id
INNER JOIN tbl4 on tbl4.id = tbl3.id
INNER JOIN tbl5 on tbl5.id = tbl4.id
go

SELECT a,b FROM base where b < 10

If the base view takes 60 seconds to complete for whatever reason, all
tables in the view will be locked for the duration of the SELECT
statement, even though the SELECT only references fields from two of
the five tables joined in the view. Correct?

In the system the base view is far more complex, and in production runs
an average of 60-80 seconds, blocking many other user processes.

Uri Dimant

2006-02-10, 3:23 am

SELECT statement by definition does not block users from reading the data.
So it depends on how do you run the statement ? Did you include BEGIN
TRAN....COMMIT TRAN?

You can add NOLOCK hint to see what is going on

<bsg075@gmail.com> wrote in message
news:1139548096.530897.62120@g14g2000cwa.googlegroups.com...
>I am working with a system that is encountering a significant number of
> blocked processes, and occassional deadlocks. I believe I have a
> solution, but I first need to confirm something about locking in SQL
> Server 2000.
>
> Given an view with 5 table joins, and a SELECT statement that only
> references 2 of the tables in the view, will all tables joined in the
> view be locked for the duration of the SELECT?
>
> For example:
>
> CREATE VIEW base AS
> SELECT tbl1.a, tbl2.b, tbl3.c, tbl4.d, tbl5.e
> FROM tbl1 INNER JOIN tbl2 on tbl2.id = tbl1.id
> INNER JOIN tbl3 on tbl3.id = tbl2.id
> INNER JOIN tbl4 on tbl4.id = tbl3.id
> INNER JOIN tbl5 on tbl5.id = tbl4.id
> go
>
> SELECT a,b FROM base where b < 10
>
> If the base view takes 60 seconds to complete for whatever reason, all
> tables in the view will be locked for the duration of the SELECT
> statement, even though the SELECT only references fields from two of
> the five tables joined in the view. Correct?
>
> In the system the base view is far more complex, and in production runs
> an average of 60-80 seconds, blocking many other user processes.
>

bsg075@gmail.com

2006-02-10, 11:23 am

The connections are from different applications, and I have not yet had
the opportunity to inspect the connection methods for transactions or
isolation levels. Assuming these calls are inside transactions, will
all the base tables in my example be held, or only those affected.

Tracing the connection activity in SQL Server, lock chains are
established where multiple processes are blocked, and the top level
lock holder is a connection executing a SELECT against this view.

Sponsored Links