Does SQL Server put a shared lock on all tables within a transaction?

Would table1, table2 and table3 in code below, be locked with a shared lock from start of transaction to the end of transaction Or they would only be locked for the duration of their update, or insert statements and not for the entire transaction? Default isolation level is in effect in SQL Server.

begin tran
update table1 set column1 = 100
if @.ERROR = 0
begin
declare @.stat int
set @.stat = (select stat from table2 where employeeid = 10)
insert into table3 (col1, col2) values (@.stat , 325)
if @.@.ERROR = 0
commit tran
else
rollback tran
end
else
roll back tran

The answer to your first question is no. The answer to your second question is no.|||

Here's the long answer:

To keep this long answer from becoming a book, I'm going to classify IS/S locks as "shared", IU/I locks as "Update" and IX/X locks as "Exclusive". The I-versions are basically the same, but has a few differences, and usually used prior to obtaining the non-I version to eliminate lock starvation.

I'm going to assume table1 only has 1 row in it for this.

Update table1 ... will initially request an update lock as it scans the table for the rows to update, once it's located the correct rows, it will acquire an exclusive lock on those pages and rows then release the update locks.

set @.stat=(SELECT .. will request shared locks on the table, retrieve it's results, then release it's shared locks.

insert into table3 will acquire an exlusive lock on either an existing page, or a new page if either none are available, or the available ones are already exclusively locked. (There is an additional exclusive lock placed on the particular row as well).

The exclusive locks will be held until the transaction is either commited or rolled back. This stored procedure will not block any other session from either reading table2 at any time, or inserting a new record into table3. It will however block any other session from updating it's only row until the transaction is complete.

Does SHOWCONTIG always use table S-lock?

I noticed that while running a DBCC SHOWCONTIG - this processed blocked
another process attempting to acquire an IX lock on that table. I saw that
the showcontig had an "S" lock on the table.
Is there any way to influence SQL Server into taking an IS lock on the table
and S locks on the pages?
Thanks in advance
WITH FAST Option may help.
also if a table is a heap (No Clustered Index) that will impact this
behavior. You WANT each table to have a clustered index (in General)
Cheers
Greg Jackson
PDX, OR
|||Not currently. Even using WITH FAST requires a Shared Table lock (however,
except on the largest tables any blocking should be fairly transient as the
results are returned so quickly)
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23%23LyYoZSEHA.3636@.TK2MSFTNGP09.phx.gbl...
> I noticed that while running a DBCC SHOWCONTIG - this processed blocked
> another process attempting to acquire an IX lock on that table. I saw
that
> the showcontig had an "S" lock on the table.
> Is there any way to influence SQL Server into taking an IS lock on the
table
> and S locks on the pages?
> Thanks in advance
>
|||Not true. If you only specify WITH FAST for a clustered or non-clustered
index it will acquire an IS table lock - that's the entire reason for me
adding the option in SQL Server 2000.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:uJCeJ7aSEHA.3728@.TK2MSFTNGP11.phx.gbl...
> Not currently. Even using WITH FAST requires a Shared Table lock (however,
> except on the largest tables any blocking should be fairly transient as
the
> results are returned so quickly)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
> news:%23%23LyYoZSEHA.3636@.TK2MSFTNGP09.phx.gbl...
> that
> table
>
|||That's what I thought (and I can see now that it is) however when I was
testing before posting my original answer ( I really did test!) I was using
dbcc showcontig('wildtest') with fast
where wildtest has a clustered index and it was getting blocked by an IX
table lock from a transaction I left open so I could examine the locking. I
just tried it again specifying the clustered index explicitly and no
blocking
dbcc showcontig('wildtest',1) with fast
This is what threw me off track :-)
I was under the impression that the first query on a table with a clustered
index would result in the same behaviour as the second one ?
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%231ITfVbSEHA.3812@.TK2MSFTNGP11.phx.gbl...
> Not true. If you only specify WITH FAST for a clustered or non-clustered
> index it will acquire an IS table lock - that's the entire reason for me
> adding the option in SQL Server 2000.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.[vbcol=seagreen]
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:uJCeJ7aSEHA.3728@.TK2MSFTNGP11.phx.gbl...
(however,[vbcol=seagreen]
> the
blocked[vbcol=seagreen]

>

Does SHOWCONTIG always use table S-lock?

I noticed that while running a DBCC SHOWCONTIG - this processed blocked
another process attempting to acquire an IX lock on that table. I saw that
the showcontig had an "S" lock on the table.
Is there any way to influence SQL Server into taking an IS lock on the table
and S locks on the pages?
Thanks in advanceWITH FAST Option may help.
also if a table is a heap (No Clustered Index) that will impact this
behavior. You WANT each table to have a clustered index (in General)
Cheers
Greg Jackson
PDX, OR|||Not currently. Even using WITH FAST requires a Shared Table lock (however,
except on the largest tables any blocking should be fairly transient as the
results are returned so quickly)
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23%23LyYoZSEHA.3636@.TK2MSFTNGP09.phx.gbl...
> I noticed that while running a DBCC SHOWCONTIG - this processed blocked
> another process attempting to acquire an IX lock on that table. I saw
that
> the showcontig had an "S" lock on the table.
> Is there any way to influence SQL Server into taking an IS lock on the
table
> and S locks on the pages?
> Thanks in advance
>|||Not true. If you only specify WITH FAST for a clustered or non-clustered
index it will acquire an IS table lock - that's the entire reason for me
adding the option in SQL Server 2000.
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:uJCeJ7aSEHA.3728@.TK2MSFTNGP11.phx.gbl...
> Not currently. Even using WITH FAST requires a Shared Table lock (however,
> except on the largest tables any blocking should be fairly transient as
the
> results are returned so quickly)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
> news:%23%23LyYoZSEHA.3636@.TK2MSFTNGP09.phx.gbl...
> that
> table
>|||That's what I thought (and I can see now that it is) however when I was
testing before posting my original answer ( I really did test!) I was using
dbcc showcontig('wildtest') with fast
where wildtest has a clustered index and it was getting blocked by an IX
table lock from a transaction I left open so I could examine the locking. I
just tried it again specifying the clustered index explicitly and no
blocking
dbcc showcontig('wildtest',1) with fast
This is what threw me off track :-)
I was under the impression that the first query on a table with a clustered
index would result in the same behaviour as the second one ?
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%231ITfVbSEHA.3812@.TK2MSFTNGP11.phx.gbl...
> Not true. If you only specify WITH FAST for a clustered or non-clustered
> index it will acquire an IS table lock - that's the entire reason for me
> adding the option in SQL Server 2000.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:uJCeJ7aSEHA.3728@.TK2MSFTNGP11.phx.gbl...
(however,[vbcol=seagreen]
> the
blocked[vbcol=seagreen]
[vbcol=seagreen]
>

Does SHOWCONTIG always use table S-lock?

I noticed that while running a DBCC SHOWCONTIG - this processed blocked
another process attempting to acquire an IX lock on that table. I saw that
the showcontig had an "S" lock on the table.
Is there any way to influence SQL Server into taking an IS lock on the table
and S locks on the pages?
Thanks in advanceWITH FAST Option may help.
also if a table is a heap (No Clustered Index) that will impact this
behavior. You WANT each table to have a clustered index (in General)
Cheers
Greg Jackson
PDX, OR|||Not currently. Even using WITH FAST requires a Shared Table lock (however,
except on the largest tables any blocking should be fairly transient as the
results are returned so quickly)
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
news:%23%23LyYoZSEHA.3636@.TK2MSFTNGP09.phx.gbl...
> I noticed that while running a DBCC SHOWCONTIG - this processed blocked
> another process attempting to acquire an IX lock on that table. I saw
that
> the showcontig had an "S" lock on the table.
> Is there any way to influence SQL Server into taking an IS lock on the
table
> and S locks on the pages?
> Thanks in advance
>|||Not true. If you only specify WITH FAST for a clustered or non-clustered
index it will acquire an IS table lock - that's the entire reason for me
adding the option in SQL Server 2000.
--
Paul Randal
Dev Lead, Microsoft SQL Server Storage Engine
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:uJCeJ7aSEHA.3728@.TK2MSFTNGP11.phx.gbl...
> Not currently. Even using WITH FAST requires a Shared Table lock (however,
> except on the largest tables any blocking should be fairly transient as
the
> results are returned so quickly)
> --
> HTH
> Jasper Smith (SQL Server MVP)
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
> news:%23%23LyYoZSEHA.3636@.TK2MSFTNGP09.phx.gbl...
> > I noticed that while running a DBCC SHOWCONTIG - this processed blocked
> > another process attempting to acquire an IX lock on that table. I saw
> that
> > the showcontig had an "S" lock on the table.
> >
> > Is there any way to influence SQL Server into taking an IS lock on the
> table
> > and S locks on the pages?
> >
> > Thanks in advance
> >
> >
>|||That's what I thought (and I can see now that it is) however when I was
testing before posting my original answer ( I really did test!) I was using
dbcc showcontig('wildtest') with fast
where wildtest has a clustered index and it was getting blocked by an IX
table lock from a transaction I left open so I could examine the locking. I
just tried it again specifying the clustered index explicitly and no
blocking
dbcc showcontig('wildtest',1) with fast
This is what threw me off track :-)
I was under the impression that the first query on a table with a clustered
index would result in the same behaviour as the second one ?
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Paul S Randal [MS]" <prandal@.online.microsoft.com> wrote in message
news:%231ITfVbSEHA.3812@.TK2MSFTNGP11.phx.gbl...
> Not true. If you only specify WITH FAST for a clustered or non-clustered
> index it will acquire an IS table lock - that's the entire reason for me
> adding the option in SQL Server 2000.
> --
> Paul Randal
> Dev Lead, Microsoft SQL Server Storage Engine
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
> news:uJCeJ7aSEHA.3728@.TK2MSFTNGP11.phx.gbl...
> > Not currently. Even using WITH FAST requires a Shared Table lock
(however,
> > except on the largest tables any blocking should be fairly transient as
> the
> > results are returned so quickly)
> >
> > --
> > HTH
> >
> > Jasper Smith (SQL Server MVP)
> >
> > I support PASS - the definitive, global
> > community for SQL Server professionals -
> > http://www.sqlpass.org
> >
> > "TJTODD" <Thxomasx.Toddy@.Siemensx.com> wrote in message
> > news:%23%23LyYoZSEHA.3636@.TK2MSFTNGP09.phx.gbl...
> > > I noticed that while running a DBCC SHOWCONTIG - this processed
blocked
> > > another process attempting to acquire an IX lock on that table. I saw
> > that
> > > the showcontig had an "S" lock on the table.
> > >
> > > Is there any way to influence SQL Server into taking an IS lock on the
> > table
> > > and S locks on the pages?
> > >
> > > Thanks in advance
> > >
> > >
> >
> >
>sql