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]
