Maybe MyISAM is still a better choice for this use...? For MyISAM and BDB tables you can specify AUTO_INCREMENT on a secondary column (or three columns in your case) in a multiple-column index. In this case, the generated value for the AUTO_INCREMENT column is calculated as MAX(auto_increment_column)+1 WHERE prefix=given-prefix. This is useful when you want to put data into ordered groups.
See the link: http://dev.mysql.com/doc/mysql/en/example-AUTO_INCREMENT.html Ed -----Original Message----- From: Fagyal Csongor [mailto:[EMAIL PROTECTED] Sent: Friday, September 10, 2004 4:53 AM To: [EMAIL PROTECTED] 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]
