Board index » delphi » Transaction from one user locks the other user

Transaction from one user locks the other user

Hi.

I recently signaled a strange situation using AdoExpress. We use SQL Server
7, D5-Sr1
If two users append a record in the same table (using TAdoDataset
components) and the first user saves the record I start a transaction and
Post the record. When all is OK I perform a CommitTrans. For testing
purposes I placed a YesNo dialog in the BeforePost event of the AdoDataset
so the first user's transaction is held up until he closes the dialog.
When the second user now posts his new record (also trying to start a
transaction) the application just freezes until the first user either
commits or rolls back the transaction.
Yes, I know I should not let a transaction stop by UI but I think this
shouldn't occur ? I already experimented with IsolationLevel but this
doesn't help.
So, any ideas ? Is this an ADO or Delphi problem ?

Thanks in advance,

Groeten, Harry

Van Spaendonck AA BV
Harry Maes
http://www.eenloket.com
h.m...@spaendonck.nl

 

Re:Transaction from one user locks the other user


Quote
> So, any ideas ? Is this an ADO or Delphi problem ?

I think you are trying to get 2 update locks on the same table - SQL Server
won't let you do that.  The second user has to wait for the first user to
finish the transaction.

--
David Reed
White Plains, Maryland USA
www.diamondsg.com
www.oasisrep.com
*To reply, remove the no_spam from my email address
*To view file attachment(s), remove underscore from file extension

Re:Transaction from one user locks the other user


Quote
> True with table-level locking. Not so with page-level and record-level
locking
> unless you try to get 2 update locks on the same page/record.

From a semantic standpoint, you are correct; but in practice, the issue of
lock compatibility is much broader.. Unless you use query hints, SS
automatically determines lock granularity - and of course, it will choose
row locking when it can.  But even with row locks, updating one record might
require an update lock on several records; in the case of a non-indexed
table, that can mean every row; if the update involves a portion of a
clustered index, several rows could be locked.

Harry answered his own question, really, in his original post when he said
"I know I should not let a transaction stop by UI..."  That's one of the
golden rules.

--
David Reed
White Plains, Maryland USA
www.diamondsg.com
www.oasisrep.com
*To reply, remove the no_spam from my email address
*To view file attachment(s), remove underscore from file extension

Re:Transaction from one user locks the other user


Quote
> I think you are trying to get 2 update locks on the same table - SQL Server
> won't let you do that.  The second user has to wait for the first user to
> finish the transaction.

True with table-level locking. Not so with page-level and record-level locking
unless you try to get 2 update locks on the same page/record.

HTH
--
V. Nazarov
IT Manager, Sofia Cable

Re:Transaction from one user locks the other user


I agree completely. Pure row-level locking is very hard (if not impossible)
to achieve with MS SQL especially when updating an existing row. But
since we are dealing with inserts here, I don't think we would observe
such behavior with client side cursors. Haven't tested it so I might be
wrong.

--
V. Nazarov
IT Manager, Sofia Cable

Quote
"David Reed" <davidr...@worldnet.att.net> wrote in message

news:8hpndp$92g5@bornews.borland.com...
Quote
> > True with table-level locking. Not so with page-level and record-level
> locking
> > unless you try to get 2 update locks on the same page/record.

> From a semantic standpoint, you are correct; but in practice, the issue of
> lock compatibility is much broader.. Unless you use query hints, SS
> automatically determines lock granularity - and of course, it will choose
> row locking when it can.  But even with row locks, updating one record might
> require an update lock on several records; in the case of a non-indexed
> table, that can mean every row; if the update involves a portion of a
> clustered index, several rows could be locked.

> Harry answered his own question, really, in his original post when he said
> "I know I should not let a transaction stop by UI..."  That's one of the
> golden rules.

> --
> David Reed
> White Plains, Maryland USA
> www.diamondsg.com
> www.oasisrep.com
> *To reply, remove the no_spam from my email address
> *To view file attachment(s), remove underscore from file extension

Re:Transaction from one user locks the other user


Hi Guys,

Thanks for the replies. No UI over a transaction is the way to go. I also
find it strange that there's a deadlock situation with a client side cursor
when both users only Append a record. Maybe SS's performing a pagelock ?
BTW, can I use the Profiler to view info about this ?

Groeten, Harry

Van Spaendonck AA BV
Harry Maes
http://www.eenloket.com
h.m...@spaendonck.nl

Re:Transaction from one user locks the other user


Quote
> I also find it strange that there's a deadlock situation with a client
side cursor
> when both users only Append a record. Maybe SS's performing a pagelock ?
> BTW, can I use the Profiler to view info about this ?

It's not really a deadlock situation; without the UI in the midst of the
transaction, each transaction would either commit or rollback, and the
second one would wait patiently for the first to finish.  SS7 has automatic
deadlock protection anyway; usually after 5 seconds a "deadlock victim" is
selected.

In the case of a non-indexed table, the Append operation could require a
table lock.. and don't forget that an Intent Exclusive or an Intent Update
lock shuts out other lock requests - and the Intent locks are placed ahead
of time.

To view the current locks, use the system procedure sp_lock, or from
Enterprise Manager under the node for the current server, choose
Management\Current Activity\Locks / Process ID.
--
David Reed
White Plains, Maryland USA

*To reply, remove the no_spam from my email address

Other Threads