Locks Rows in Table when use View

Question

Post reply

Locks Rows in Table when use View

shifan

SSC Enthusiast

Points: 123
More actions
March 27, 2006 at 11:13 pm

#152859

I want to know when I use Views for Query, will it will lock (Read) the record of Table(s) which are participated in View or not. How can i check this in MS SQL Server 2000

regards

Shifan

shifan

Viewing 7 posts - 1 through 6 (of 6 total)

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

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 1

Yes, locks will be taken on the base table. You can see this my running the stored proc sp_lock or looking at the system table syslocks (located in the master database).

The locks will only be there for the duration of the query, so unless it's a long running query, it may be hard to catch them.

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Alistair Warburton SSC Journeyman Points: 77 More actions · Answer 2

If you want to eliminate locks for a select statment have a look at NOLOCK.

I also think making a query ReadOnly will prevent locking of the base table.

Make sure you are aware of the dirty read problem though, basically data can change under you and you wont know about it, it realy depends what you are trying to achieve

Alistair Warburton

alexia-294642 SSChasing Mays Points: 600 More actions · Answer 3

alexia-294642

SSChasing Mays

Points: 600

March 28, 2006 at 7:14 pm

#629342

try to read transaction locking in BOL...

I think the best is to use "set transaction isolation level read committed" in your query

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 4

No need to do that, read comitted is the default isolation level in SQL

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

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

shifan SSC Enthusiast Points: 123 More actions · Answer 5

I have involved in developing a system which is lot of concurrent users and for report generation purpose i want to fetch row without lock, then the records available for online data processing users without delay; i guess this will increase the performance.

can anybody tell whether i am correct in this regard

shifan

David Griffiths-273839 Say Hey Kid Points: 663 More actions · Answer 6

1. A view is nothing more than a way of packaging a SELECT statement in a convenient re-usable form. Querying a view is no different from running the equivalent SQL statement against the underlying tables.

2. The normal transaction isolation level "Read Committed" takes locks on data rows. These are transient and persist only as long as it takes to fetch the data; their impact on performance is small.

3. However, if another user is updating the table(s), you may experience a delay when attempting to read the changed data - you can't lock the rows until the other user has committed (or rolled back) his transaction. You might perceive this delay as a "performance problem".

4. You can avoid this locking delay by reading without locking - you can control this per SQL statement by using the locking hint WITH(NOLOCK), or set it as the default for your session by using SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED.

5. The danger of this is that you can now read uncommitted data. Rather than wait for another user to complete his transaction, your SELECT reads the data he is in the process of changing. This can lead to inconsistent data. If you're simply generating a report you may be able to live with the possibility of inconsistent data - it may only affect a small percentage of the data. For most purposes this is unacceptable, which is why "Read Committed" is the default.