Mark Leith wrote:
OK I'll nibble. :)
On 6 Aug 2009, at 13:49, Jim Starkey wrote:
At the expense of beating a (hopefully) dead horse, there are many
theoretical problems with multi-table update, particularly the type
where one or more tables is actually changed. Among the problems:
* Are triggers fired for update tables referenced but not changed?
Depends on the type of trigger. FOR EACH ROW - no, FOR EACH STATEMENT
- yes. MySQL only supports FOR EACH ROW, so theoretically - no.
Drizzle supports none, so "who cares".. :)
Uh, Mark, that exactly how dozens -- yea hundreds -- of terrible ideas
get propagated. My favorite really bad idea is to handle assignment
overflow by assigning the largest value thats fits in lieu of an error.
Mark, if you know for certain that a "feature" is going to conflict with
the standard and/or common sense, don't do it.
* Does a record in a table referenced but not changed get an update
lock (or equivalent)?
If thinking along the lines of InnoDB it would get a next key lock on
every record it touches (whether it updates it or not). That's pretty
much the same as for a single table update as well. I like the Falcon
model of only locking records updated as well though, when you can
afford to not have to deal with SBR etc. ;) Of course, those logging
requirements are going to be entirely different with Drizzle.
If drizzle is going to support pluggable engines (isn't that a basic
requirement?), it shouldn't be tied to the implementation of any single
engine.
Falcon, in fact, makes a major distinction between select and select for
update. Depending on transaction mode, a select for update can fail or
block, but a simple select will always succeed. And, as should be
obvious, a select for update is *much* more expensive that a simple
record fetch. So there are two alternatives: Bum out select for update
for records not actually being modified and trash transaction semantics,
or do a dummy update that ruins performs and induces false update
conflicts or transaction deadlocks.
That's a lousy choice. Both are bad. And the only real motivator is to
do something sleazy with non-transactional engines that isn't reliable
under the best of circumstances.
The better choice is to dump multi-table update and fix sub-queries. A
decent sub-query optimization is intrinsically faster than a bogus
multi-table update.
* Does a multiple table update block on a record that won't be
changed but write locked by another transaction?
Depends on the transactional model again eh. :) Ideally - no.
Why not? A record updates by a transaction has to stay updated.
Eliminating the update changes transaction semantics.
* Does a "non-update" to a referenced but not changed table block
other transactions?
And.. yet again..
* Is a record that occurs two or more times in a join updates
multiple times?
Well, the standard does touch on this in some respects iirc (although
not specifically for multi-table DML, but things like updatable views
etc.), and I think it says that you should buffer all rows that would
be affected up front, and then do the changes in a single pass. I'd
have to dig around to find my vague recollections of that though.
The interaction of this with triggers is pretty awful.
Triggers, ladies and gentlemen, are wonderful. The SQL standard version
stinks and isn't worth implementing. Decent triggers are sublime.
Please don't write them off because you don't understand them.
I don't think the concept of multi-table update has been thought
through. It may have seemed like an easy hack for a non-transaction
ISAM, but folded into rigorously defined transactional frameworks,
it's a mess.
It's also non-standard. I don't generally object to non-standard
extensions (I'm a repeat offender myself), but I draw the line at
extensions that are essentially unsound in conception.
Oh we should certainly just throw it away then instead of improving
upon it. ;)
You should either come up with good answers to these questions or give
it up. Frankly, I don't think the feature can be saved. There are
better, easier, and faster ways to do the same thing.
--
Jim Starkey
Sent from Shearwater, off
the coast of New England
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
