RE: Table Locking

From: Pablo (Pablo_at_discussions.microsoft.com)
Date: 07/27/04 

Date: Tue, 27 Jul 2004 13:41:03 -0700

TAB/Sch-S has no effect on reading or writing in other transactions.

"Schema stability (Sch-S) locks are used when compiling queries. Schema stability (Sch-S) locks do not block any transactional locks, including exclusive (X) locks. Therefore, other transactions can continue to run while a query is being compiled, including transactions with exclusive (X) locks on a table. However, DDL operations cannot be performed on the table."...BOL

Can you describe effect of blocking (timeout in other transactions)?

Regards,
Pablo

"Jay" wrote:

> Hi Pablo,
>
> Lock type - TAB
> Mode Sch-S
>
> When SP is running, can not access those 4 tables. I tested so many times.
>
> "Pablo" wrote:
>
> > Hi ,
> > Can you precise what mode,type o locking did u have?
> > if You change isolation level by
> > SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED in SP
> > You don't have to change locking for each table (NOLOCK) because it's redundant.
> > Did you test that rest transactions are holded by this report?
> >
> > Regards,
> > Pablo
> >
> >
> > "Jay" wrote:
> >
> > > I have a stored procedure, which is locking table. There are four tables involved in this SP doing straight join to get data for one of our report. In this SP just select statement then getting data. I added nolo*** for all the table and also placed in SP SET NOCOUNT ON and SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED. Takes about 10 minutes to run SP. When I look through Current Activity and Lockes/Object table, I see that all these 4 tables got locked (Table level Locks). So all other transactions are holding because of this. There are about 1 million records are in the main table.
> > >
> > > Can somebody please help me in this?
> > >
> > > Thanks
> > >
> > > Jacob

Quantcast