select rows database lock?

#1

When in the sql enterprise manager, if you right click a table and go to open
table--> select all rows, does that "lock" the database? As in if you select
all rows will someone else trying to read the data (via app or web interface)
and update it be locked out of it? We're often told that we shouldn't use
this method at our work and we should only run statements with nolocks on
live data. I had always thought that the data only locks when you put the
cursor in one of the rows to actually edit it.

What is the correct ruling? I could do tests myself but i'm not sure they
would be accurate. I mainly want to know what happens when you select all
rows (or select top) but don't put your cursor in the data.

#2

Enterprise Manager wasn't designed to be a client data tool
so how it retrieves records from large tables is not
necessarily very efficient.
Enterprise Manager will need to consume the entire recordset
to have the locks released. You need to scroll to the last
record in Enterprise Manager to have the locks
released. You would have an IS lock on the table and a
shared lock on the specific page you are on until moving to
the end of the result set.
A shared lock is not compatible with an exclusive lock.
Exclusive locks are needed for updates.

-Sue

On Fri, 25 Feb 2005 05:47:02 -0800, "gl"
<gl (AT) discussions (DOT) microsoft.com> wrote:

Quote:

When in the sql enterprise manager, if you right click a table and go to open
table--> select all rows, does that "lock" the database? As in if you select
all rows will someone else trying to read the data (via app or web interface)
and update it be locked out of it? We're often told that we shouldn't use
this method at our work and we should only run statements with nolocks on
live data. I had always thought that the data only locks when you put the
cursor in one of the rows to actually edit it.

What is the correct ruling? I could do tests myself but i'm not sure they
would be accurate. I mainly want to know what happens when you select all
rows (or select top) but don't put your cursor in the data.

#3

gl wrote:

Quote:

When in the sql enterprise manager, if you right click a table and go
to open table--> select all rows, does that "lock" the database? As
in if you select all rows will someone else trying to read the data
(via app or web interface) and update it be locked out of it? We're
often told that we shouldn't use this method at our work and we
should only run statements with nolocks on live data. I had always
thought that the data only locks when you put the cursor in one of
the rows to actually edit it.

What is the correct ruling? I could do tests myself but i'm not sure
they would be accurate. I mainly want to know what happens when you
select all rows (or select top) but don't put your cursor in the data.

You should not use SQL EM for browsing tables because it does leave
locks on unfetched pages on the server. You can easily test this
yourself:

1- Pick any medium sized table
2- Open it up in SQL EM (don't scroll the table at all at this point)
3- Run Profiler and use the default template
4- Back in SQL EM drag the scroll bar to the end of the table to force
all rows to be fetched
5- Go back to Profiler and you'll see the SQL:BatchCompleted event pop
up

--
David Gugick
Imceda Software
www.imceda.com

#4

So if i go to open sql-->query then put a NOLOCK on the base select statement
will that solve the problem?

So what i'm getting here is the database is locked until the you scroll to
the last record? Is the best way of viewing live data just typing out query
statements with a nolock in the sql query tool then?

"Sue Hoegemeier" wrote:

Quote:

Enterprise Manager wasn't designed to be a client data tool
so how it retrieves records from large tables is not
necessarily very efficient.
Enterprise Manager will need to consume the entire recordset
to have the locks released. You need to scroll to the last
record in Enterprise Manager to have the locks
released. You would have an IS lock on the table and a
shared lock on the specific page you are on until moving to
the end of the result set.
A shared lock is not compatible with an exclusive lock.
Exclusive locks are needed for updates.

-Sue

On Fri, 25 Feb 2005 05:47:02 -0800, "gl"
gl (AT) discussions (DOT) microsoft.com> wrote:

When in the sql enterprise manager, if you right click a table and go to open
table--> select all rows, does that "lock" the database? As in if you select
all rows will someone else trying to read the data (via app or web interface)
and update it be locked out of it? We're often told that we shouldn't use
this method at our work and we should only run statements with nolocks on
live data. I had always thought that the data only locks when you put the
cursor in one of the rows to actually edit it.

What is the correct ruling? I could do tests myself but i'm not sure they
would be accurate. I mainly want to know what happens when you select all
rows (or select top) but don't put your cursor in the data.

#5

gl wrote:

Quote:

So if i go to open sql-->query then put a NOLOCK on the base select
statement will that solve the problem?

So what i'm getting here is the database is locked until the you
scroll to the last record? Is the best way of viewing live data just
typing out query statements with a nolock in the sql query tool then?

Yes, I would use a query tool like QA instead.

--
David Gugick
Imceda Software
www.imceda.com

#6

NOLOCK helps, but you also have to attach it to every table in the select
statement. It's really best to stay away from viewing data in EM. I learned
the hard way that it can interfere with production applications. I did some
experiments and it also seemed that whether locking occurred or not was
really unpredictable, especially with views.

Roman

Roman Rehak, MCSD, MCDBA, MCSA
SQL Server Specialist
Competitive Computing
http://sqljunkies.com/WebLog/Roman

"gl" <gl (AT) discussions (DOT) microsoft.com> wrote

Quote:

So if i go to open sql-->query then put a NOLOCK on the base select
statement
will that solve the problem?

So what i'm getting here is the database is locked until the you scroll to
the last record? Is the best way of viewing live data just typing out
query
statements with a nolock in the sql query tool then?

"Sue Hoegemeier" wrote:

Enterprise Manager wasn't designed to be a client data tool
so how it retrieves records from large tables is not
necessarily very efficient.
Enterprise Manager will need to consume the entire recordset
to have the locks released. You need to scroll to the last
record in Enterprise Manager to have the locks
released. You would have an IS lock on the table and a
shared lock on the specific page you are on until moving to
the end of the result set.
A shared lock is not compatible with an exclusive lock.
Exclusive locks are needed for updates.

-Sue

On Fri, 25 Feb 2005 05:47:02 -0800, "gl"
gl (AT) discussions (DOT) microsoft.com> wrote:

When in the sql enterprise manager, if you right click a table and go
to open
table--> select all rows, does that "lock" the database? As in if you
select
all rows will someone else trying to read the data (via app or web
interface)
and update it be locked out of it? We're often told that we shouldn't
use
this method at our work and we should only run statements with nolocks
on
live data. I had always thought that the data only locks when you put
the
cursor in one of the rows to actually edit it.

What is the correct ruling? I could do tests myself but i'm not sure
they
would be accurate. I mainly want to know what happens when you select
all
rows (or select top) but don't put your cursor in the data.

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

select rows database lock?

microsoft.public.sqlserver.clients microsoft.public.sqlserver.clients

select rows database lock? - 02-25-2005 , 08:47 AM

Re: select rows database lock? - 02-25-2005 , 09:37 AM

Re: select rows database lock? - 02-25-2005 , 09:41 AM

Re: select rows database lock? - 02-25-2005 , 11:25 AM

Re: select rows database lock? - 02-25-2005 , 01:40 PM

Re: select rows database lock? - 02-28-2005 , 12:22 AM