|
|
Posted by Andy M on 01/26/08 15:47
Locking hints can have some unexpected behavior.
In particular, the use of (nolock) during your SELECT could result in
either missing rows, or selecting the same row twice. This is due to
the way SQL Server will split & re-arrange pages during the INSERT
operation. Because some pages are being split and re-arranged, the
SELECT...(nolock) will not realize the pages are being rearranged, and
either miss, or skip certain rows/pages.
As a result, (nolock) is helpful for ad hoc queries, but in many
mission-critical production roles, (nolock) can cause many problems.
While the locking is happening, you can you sp_lock to see what
objects are being locked, and sp_who2 to verify the user/process
causing those locks.
I would highly recommend working to change the way the INSERT is done,
if there is anyway to do that. One possibility would be to have the
FOR insert into a temp table, then use INSERT INTO [Production Table]
SELECT...FROM [Temp Table] . This would allow for the insert to
occur in one operation, rather than 10,000 smaller inserts.
On Jan 25, 11:59 pm, quincy...@yahoo.com wrote:
> On Jan 25, 10:38 pm, quincy...@yahoo.com wrote:
>
>
>
> > On Jan 25, 9:25 pm, Tom van Stiphout <no.spam.tom7...@cox.net> wrote:
>
> > > On Fri, 25 Jan 2008 15:02:52 -0800 (PST), quincy...@yahoo.com wrote:
>
> > > I take it the code on the remote server is something like this:
> > > truncate table SomeTable
> > > for i=1 to 10000
> > > Insert One Row
> > > next i
> > Yes.
>
> > > Inserting a record will put an Exclusive lock on the table. From BOL:
> > > Exclusive (X) Used for data-modification operations, such as INSERT,
> > > UPDATE, or DELETE. Ensures that multiple updates cannot be made to the
> > > same resource at the same time.
>
> > So you are telling me INSERT in a loop like that will lock and unlock
> > the table
> > repeatedly, but possibly because of the tightness of the loop never
> > let me in
> > to get my stuff done. And are you saying UPDATE, and DELETE work the
> > same
> > way? Now for the dumb question: What is BOL: Exclusive (X)? I guess
> > it
> > remains a open question what might make the remote side take 6 minutes
> > to
> > complete these insert operations. But knowing what will lock the
> > other side
> > out is very important.
>
> Ok I found this reference in the documentation. I am wondering if
> locking hints on
> the select query on the local side my help this?
>
>
>
> > > If you don't have control over the remote server process, you will
> > > have to live with it. If you do have control, you could investigate
> > > more efficient data import strategy (perhaps BCP) as well as more
> > > acceptable timing (2AM).
>
> > Well, the other side is under my influence. I am the developer of the
> > local
> > application. The other side is someone else's code for which they
> > have
> > full control. I can advise and point in a different direction, if I
> > have that direction,
> > and most likely get things done. We are currently doing the late
> > night trick But
> > not always...
>
> > > -Tom.
>
> > > >I am running MS SQL 2000 server. The table involved is only about
> > > >10,000 records. But this is the behavior I am seeing.
>
> > > >The local machine is querying the table looking for a particular
> > > >record. Everything works fine.
> > > >The remote amchine goes to clear the table and then insert all 10,000
> > > >records, into the table the following happens.
>
> > > >1) the local machines queries do not compilete until the remote
> > > >machine is done.
> > > >2) the remote machine can take up to 6 minutes to do these 10,000
> > > >insert operations. So nothing on the local machine works right for
> > > >these 6 minutes.
>
> > > >I do not have access to the remote machines source to see what is
> > > >running but I am told it is simply a for loop with a insert query in
> > > >it. Nothing of a locking natture.
>
> > > >Any idea the types of things I should look for to track this down? I
> > > >found this by doing SQL profiler profiling and finding the remote
> > > >operations. Turn these operatiiosn off and the local machine works
> > > >fine again, with no other action.
>
> > > >Thanks,
> > > >David- Hide quoted text -
>
> > > - Show quoted text -- Hide quoted text -
>
> > - Show quoted text -
Navigation:
[Reply to this message]
|