locking

How does SS2000 lock by default for a select, update and delete statement.
Is it row level, page, table?
Thanks,
DanIt depends. Default is row level, but optimizer may escalate it to page
or table level locking if it see fit. You can force it to stay at row
level by using query hints. Check query hints in BOL
Eric Li
SQL DBA
MCDBA
Dan wrote:

> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>|||Eric is correct but I want to make one slight comment. SQL Server never
escalates to a page level lock from a row level. All escalation is always
to table level if it occurs. But it can choose to take out a page or table
level lock in the first place.
Andrew J. Kelly
SQL Server MVP
"Eric.Li" <anonymous@.microsoftnews.org> wrote in message
news:OMU0VgBSEHA.1392@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> It depends. Default is row level, but optimizer may escalate it to page
> or table level locking if it see fit. You can force it to stay at row
> level by using query hints. Check query hints in BOL
> --
> Eric Li
> SQL DBA
> MCDBA
> Dan wrote:
>
statement.[vbcol=seagreen]|||It appears to select the type of locking largely based on the number of
records you are processing. If you processing a large number of records,
taking out a page / table lock is more resource friendly than individual row
locks. Having said that, it's possible to force the use of a particular
locking mechanism with hints.
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>|||If I also may add, SQL does not necessarily lock at the row level... One of
the recent improvements in the locking code for SQL is that the lock manager
takes into account how busy the object ( table for instance) has been
lately...
For instance, you are updating all of the rows in a table. If the table has
been very busy lately, SQL will choose a lower level lock ( page or row
level). This will cause your update to run more slowly ( because there will
be many more calls to the lock manager) , but overall concurrency will be
improved, since you will not be locking rows until right before you make the
update.
If the table has not been used lately, then you may get a whole table
lock... your update runs faster (due to the single lock manager call), and
no one else is hurt, since the table has not been used recently.
However in a busy multi user environment, lower level locks are most often
used...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dan" <ddonahue@.archermalmo.com> wrote in message
news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
> How does SS2000 lock by default for a select, update and delete statement.
> Is it row level, page, table?
> Thanks,
> Dan
>|||Thanks Eric, Andrew, Peter and Wayne. I understand it better now and I'll do
some more reading.
Dan
"Wayne Snyder" <wayne.nospam.snyder@.mariner-usa.com> wrote in message
news:egQfZvJSEHA.1160@.TK2MSFTNGP09.phx.gbl...
> If I also may add, SQL does not necessarily lock at the row level... One
of
> the recent improvements in the locking code for SQL is that the lock
manager
> takes into account how busy the object ( table for instance) has been
> lately...
> For instance, you are updating all of the rows in a table. If the table
has
> been very busy lately, SQL will choose a lower level lock ( page or row
> level). This will cause your update to run more slowly ( because there
will
> be many more calls to the lock manager) , but overall concurrency will be
> improved, since you will not be locking rows until right before you make
the
> update.
> If the table has not been used lately, then you may get a whole table
> lock... your update runs faster (due to the single lock manager call), and
> no one else is hurt, since the table has not been used recently.
> However in a busy multi user environment, lower level locks are most often
> used...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "Dan" <ddonahue@.archermalmo.com> wrote in message
> news:O7ngAXBSEHA.2216@.TK2MSFTNGP12.phx.gbl...
statement.[vbcol=seagreen]
>

Locking

I have set the Isolation Level to Read Uncommitted but the tables are still
locked after i have run a select in Query Analyzer, can anyone explain why
this is 'Can you provide more details? Hoe did you set the isolation level. Can you reproduce this from two
connection in query analyzer?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Clive Godden" <clive.godden@.peopleworld.co.uk> wrote in message
news:eGpQzej$EHA.2572@.tk2msftngp13.phx.gbl...
>I have set the Isolation Level to Read Uncommitted but the tables are still
> locked after i have run a select in Query Analyzer, can anyone explain why
> this is '
>|||All i did was run this
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
go
begin transaction
select * from gipsilicence
commit transaction
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||That doesn't help us reproduce the problem as this is only one connection, and I assume that the one
connection doesn't block itself. Are you saying that this connection is blocking another connection,
or is it blocked by another connection? NOLOCK should not acquire shared locks and should not honor
exclusive locks, hence I'm asking as much details as possible, preferably a repro.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Clive Godden" <clive.godden@.peopleworld.co.uk> wrote in message
news:eqoQm3j$EHA.2136@.TK2MSFTNGP10.phx.gbl...
> All i did was run this
> SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
> go
> begin transaction
> select * from gipsilicence
> commit transaction
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!sql

