Jonathan Scott via AccessMonster.com said:
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
I suggest that you keep your hair on and go back to read what I actually
said, instead of putting words into my mouth.
Of course it would not be reasonable for an UPDATE to keep a row locked even
after it has been completed. BUT, when you do an UPDATE within a
transaction, the UPDATE is NOT completed until the transaction is committed.
That's the WHOLE POINT of transactions: ALL the UPDATES (or whatever) get
done, or NONE of them, and the only way that can work is if the UPDATEs are
not actually applied to the database until the transaction is committed.
This further implies that the affected rows have to be locked until the
transaction is committed, otherwise if someone else updated one of them
before you committed your transaction, then complete chaos would result.
Neither would it be reasonable if a row could only be touched once inside of
a single transaction. If you begin a transaction, YOU can update a row as
many times as you like within that transaction, because YOU have the lock.
However, as soon as you update it once, no-one else can update it until you
commit your transaction (which is why you should never have user actions
within the scope of a transaction, 'cos someone is sure to go to lunch
leaving the transaction uncommitted and records locked).
So far this is a noddy-and-big-ears-learn-about-transactions class. This
behaviour is not peculiar to Access (it's exactly the same in, say, SQL
Server), and nor is it indicative of anything being "not all that good":
logically, it's the only way it can work in order to (i) make transactions
function whilst (ii) ensuring that all users have a consistent view of the
data. So, before you start libelling people (or Access) with words like
"bogus", I suggest that you learn the basics of transaction protection.
Now, as for your specific problem, if you were to issue a second UPDATE on
the same row, that would indeed work just fine. However, you are not
issuing a second UPDATE, you are trying to ALTER the design of the table. I
suggest that you try this: (i) open a form that is bound to a table and then
(ii) open the table in design view, and try to change the design of the
table. You can't do it, can you? Quite reasonably so, too: just imagine
the chaos that would ensue if you could go around changing the design of the
table whilst people were using it. But, this is EXACTLY what you are trying
to do in your transaction. When you try to change the table design, Access
detects that there is a lock in place on a row in the table, and quite
reasonably takes this as evidence that someone is using the table, hence you
can't change it's design. It doesn't seem to me to be relevant that the
person (i.e. you) who took out the lock is the same person who is trying to
change the design because it would be just too unreasonable to expect Access
to work out all the possible ramifications of all possible table design
changes in combination with all possible data updates and STILL be able to
reliably commit the transaction. For example, if you WERE able to change
the design then you might delete a field that you had just updated, putting
the database engine into an impossible position when it comes to committing
the transaction.
If you really want to solve this problem, I suggest that instead of bitching
about Access or about the (good) information you have been given, you
explain what it is you are trying to achieve in functional terms, and see if
anyone can suggest an alternative approach.
