Re: select rows database lock?
From: Roman Rehak (rrehak_at_OPPOSITEofCOLDmail.com)
Date: 02/28/05
- Next message: Josh: "Re: Deploying System DSN to Multiple PCs"
- Previous message: Roman Rehak: "Re: Deploying System DSN to Multiple PCs"
- Maybe in reply to: David Gugick: "Re: select rows database lock?"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 28 Feb 2005 00:22:10 -0500
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@discussions.microsoft.com> wrote in message
news:90B4843D-135F-4BBA-8A0D-3CF6115E9080@microsoft.com...
> 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@discussions.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.
> >
> >
- Next message: Josh: "Re: Deploying System DSN to Multiple PCs"
- Previous message: Roman Rehak: "Re: Deploying System DSN to Multiple PCs"
- Maybe in reply to: David Gugick: "Re: select rows database lock?"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