Locking

It appears that when we issue an UPDATE statement on a table, it locks the
WHOLE table so that any other users can not complete SELECT queries on the
table (without using "WITH (nolock)" in the SELECT statement)
Is there anyway to tell SQL SERVER to not lock the WHOLE table when doing an
update, so that maybe it takes longer, but it only locks 1 row or page at a
time?Mike
The optimiser escalates to a full table lock when it thinks it will be more
efficient way of processing the data than using page locks. There are a
number of things you can do if you think it should not be performing a table
lock.
Are your statistics up to date? If you don't regularly update statistics,
the optimiser is working with at least one hand tied behind it's back. If the
statistics are not up tp date it may be making incorrect choices.
Do you have suitable indexes? If you have indexes on the data you are
updating (especially a clustered index) it will be easier for the optimiser
to use page locks rather than table locks, if suitable.
It may be you are updating enough of the table that a table lock is the
correct option. In this case you can split up your updates into smaller
transactions, small enough that the optimiser uses page locks.
Hope this helps
John
"Mike" wrote:
> It appears that when we issue an UPDATE statement on a table, it locks the
> WHOLE table so that any other users can not complete SELECT queries on the
> table (without using "WITH (nolock)" in the SELECT statement)
> Is there anyway to tell SQL SERVER to not lock the WHOLE table when doing an
> update, so that maybe it takes longer, but it only locks 1 row or page at a
> time?
>
>|||Forgive my ignorance, but how do I get my statistics up to date? I am not
experienced in SQL Server administration.
I am using the pubs database in a new installation of SQL Server.
When I issue:
UPDATE authors set au_lname = 'a'
and then jump over to Enterprise Manager and right-click on "Current
Activity" under Management and select Refresh, then I go to Locks/Object and
pubs.dbo.authors, there is 1 row where Lock Type is "TAB" and a bunch with
Lock Type "PAG".
If I add "with (rowlock)" to the UPDATE statement, there is still 1 TAB lock
but less PAG locks.
(I inserted thousands more rows into authors than it comes with to give me
time to go to Ent. Mgr and select Refresh while the update is running)
There is a compound index on au_lname and au_fname.
"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
message news:D12A67EA-93C0-48F4-A45B-B4A3E9A48218@.microsoft.com...
> Mike
> The optimiser escalates to a full table lock when it thinks it will be
> more
> efficient way of processing the data than using page locks. There are a
> number of things you can do if you think it should not be performing a
> table
> lock.
> Are your statistics up to date? If you don't regularly update statistics,
> the optimiser is working with at least one hand tied behind it's back. If
> the
> statistics are not up tp date it may be making incorrect choices.
> Do you have suitable indexes? If you have indexes on the data you are
> updating (especially a clustered index) it will be easier for the
> optimiser
> to use page locks rather than table locks, if suitable.
> It may be you are updating enough of the table that a table lock is the
> correct option. In this case you can split up your updates into smaller
> transactions, small enough that the optimiser uses page locks.
> Hope this helps
> John
> "Mike" wrote:
>> It appears that when we issue an UPDATE statement on a table, it locks
>> the
>> WHOLE table so that any other users can not complete SELECT queries on
>> the
>> table (without using "WITH (nolock)" in the SELECT statement)
>> Is there anyway to tell SQL SERVER to not lock the WHOLE table when doing
>> an
>> update, so that maybe it takes longer, but it only locks 1 row or page at
>> a
>> time?
>>|||I think you see the IX (intent exclusive) lock on the table.
That is not a real lock, it just tells the the engine that there is a execute
or update lock somewhere in the table.
Normally SQLServer will use row locks. The only way you get a real table
lock with this statement is if you have forbidden row and page locks to a
clustered index on the table. I doubt you have been playing with the
sp_indexoption, so that seems unlikely.
Joachim.
On Fri, 21 Jan 2005 11:28:34 -0500, "Mike W" <mikeotown@.nospam.msn.com>
wrote:
>Forgive my ignorance, but how do I get my statistics up to date? I am not
>experienced in SQL Server administration.
>I am using the pubs database in a new installation of SQL Server.
>When I issue:
>UPDATE authors set au_lname = 'a'
>and then jump over to Enterprise Manager and right-click on "Current
>Activity" under Management and select Refresh, then I go to Locks/Object and
>pubs.dbo.authors, there is 1 row where Lock Type is "TAB" and a bunch with
>Lock Type "PAG".
>If I add "with (rowlock)" to the UPDATE statement, there is still 1 TAB lock
>but less PAG locks.
>(I inserted thousands more rows into authors than it comes with to give me
>time to go to Ent. Mgr and select Refresh while the update is running)
>There is a compound index on au_lname and au_fname.
>"John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
>message news:D12A67EA-93C0-48F4-A45B-B4A3E9A48218@.microsoft.com...
>> Mike
>> The optimiser escalates to a full table lock when it thinks it will be
>> more
>> efficient way of processing the data than using page locks. There are a
>> number of things you can do if you think it should not be performing a
>> table
>> lock.
>> Are your statistics up to date? If you don't regularly update statistics,
>> the optimiser is working with at least one hand tied behind it's back. If
>> the
>> statistics are not up tp date it may be making incorrect choices.
>> Do you have suitable indexes? If you have indexes on the data you are
>> updating (especially a clustered index) it will be easier for the
>> optimiser
>> to use page locks rather than table locks, if suitable.
>> It may be you are updating enough of the table that a table lock is the
>> correct option. In this case you can split up your updates into smaller
>> transactions, small enough that the optimiser uses page locks.
>> Hope this helps
>> John
>> "Mike" wrote:
>> It appears that when we issue an UPDATE statement on a table, it locks
>> the
>> WHOLE table so that any other users can not complete SELECT queries on
>> the
>> table (without using "WITH (nolock)" in the SELECT statement)
>> Is there anyway to tell SQL SERVER to not lock the WHOLE table when doing
>> an
>> update, so that maybe it takes longer, but it only locks 1 row or page at
>> a
>> time?
>>
>
--
This post is free post; you can redistribute it and/or
modify it under the terms of the GNU General Public License
as published by the Free Software Foundation; either version 2
of the License, or (at your option) any later version.
Joachim Verhagen
http://www.xs4all.nl/~jcdverha/ (Science Jokes)|||Mike
You can update statistics automatically by enabling the auto update
statistics option. You can do this through enterprise manager. Right click on
the database you want to set the option for and choose properties. On the
option page of properties you can turn this option on and off. You can also
turn the option on or off through query analyser using sp_dboption. (See BOL
for details).
You can use dbcc show_statistics to see how up to date your statistics are.
Statistics also get updated when you rebuild a clustered index.
Hope this helps
John
"Mike W" wrote:
> Forgive my ignorance, but how do I get my statistics up to date? I am not
> experienced in SQL Server administration.
> I am using the pubs database in a new installation of SQL Server.
> When I issue:
> UPDATE authors set au_lname = 'a'
> and then jump over to Enterprise Manager and right-click on "Current
> Activity" under Management and select Refresh, then I go to Locks/Object and
> pubs.dbo.authors, there is 1 row where Lock Type is "TAB" and a bunch with
> Lock Type "PAG".
> If I add "with (rowlock)" to the UPDATE statement, there is still 1 TAB lock
> but less PAG locks.
> (I inserted thousands more rows into authors than it comes with to give me
> time to go to Ent. Mgr and select Refresh while the update is running)
> There is a compound index on au_lname and au_fname.
> "John Bandettini" <JohnBandettini@.discussions.microsoft.com> wrote in
> message news:D12A67EA-93C0-48F4-A45B-B4A3E9A48218@.microsoft.com...
> > Mike
> >
> > The optimiser escalates to a full table lock when it thinks it will be
> > more
> > efficient way of processing the data than using page locks. There are a
> > number of things you can do if you think it should not be performing a
> > table
> > lock.
> >
> > Are your statistics up to date? If you don't regularly update statistics,
> > the optimiser is working with at least one hand tied behind it's back. If
> > the
> > statistics are not up tp date it may be making incorrect choices.
> >
> > Do you have suitable indexes? If you have indexes on the data you are
> > updating (especially a clustered index) it will be easier for the
> > optimiser
> > to use page locks rather than table locks, if suitable.
> >
> > It may be you are updating enough of the table that a table lock is the
> > correct option. In this case you can split up your updates into smaller
> > transactions, small enough that the optimiser uses page locks.
> >
> > Hope this helps
> >
> > John
> >
> > "Mike" wrote:
> >
> >> It appears that when we issue an UPDATE statement on a table, it locks
> >> the
> >> WHOLE table so that any other users can not complete SELECT queries on
> >> the
> >> table (without using "WITH (nolock)" in the SELECT statement)
> >>
> >> Is there anyway to tell SQL SERVER to not lock the WHOLE table when doing
> >> an
> >> update, so that maybe it takes longer, but it only locks 1 row or page at
> >> a
> >> time?
> >>
> >>
> >>
>
>

