table blocking question

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Pingx - 18 Mar 2008 01:36 GMT

In SQL server, does the update block select statement?

Thank.

Pingx

Reply to this Message

Linchi Shea - 18 Mar 2008 02:29 GMT

An update does not necessarily block a select. An update may block a select
if the update is on a piece of data that the select is trying to read from.
In that case, it depends on the transaction isolation level. For the default
transaction isolation level (read committed), the update blocks the select if
they request incompatible locks on the same resource. SQL2005 does support
isolation levels (e.g. read uncommitted and snapshot) with which updates
won't block selects and selects won't block updates.

Read TRANSACTION ISOLATION LEVELS in SQL Server Books Online.

Linchi

> In SQL server, does the update block select statement?
>
> Thank.
>
> Pingx

Reply to this Message

Pingx - 18 Mar 2008 07:17 GMT

Thanks for the info.
I know how to set the isolation level for a session.
Can you tell me how to set it at instance level or database level?
Also, how do you set isolation level in T-SQL?

Pingx

> An update does not necessarily block a select. An update may block a select
> if the update is on a piece of data that the select is trying to read from.
[quoted text clipped - 13 lines]
> >
> > Pingx

Reply to this Message

Tibor Karaszi - 18 Mar 2008 08:57 GMT

> Can you tell me how to set it at instance level or database level?

No can do.

> Also, how do you set isolation level in T-SQL?

Read about below command:
SET TRANSACTION ISOLATION LEVEL

Signature

Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

> Thanks for the info.
> I know how to set the isolation level for a session.
[quoted text clipped - 20 lines]
>> >
>> > Pingx

Reply to this Message