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".. :)
* 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.
* 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.
* 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.
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. ;)
Cheers,
Mark
--
Mark Leith
MySQL Regional Support Manager, Americas
Sun Microsystems, Inc., http://www.sun.com/mysql/
_______________________________________________
Mailing list: https://launchpad.net/~drizzle-discuss
Post to : [email protected]
Unsubscribe : https://launchpad.net/~drizzle-discuss
More help : https://help.launchpad.net/ListHelp
