Re: UPDATE and ALTER in transactions do not mix

Baz,

It seems reasonable to you that an UPDATE should keep a row locked even after
it has been completed? In other words, it is reasonable that a row can only
be touched once inside of a single transaction?

In all honesty, it sounds completely bogus to me. I suppose Access97's
implementation of transactions is not all that good. I was reasonably
impressed by Access97's implementation of DDL, and I hope Access2003 will be
even better. Hopefully, their transaction implementation will be better as
well.

I still don't see how it is unreasable to be able to do this within a
transaction. Does anyone else know of the nature of this problem? Any help is
appreciated!

Jonathan Scott


Baz wrote:
>> I am trying to update a system by issuing an UPDATE and then an ALTER
>> statement. I suppose this normally should work no problem; however, inside of
>[quoted text clipped - 4 lines]
>>
>> Jonathan Scott
>
>I can't categorically confirm that this is the case, but it seems pretty
>reasonable. Performing an UPDATE within a transaction will issue a lock
>which will not be released until the transaction is committed, and it seems
>sensible to me that you can't ALTER a table that has a lock on it.


--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-tablesdbdesign/200508/1
.

Relevant Pages

  • Re: Working Transactions somehow started not to work
    ... Transaction and locking are not properties of recordsets but are properties ... Opening a served-based cursor will put a lock on ... If a connection is closed, ... SL> and are not associated necessarily with server-based cursors. ...
    (microsoft.public.access.adp.sqlserver)
  • Re: controlling lock order in transactions
    ... Andrew J. Kelly SQL MVP ... > ROLLBACK TRANSACTION; ... > find in the clients sp that could be causing the dead lock is the join on ...
    (microsoft.public.sqlserver.programming)
  • Re: controlling lock order in transactions
    ... I believe the default isolation level for .net may be ... My transaction ONLY performs INSERTS. ... Yes it will put an exclusive lock on the row being inserted but Serializable ... COMMIT TRANSACTION ...
    (microsoft.public.sqlserver.programming)
  • Re: Tansaction/locking MSSQL2000
    ... >transaction behavior in single statements [not a BEGIN TRANSACTION ... >tables I need to lock so there is not a race later]? ... If the table is already held by an exclusive lock ... When the transaction is finished (due to ROLLBACK/COMMIT, releasing the ...
    (microsoft.public.sqlserver.programming)
  • Re: Question on Locks
    ... The mode and Type of the the lock are important here. ... > Even the default Transaction Isolation Level is Read Committed which means> I should be able to read those records that are already committed, ... >> Use NOLOCK and READPAST sparingly. ... >> If you run the same with the NOLOCK hint, you'll get results instantly ->> but you'll see Clinton being born in 2002, in violation of the business>> rule! ...
    (microsoft.public.sqlserver.programming)