Re: Transaction problem (delete / select)

From: Joao Rosa (joaorosa_at_netcabo.pt)
Date: 01/18/05 

Date: Tue, 18 Jan 2005 17:32:50 -0000

I confirmed that the problem is the locks when using delete.

I use the isolation level for transactions as read commited, so the chages i
made in computer 1 not visible in computer 2 until the end of the
transaction.
I understand your answer but i tried the same thing on Access 2000 and
oracle 9i and no problem ocurred, only with sql server 2000.
Forgive my ignorance but is any way to work around?
Can the locks be configured?

I'm gonna explain the code i have, and the context:
In computer 1 i have a form with data from table students and a grid from
table students_payments.
The table students_payments contains the payments that the students have
made.
When editing a student i can also delete, edit or insert in the grid
payments for the student.
The transaction begins before editing the student and ends when i don't need
to edit anymore.
Until then the table students_payments had several rows changed that cannot
be visible to others users until i commit the transaction.
The problem is here when i delete 1 row from table students_payments the
other users cannot use this table.
The transaction ends when i close the form (save or cancel).

Regards from Portugal
Joćo Rosa
---------------------------------------

"Mike Epprecht (SQL MVP)" <mike@epprecht.net> escreveu na mensagem
news:10451535-E9C6-4BCC-9A05-BF5FF56CCF4F@microsoft.com...
> 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
> > > >
> > > >
> > > >
> >
> >
> >

Relevant Pages

  • Re: [RFC] JBD ordered mode rewrite
    ... data buffers that need syncing on transaction commit but a list of inodes ... that need writeout during commit. ... the file then that inode would be added to the journal list and the pages ...
    (Linux-Kernel)
  • Re: Behavior of Connection.commit()
    ... the DBMS message when when the DBMS has already killed the whole tx, and in that case, throw an exception from any subsequent connection, statement or result set method that would/could do an update or query saying "The DBMS has killed the current transaction. ... The user application may depend on holding locks in order, and once it is allowed to proceed obtaining the lock for data 2, it may then go on to obtain other locks before trying to commit, and this not-expected order of locking may cause deadlocks that could kill other innocent, correct transactions. ...
    (microsoft.public.sqlserver.jdbcdriver)
  • Re: [RFC] JBD ordered mode rewrite
    ... data buffers that need syncing on transaction commit but a list of inodes ... that need writeout during commit. ... delayed allocation, starting a new transaction could to happen a lot to ...
    (Linux-Kernel)
  • Re: [PATCH 2/2] improve ext3 fsync batching
    ... array than it takes to complete the transaction. ... when commit times go up to seconds? ... Transactions on that busier drive would take longer, we would sleep ... longer which would allow us to batch up more into one transaction. ...
    (Linux-Kernel)
  • Re: Backups and Transaction Log file size
    ... It sounds like a classic case of a long running open transaction. ... Find the client and either commit or roll it back. ... If you know it is a garbage connection you can kill the SPID and it will roll back any changes that the SPID may have open and allow you to backup and truncate properly. ... Once the committed trans have been ...
    (microsoft.public.sqlserver.setup)
Loading