Locking

Hi all!
Can anyone point me to a doc that describes what the default locking levels
were across different versions of SQL, i.e. row vs. page vs. table, going
all the way back to version 4.2, and what the circumstances would have been
when SQL decides what to use over the other? Guess versions 4.2 and 6.x were
page-locking no matter what if I remember correctly, but want to make sure
before I tell my colleagues rubbish.
Thanks in advance,
Jan Van der EeckenI don't have any documentation but you didn't have true row level locks
until version 7.0. So before that it was Page and after it is Row.
--
Andrew J. Kelly SQL MVP
"Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
> Hi all!
> Can anyone point me to a doc that describes what the default locking
> levels were across different versions of SQL, i.e. row vs. page vs. table,
> going all the way back to version 4.2, and what the circumstances would
> have been when SQL decides what to use over the other? Guess versions 4.2
> and 6.x were page-locking no matter what if I remember correctly, but want
> to make sure before I tell my colleagues rubbish.
> Thanks in advance,
> Jan Van der Eecken
>|||Thanks Andrew. I was almost under the impression that it was page-level in
7.0 as well.
Cheers,
Jan
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>I don't have any documentation but you didn't have true row level locks
>until version 7.0. So before that it was Page and after it is Row.
> --
> Andrew J. Kelly SQL MVP
>
> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>> Hi all!
>> Can anyone point me to a doc that describes what the default locking
>> levels were across different versions of SQL, i.e. row vs. page vs.
>> table, going all the way back to version 4.2, and what the circumstances
>> would have been when SQL decides what to use over the other? Guess
>> versions 4.2 and 6.x were page-locking no matter what if I remember
>> correctly, but want to make sure before I tell my colleagues rubbish.
>> Thanks in advance,
>> Jan Van der Eecken
>|||6.5 added some possibility to get some type of row locks for INSERTs. Sorry to be vague, but it was
a long time ago. As I recall, you had to satisfy a number of conditions (probably what indexes you
had) in order to get some concurrency on the page for INSERT operations.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>I don't have any documentation but you didn't have true row level locks until version 7.0. So
>before that it was Page and after it is Row.
> --
> Andrew J. Kelly SQL MVP
>
> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>> Hi all!
>> Can anyone point me to a doc that describes what the default locking levels were across different
>> versions of SQL, i.e. row vs. page vs. table, going all the way back to version 4.2, and what the
>> circumstances would have been when SQL decides what to use over the other? Guess versions 4.2 and
>> 6.x were page-locking no matter what if I remember correctly, but want to make sure before I tell
>> my colleagues rubbish.
>> Thanks in advance,
>> Jan Van der Eecken
>|||Thanks, Tibor
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23dwfZahWGHA.4620@.TK2MSFTNGP04.phx.gbl...
> 6.5 added some possibility to get some type of row locks for INSERTs.
> Sorry to be vague, but it was a long time ago. As I recall, you had to
> satisfy a number of conditions (probably what indexes you had) in order to
> get some concurrency on the page for INSERT operations.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>>I don't have any documentation but you didn't have true row level locks
>>until version 7.0. So before that it was Page and after it is Row.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
>> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>> Hi all!
>> Can anyone point me to a doc that describes what the default locking
>> levels were across different versions of SQL, i.e. row vs. page vs.
>> table, going all the way back to version 4.2, and what the circumstances
>> would have been when SQL decides what to use over the other? Guess
>> versions 4.2 and 6.x were page-locking no matter what if I remember
>> correctly, but want to make sure before I tell my colleagues rubbish.
>> Thanks in advance,
>> Jan Van der Eecken
>>
>|||They called it INSERT-ROW locks in SQL 6.5, but it was not row level
locking. It was just a new type of page lock that allowed multiple inserts
on the same page.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23dwfZahWGHA.4620@.TK2MSFTNGP04.phx.gbl...
> 6.5 added some possibility to get some type of row locks for INSERTs.
> Sorry to be vague, but it was a long time ago. As I recall, you had to
> satisfy a number of conditions (probably what indexes you had) in order to
> get some concurrency on the page for INSERT operations.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>>I don't have any documentation but you didn't have true row level locks
>>until version 7.0. So before that it was Page and after it is Row.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
>> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>> Hi all!
>> Can anyone point me to a doc that describes what the default locking
>> levels were across different versions of SQL, i.e. row vs. page vs.
>> table, going all the way back to version 4.2, and what the circumstances
>> would have been when SQL decides what to use over the other? Guess
>> versions 4.2 and 6.x were page-locking no matter what if I remember
>> correctly, but want to make sure before I tell my colleagues rubbish.
>> Thanks in advance,
>> Jan Van der Eecken
>>
>|||Thanks for the answers all of you ppl! Got the answer to my colleague
correct after all. But I really appreciate your help, and I really feel
honoured since I only got replies from MVP's..Obviously my question was kind
of obscure.
Nite,
Jan Van der Eecken
Cape Town
South Africa
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23spoQjlWGHA.4768@.TK2MSFTNGP05.phx.gbl...
> They called it INSERT-ROW locks in SQL 6.5, but it was not row level
> locking. It was just a new type of page lock that allowed multiple inserts
> on the same page.
> --
> HTH
> Kalen Delaney, SQL Server MVP
> www.solidqualitylearning.com
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
> in message news:%23dwfZahWGHA.4620@.TK2MSFTNGP04.phx.gbl...
>> 6.5 added some possibility to get some type of row locks for INSERTs.
>> Sorry to be vague, but it was a long time ago. As I recall, you had to
>> satisfy a number of conditions (probably what indexes you had) in order
>> to get some concurrency on the page for INSERT operations.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
>> news:%23LP5XxdWGHA.2376@.TK2MSFTNGP03.phx.gbl...
>>I don't have any documentation but you didn't have true row level locks
>>until version 7.0. So before that it was Page and after it is Row.
>> --
>> Andrew J. Kelly SQL MVP
>>
>> "Jan Van der Eecken" <jvandereecken@.omam.com> wrote in message
>> news:u$lROWbWGHA.1228@.TK2MSFTNGP02.phx.gbl...
>> Hi all!
>> Can anyone point me to a doc that describes what the default locking
>> levels were across different versions of SQL, i.e. row vs. page vs.
>> table, going all the way back to version 4.2, and what the
>> circumstances would have been when SQL decides what to use over the
>> other? Guess versions 4.2 and 6.x were page-locking no matter what if I
>> remember correctly, but want to make sure before I tell my colleagues
>> rubbish.
>> Thanks in advance,
>> Jan Van der Eecken
>>
>>
>

