Re: Transaction problem (delete / select)
From: Mike Epprecht (SQL MVP) (mike_at_epprecht.net)
Date: 01/18/05
- Next message: Doan Ly via SQLMonster.com: "Re: MS-SQL Search by keyword performance"
- Previous message: Tibor Karaszi: "Re: Transaction problem (delete / select)"
- In reply to: Joao Rosa: "Re: Transaction problem (delete / select)"
- Next in thread: Joao Rosa: "Re: Transaction problem (delete / select)"
- Reply: Joao Rosa: "Re: Transaction problem (delete / select)"
- Messages sorted by: [ date ] [ thread ]
Date: Tue, 18 Jan 2005 06:53:02 -0800
Hi
It is not a bug. This behavior can be expected. SQL Server may take a row
lock or a page lock during the delete. If you select needs to access a row
that is on the same page as the record that is being deleted, the select has
to wait.
Having a good clustered index can help alleviate the problem.
Having long running transactions, including ones that do DB work, then wait
for user input before they commit are going to have terrible blocking
problems.
Regards
Mike
"Joao Rosa" wrote:
> Hi Mike
> Thanks for you help.
>
> The delete is using a where clause. (sorry not to mention it).
> begin transaction
> delete from [table A] where [field 1] = [value]
>
>
> Until i do not make a commit or rollback on computer 1, shouldn´t the
> computer 2 bewave normaly? Like there is no change going on computer 1?
> Until the end of the transaction the table should be available to fetch all
> records, so the changes going on computer 1 are not efective, and on
> computer 2 it should work normaly.
>
> I tried with Access 2000 and this is not a problem.
> Could it be a bug, or some other thing?
>
>
> I hope i explained things well.
> Sorry for my english.
>
> Regards from Portugal
> João Rosa
> ---------------------------------------
>
>
> "Mike Epprecht (SQL MVP)" <mike@epprecht.net> escreveu na mensagem
> news:16F9FEB5-7002-48BD-AC71-ED1202A8A990@microsoft.com...
> > Hi
> >
> > Do you have WHERE clauses in the DELETE and SELECT statements?
> >
> > If not, you are expecting SQL Server to look at data that will all be
> removed.
> >
> > You could add the lock hint into the select "WITH (NOLOCK)", but be aware,
> > you could be reading rows that are removed by the time the data is
> returned
> > to you client program.
> >
> > What are you trying to do?
> >
> > Regards
> > Mike
> >
> > "Joao Rosa" wrote:
> >
> > > I have 2 computers linking to sql server 2000.
> > >
> > > computer 1:
> > > ....
> > > begin transaction
> > > delete from [table A]
> > > ....
> > >
> > > computer 2:
> > > select * from [table A]
> > >
> > >
> > > While the transaction is on computer 1, the select command on computer 2
> > > hangs, until computer 1 makes a commit or rollback.
> > > This only happens with the delete command, the insert and update are ok.
> > > The code in computer 1 is gona take a while to execute until a commit or
> > > rollback can be made, meanwhile computer 2 needs to access the table.
> > > I tried inclusive in sql analyzer with the same results.
> > >
> > >
> > > If anyone could help, i apreciated.
> > > Thanks
> > >
> > > João Rosa
> > >
> > >
> > >
>
>
>
- Next message: Doan Ly via SQLMonster.com: "Re: MS-SQL Search by keyword performance"
- Previous message: Tibor Karaszi: "Re: Transaction problem (delete / select)"
- In reply to: Joao Rosa: "Re: Transaction problem (delete / select)"
- Next in thread: Joao Rosa: "Re: Transaction problem (delete / select)"
- Reply: Joao Rosa: "Re: Transaction problem (delete / select)"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
