Re: UPDATE and ALTER in transactions do not mix

"Jamie Collins" <jamiecollins@xxxxxxxxxx> wrote in message
news:1123226123.607617.250400@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
>
> Jonathan Scott via AccessMonster.com wrote:
> > 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?
>
> What locking strategy are you (or think you are) using?
>

It isn't a question of locking strategies, it's a question of how
transaction protection works. When you do an UPDATE inside a transaction,
the record HAS to stay locked until the transaction is committed.

> Sounds reasonable to me, especially considering you can legitimately do
> things the other way around i.e. ALTER before UPDATE.

Of course it works the other way round: the ALTER doesn't take out any
locks. But, if SOMEONE ELSE had the table open at the same time, I'm pretty
sure that the ALTER would fail.

> What kind of
> application are you writing that mixes DML and DDL in the same
> transaction?

Good question!


.

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)
  • 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
    ... > 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. ... sensible to me that you can't ALTER a table that has a lock on it. ...
    (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)
  • Re: Add a coulm in a table, after a specific column
    ... should be one big transaction. ... That or just restore a backup if there ... o Remove all "go" in the script, and wrap most statements in EXEC. ... before the ALTER TABLE statement put always some ...
    (comp.databases.ms-sqlserver)