Locking

Hi all,
SQL server doesnt provide any kind of configuration to
control granularity of locking on tables. As per books
online it is managed dynamically.
I have a major problem with my SQL Server, during
execution of one SP, on one table with 400+ rows it
acquires 600,000 locks. My profiler shows
whooping 'Lock:Acquired' on this particular table.
Can somebody please throw some light on this? Why SQL
server doesn't promote it to single table level lock? Is
there any bug in SQL?
thanks very much
Himanshu JaniYou probably want to keep working on why this happens in the first place (although there's not much
to go on for us), but here are a couple of options for immediate fix:
Specify lock hint in the query (TABLOCK, for instance).
Configure the index used to not allow row locks (sp_indexoption).
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as ugroup=microsoft.public.sqlserver
"Himanshu Jani" <anonymous@.discussions.microsoft.com> wrote in message
news:2c84301c393cd$c746cab0$a601280a@.phx.gbl...
> Hi all,
> SQL server doesnt provide any kind of configuration to
> control granularity of locking on tables. As per books
> online it is managed dynamically.
> I have a major problem with my SQL Server, during
> execution of one SP, on one table with 400+ rows it
> acquires 600,000 locks. My profiler shows
> whooping 'Lock:Acquired' on this particular table.
> Can somebody please throw some light on this? Why SQL
> server doesn't promote it to single table level lock? Is
> there any bug in SQL?
> thanks very much
> Himanshu Jani

