Hi Ed,
Wow, thank you! MySQL keeps surprising me :-) INSERT ... ON DUPLICATE KEY UPDATE ... was the first in the list ;-)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
However, I still need InnoDB, though, as I am doing deletes on multiple tables, of which I am scared :-)
Reagards, - Csongor ps: Obviously this was a "dumb" question, not a "dump" one :-)
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]
