On 4 Aug 2009, at 22:50, Sheeri K. Cabral wrote:
On Tue, Aug 4, 2009 at 5:37 PM, Mark Leith<[email protected]> wrote:
Mark -- I agree with your 2 points, but I believe that
multi-update/delete isn't necessary if subqueries work properly and
efficiently. For example: DELETE FROM one_tbl WHERE stuff IN
(....).
From what I see, multi-update and multi-delete are used to change
information in *one* table, but the rows to change are determined by
using more than one table. In that case, if/when subqueries work
with
proper efficiency, the real issue of "I want to easily change
information in one table based on information in more than one
table"
will be solved -- appropriately and non-buggily.
Have you found that multi-update/delete is used to actually change
information in more than one table? All the implementations of
*that
usage* that I've seen in the wild have been instead of using foreign
keys and cascading properly.......
Yes I have - plenty. Foreign keys aren't in all storage engines.. ;)
Neither are transactions, and you mentioned that "2) It's extremely
useful and convenient *not* to have to do this in a transaction" --
implying that you would do it in a transaction if you didn't have the
multi-update/delete. What did you do before multi-update/delete was
available? If you could do it in a transaction in MySQL, you can do
it with foreign keys too (unless you were actually using BDB).
I don't believe that multi-update/delete is implemented in MySQL in a
way that's useful to avoid locking scenarios that would cause issues
in the use cases that Drizzle is designed for. That's more of a
feeling (I used the word "believe") than actual knowledge.
Well, you see, this is one of the major benefits to the statement -
with a non-transactional engine that does not support foreign keys,
you have to write lock all of the tables up front, and modify them as
a unit, and then unlock, if you want to guarantee some form of
consistency. The convenience is the same (stops you either calling
LOCK TABLES or START TRANSACTION and running multiple statements which
all have to be parsed, executed, etc.).
Then start mixing that with transactional and non-transactional
tables.. :)
Doing everything as a single unit in this case, does indeed make more
sense than not when you can not be sure of the transactional and/or
referential integrity capabilities of engines, imho.
Cheers,
--
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