Locking

Our database hase been designed around the optimistic locking strategy - this has worked well for many years- however we have now ported our database to ms sql server - where the default locking strategy is pessimistic - This is fine for most transactions - but I badly need to change the default to optimistic for one particular area of the system - how do I do this at the server side
In short - How do I change the default locking strategy from pessimistic to optimistic
We are using Borland delphi and bde to communicate with the database.Actually the default is Optimistic as far as SQL Server itself is concerned.
You should investigate how your connections are being made to the server and
what they are setting. For instance you may be set to use the SERIALIZABLE
transaction isolation mode and set as with IMPLICIT TRANSACTIONS. I would
check out SET IMPLICIT_TRANSACTIONS and SET TRANSACTION ISOLATION LEVEL in
BooksOnLine. You can useProfiler to see what your front end is sending to
SQL Server when you connect.
--
Andrew J. Kelly SQL MVP
"scimitar" <julian@.phoenix7.co.uk> wrote in message
news:AD2AA861-40AE-47DE-852A-4D4264781418@.microsoft.com...
> Our database hase been designed around the optimistic locking strategy -
this has worked well for many years- however we have now ported our database
to ms sql server - where the default locking strategy is pessimistic - This
is fine for most transactions - but I badly need to change the default to
optimistic for one particular area of the system - how do I do this at the
server side.
> In short - How do I change the default locking strategy from pessimistic
to optimistic ?
> We are using Borland delphi and bde to communicate with the database.|||Andrew, Pessimistic is the default.
The only way to obtain optimistic concurrency control is from an application
using some sort of GUID or timestamp or you would have to use some
combination of nolock,update locks ,etc . If you provide some more info, Im
pretty sure someone could assist you
"Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
news:edbBIXc%23DHA.488@.TK2MSFTNGP12.phx.gbl...
> Actually the default is Optimistic as far as SQL Server itself is
concerned.
> You should investigate how your connections are being made to the server
and
> what they are setting. For instance you may be set to use the
SERIALIZABLE
> transaction isolation mode and set as with IMPLICIT TRANSACTIONS. I
would
> check out SET IMPLICIT_TRANSACTIONS and SET TRANSACTION ISOLATION LEVEL in
> BooksOnLine. You can useProfiler to see what your front end is sending to
> SQL Server when you connect.
> --
> Andrew J. Kelly SQL MVP
>
> "scimitar" <julian@.phoenix7.co.uk> wrote in message
> news:AD2AA861-40AE-47DE-852A-4D4264781418@.microsoft.com...
> > Our database hase been designed around the optimistic locking strategy -
> this has worked well for many years- however we have now ported our
database
> to ms sql server - where the default locking strategy is pessimistic -
This
> is fine for most transactions - but I badly need to change the default to
> optimistic for one particular area of the system - how do I do this at the
> server side.
> >
> > In short - How do I change the default locking strategy from pessimistic
> to optimistic ?
> >
> > We are using Borland delphi and bde to communicate with the database.
>|||> The only way to obtain optimistic concurrency control is from an
application
> using some sort of GUID or timestamp
Although a rowversion column makes the task easier, even without one you can
compare column values with those initially retrieved. For example:
UPDATE MyTable
SET MyData = @.NewValue
WHERE MyPK = @.MyPK AND
MyData = @.OldValue
IF @.@.ROWCOUNT = 0
RAISERROR('Data modified by another user', 16, 1)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:uDp3$We%23DHA.1804@.TK2MSFTNGP12.phx.gbl...
> Andrew, Pessimistic is the default.
> The only way to obtain optimistic concurrency control is from an
application
> using some sort of GUID or timestamp or you would have to use some
> combination of nolock,update locks ,etc . If you provide some more info,
Im
> pretty sure someone could assist you
> "Andrew J. Kelly" <sqlmvpnoooospam@.shadhawk.com> wrote in message
> news:edbBIXc%23DHA.488@.TK2MSFTNGP12.phx.gbl...
> > Actually the default is Optimistic as far as SQL Server itself is
> concerned.
> > You should investigate how your connections are being made to the server
> and
> > what they are setting. For instance you may be set to use the
> SERIALIZABLE
> > transaction isolation mode and set as with IMPLICIT TRANSACTIONS. I
> would
> > check out SET IMPLICIT_TRANSACTIONS and SET TRANSACTION ISOLATION LEVEL
in
> > BooksOnLine. You can useProfiler to see what your front end is sending
to
> > SQL Server when you connect.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "scimitar" <julian@.phoenix7.co.uk> wrote in message
> > news:AD2AA861-40AE-47DE-852A-4D4264781418@.microsoft.com...
> > > Our database hase been designed around the optimistic locking
strategy -
> > this has worked well for many years- however we have now ported our
> database
> > to ms sql server - where the default locking strategy is pessimistic -
> This
> > is fine for most transactions - but I badly need to change the default
to
> > optimistic for one particular area of the system - how do I do this at
the
> > server side.
> > >
> > > In short - How do I change the default locking strategy from
pessimistic
> > to optimistic ?
> > >
> > > We are using Borland delphi and bde to communicate with the database.
> >
> >
>|||Were you using previously Interbase?
"scimitar" <julian@.phoenix7.co.uk> wrote in message
news:AD2AA861-40AE-47DE-852A-4D4264781418@.microsoft.com...
> Our database hase been designed around the optimistic locking strategy -
this has worked well for many years- however we have now ported our database
to ms sql server - where the default locking strategy is pessimistic - This
is fine for most transactions - but I badly need to change the default to
optimistic for one particular area of the system - how do I do this at the
server side.
> In short - How do I change the default locking strategy from pessimistic
to optimistic ?
> We are using Borland delphi and bde to communicate with the database.

