Csongor, in InnoDB, it is better to use
SELECT ... FOR UPDATE to lock the result set of a SELECT. 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. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking 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 technical support from https://order.mysql.com/ ----- Original Message ----- From: "Fagyal Csongor" <[EMAIL PROTECTED]> Newsgroups: mailing.database.myodbc Sent: Friday, September 10, 2004 1:55 PM Subject: Dump question: transactions vs. locking > Hi, > > I think I have some problems understanding how transactions work, and > how they relate to locking. Can someone please explain? > The question is this: > > I have a table where I have a column into which I insert numbers in > sequencial order - it is not set to auto_increment, though, because the > field is only unique with two other columns. The fields are: > "mainid","subid" and "userid" (in the table "invoices"). > Before I changed to InnoDB, using MyISAM I used to lock the table, get > MAX(subid), do an insert, then unlock. It was something like this (this > is Perl): > > # lock the table > $dbh->do('LOCK TABLES invoices WRITE'); > # next subid is MAX(subid) > my ($nextsubid) = $dbh->selectrow_array('SELECT MAX(subid) FROM invoices > WHERE userid=? AND mainid=?', undef, $userid, $mainid); > # increment by 1 > $nextsubid++; > # insert all stuff with new $subid > $dbh->do('INSERT INTO invoices ....'); # set subid here > # unlock > $dbh->do('UNLOCK TABLES'); > > So what if I change to transactions? Should I simply just "substitute" > LOCK/UNLOCK by BEGIN/COMMIT, or should I still use LOCK/UNLOCK? If the > later, what is the correct order: BEGIN and then LOCK, or LOCK and then > BEGIN? Also, am I right that as in transactional mode I always need a > commit, I MUST use BEGIN/COMMIT, and cannot keep the original simple > LOCK/UNLOCK? > > Thank you, > - Csongor > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
