Re: UPDATE and ALTER in transactions do not mix

"Jonathan S via AccessMonster.com" <forum@xxxxxxxxxxxxxxxxx> wrote in
message news:5254E99C21B04@xxxxxxxxxxxxxxxxxxxx
> 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
> a transaction I always get that the table is in use by another user.
>
> Can anyone verify that UPDATE followed by an ALTER statement, inside of a
> transaction, would cause the "table in use by another user" error?
>
> Jonathan Scott
>
>
> --
> Message posted via http://www.accessmonster.com

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.


.

Relevant Pages

  • Re: "Invalid Cursor Error"
    ... ALTER TABLE is fine as long ... >> problem is and spending money on this seems absurd. ... Just plain simple Enterprise Manager. ... > SET TRANSACTION ISOLATION LEVEL SERIALIZABLE ...
    (microsoft.public.sqlserver.server)
  • UPDATE and ALTER in transactions do not mix
    ... I am trying to update a system by issuing an UPDATE and then an ALTER ... a transaction I always get that the table is in use by another user. ... Prev by Date: ...
    (microsoft.public.access.tablesdbdesign)
  • Re: UPDATE and ALTER in transactions do not mix
    ... their transaction implementation will be better as ... >> I am trying to update a system by issuing an UPDATE and then an ALTER ... >> Jonathan Scott ... >sensible to me that you can't ALTER a table that has a lock on it. ...
    (microsoft.public.access.tablesdbdesign)
  • Re: UPDATE and ALTER in transactions do not mix
    ... > Jonathan Scott via AccessMonster.com wrote: ... >> be touched once inside of a single transaction? ... > What locking strategy are you using? ... Of course it works the other way round: the ALTER doesn't take out any ...
    (microsoft.public.access.tablesdbdesign)
  • Re: How to Halt a Schema Change
    ... if I use ALTER TABLE it will run in a flash? ... The table is probably huge,and by doing it via SSMS has an impact as SQL ... It's now rolling back, according to Activity Monitor. ... If you stop a transaction it will probably take as long to roll back as ...
    (microsoft.public.sqlserver.server)