Kathryn, ----- Original Message ----- From: "Kathryn Cassidy" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Friday, January 03, 2003 1:14 PM Subject: built in commands are atomic, right?
> Hi there, > > This is probably a rather foolish question, but I can't find a definitive > answer anywhere in the docs and I don't like assumptions... > > Are all built in mysql commands atomic? In particular, is replace > atomic. In the docs it says that it deletes the entry if it exists > then inserts it, is that actually what it does (ie, is replace just > a macro for delete followed by insert)? > > I'd imagine it is atomic, but I was wondering if anyone has a definitive > answer for me. yes, REPLACE is 'atomic'. The word 'atomic' has many meanings. One is that no one can see a half-completed operation. If your table is MyISAM, MySQL locks the entire table with an exclusive lock for the duration of the REPLACE operation. If your table is InnoDB, the procedure is this: 1. Try the insert. If it succeeds, good. 2. If the insert does not succeed because of a UNIQUE KEY violation, set a shared lock on the conflicting index record. That guarantees the record is there when we come to step 3. 3. Perform either an update or a delete + insert of the conflicting row. These operations in InnoDB automatically set exclusive locks on the index records they look at or change. Since InnoDB in step 3 sets an exclusive lock on every index record which it modifies, no one can see a 'half replace'. For the same reason as for REPLACE, every SQL statement in MySQL is 'atomic'. That is essential for replication to work. The execution of the SQL statements must keep the master and the slave identical, though in the master the statements are processed concurrently, while the slave processes them in the serial order of the binlog. We say that the execution of SQL statements is 'serializable', and the serialization order is the order in which they are written to the binlog. > Thanks, > Kathryn. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