Locking

I've got the following stored procedure I'm working on:
-- ============================================= -- Create procedure postCash
-- ============================================= USE Prototype
GO
IF EXISTS (SELECT name
FROM sysobjects
WHERE name = N'postCash'
AND type = 'P')
DROP PROCEDURE postCash
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE postCash
@.bank_id varchar(50), @.gl_id int, @.gl_code varchar(50),
@.post_date varchar(10), @.amount money, @.comment varchar(50),
@.check_number varchar(50) = NULL
AS
SET NOCOUNT ON
DECLARE @.err_code int
DECLARE @.current_bal money
BEGIN TRANSACTION
SET @.current_bal = (SELECT TOP 1 NewBalance FROM Cash ORDER BY GLID DESC)
SET @.err_code = @.@.ERROR
IF @.err_code <> 0 GOTO AbortTransaction
INSERT INTO Cash (BankAccountID, GLID, GLCode, [Date], Amount, NewBalance,
Comment, CheckNumber)
VALUES (@.bank_id, @.gl_id, @.gl_code, @.post_date, @.amount, @.amount +
@.current_bal, @.comment, @.check_number);
SET @.err_code = @.@.ERROR
IF @.err_code <> 0 GOTO AbortTransaction
COMMIT TRANSACTION
RETURN 0
AbortTransaction:
ROLLBACK TRANSACTION
RETURN @.err_code
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
I want to make sure that no one else can read the value of NewBalance until
the entire transaction completes successfully. I've read through various
locking topics in BOL but I don't quite understand the difference between
putting, say SERIALIZABLE after FROM in the SELECT or using SET TRANSACTION
ISOLATION LEVEL SERIALIZABLE. In the examples, the second statment is always
followed by GO. But if I use that in my SP then won't it wipe out all my
variable declarations? Will putting SERIALIZABLE after FROM produce the
result I'm looking for? Or, if I use SET TRANSACTION ISOLATION LEVEL
SERIALIZABLE do I then need to "turn it off" at the end somewhere?Ron,
As I understand it, you could use HOLDLOCK for the query (equivalent to
SERIALIZABLE). No one should be able to see the INSERTed record until the
tran commits.
HTH
Jerry
"Ron Hinds" <__ron__dontspamme@.wedontlikespam_garageiq.com> wrote in message
news:O8uWkNc1FHA.3892@.TK2MSFTNGP12.phx.gbl...
> I've got the following stored procedure I'm working on:
> -- =============================================> -- Create procedure postCash
> -- =============================================> USE Prototype
> GO
> IF EXISTS (SELECT name
> FROM sysobjects
> WHERE name = N'postCash'
> AND type = 'P')
> DROP PROCEDURE postCash
> GO
> SET QUOTED_IDENTIFIER ON
> GO
> SET ANSI_NULLS ON
> GO
> CREATE PROCEDURE postCash
> @.bank_id varchar(50), @.gl_id int, @.gl_code varchar(50),
> @.post_date varchar(10), @.amount money, @.comment varchar(50),
> @.check_number varchar(50) = NULL
> AS
> SET NOCOUNT ON
> DECLARE @.err_code int
> DECLARE @.current_bal money
> BEGIN TRANSACTION
> SET @.current_bal = (SELECT TOP 1 NewBalance FROM Cash ORDER BY GLID DESC)
> SET @.err_code = @.@.ERROR
> IF @.err_code <> 0 GOTO AbortTransaction
> INSERT INTO Cash (BankAccountID, GLID, GLCode, [Date], Amount,
> NewBalance,
> Comment, CheckNumber)
> VALUES (@.bank_id, @.gl_id, @.gl_code, @.post_date, @.amount, @.amount +
> @.current_bal, @.comment, @.check_number);
> SET @.err_code = @.@.ERROR
> IF @.err_code <> 0 GOTO AbortTransaction
> COMMIT TRANSACTION
> RETURN 0
> AbortTransaction:
> ROLLBACK TRANSACTION
> RETURN @.err_code
> GO
> SET QUOTED_IDENTIFIER OFF
> GO
> SET ANSI_NULLS ON
> GO
> I want to make sure that no one else can read the value of NewBalance
> until
> the entire transaction completes successfully. I've read through various
> locking topics in BOL but I don't quite understand the difference between
> putting, say SERIALIZABLE after FROM in the SELECT or using SET
> TRANSACTION
> ISOLATION LEVEL SERIALIZABLE. In the examples, the second statment is
> always
> followed by GO. But if I use that in my SP then won't it wipe out all my
> variable declarations? Will putting SERIALIZABLE after FROM produce the
> result I'm looking for? Or, if I use SET TRANSACTION ISOLATION LEVEL
> SERIALIZABLE do I then need to "turn it off" at the end somewhere?
>|||On Thu, 20 Oct 2005 15:12:40 -0700, "Ron Hinds"
<__ron__dontspamme@.wedontlikespam_garageiq.com> wrote:
>I want to make sure that no one else can read the value of NewBalance until
>the entire transaction completes successfully.
How sure?
If you mean COMPLETELY SURE I don't think you can do it (without
heroic methods) in SQLServer, if reader allows dirty reads. I don't
believe even serializable shuts out readers. You could throw the
database into single-user mode, but that's pretty extreme.
On the other hand, simply doing what you've already done, putting the
insert into a transaction, will assure that readers in the default
"read committed" or higher levels of isolation, will pend behind your
transaction if they try to read it.
> I've read through various
>locking topics in BOL but I don't quite understand the difference between
>putting, say SERIALIZABLE after FROM in the SELECT or using SET TRANSACTION
>ISOLATION LEVEL SERIALIZABLE. In the examples, the second statment is always
>followed by GO. But if I use that in my SP then won't it wipe out all my
>variable declarations?
Don't put GO in SPs, everything will be fine.
>Will putting SERIALIZABLE after FROM produce the
>result I'm looking for? Or, if I use SET TRANSACTION ISOLATION LEVEL
>SERIALIZABLE do I then need to "turn it off" at the end somewhere?
It ends with the SP, but I think you don't really need it.
J.sql