Csongor, ----- Alkuperäinen viesti ----- Lähettäjä: "Fagyal Csongor" <[EMAIL PROTECTED]> Vastaanottaja: "Heikki Tuuri" <[EMAIL PROTECTED]> Kopio: <[EMAIL PROTECTED]> Lähetetty: Monday, September 13, 2004 3:36 PM Aihe: Re: Dump question: transactions vs. locking
> Hi Heikki, > > >Csongor, > > > >in InnoDB, it is better to use > > > >SELECT ... FOR UPDATE > > > >to lock the result set of a SELECT. > > > > > Thank you, I think I will go with this one. > > >A plain SELECT in InnoDB is a consistent, non-locking read that reads a > >snapshot of the database at an earlier time. It does not lock anything. > > > But it does allow a LOCK during a transaction, doesn't it? Or is this > practice not recommended? the behavior in >= 4.0.20 and >= 4.1.3 is that when you call LOCK TABLES ... it sets a table lock both in MySQL and also inside InnoDB. Note that LOCK TABLES also implicitly commits the current transaction. When you call COMMIT that releases the InnoDB table and row locks, but does NOT release the MySQL table locks. To release the MySQL table locks, you have to call UNLOCK TABLES. Actually, also UNLOCK TABLES implicitly commits the current transaction. Since the rules for LOCK TABLES are as complex as this, it is usually better to avoid that command with transactions. A sensible use of LOCK TABLES might look like this: SET AUTOCOMMIT=0; #Let us process a transaction LOCK TABLES t WRITE, s READ; #Lock all tables we are going to use; note that this command may also fail SELECT * FROM s LOCK IN SHARE MODE; #We must use a 'locking read' to see the latest data <calculate a new row for t based on what we read from s> INSERT INTO t VALUES (...); COMMIT; UNLOCK TABLES; Above we have enclosed a transaction inside LOCK TABLES ... UNLOCK TABLES. Be prepared for lock wait timeout errors and deadlocks when issuing LOCK TABLES. It is NOT guaranteed to succeed every time. > Regards, > - Csongor Best regards, Heikki Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL support from http://www.mysql.com/support/index.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
