Others may correct me if I'm wrong, but if you want to maintain your own auto increment
without duplicates like that, then you need to serialize inserts to your table. Which
means either doing an explicit table lock by the process that's inserting, or using innodb
with transactions in the SERIALIZABLE tx isolation mode.
If I were you, I would maintain your incrementing sequence in a separate table.
Prep the table:
CREATE TABLE store_seq ( store_id int unsigned not null, nextid int unsigned not null );
insert into store_seq ( 1, 1 );
Get next id:
update store_seq set nextid=LAST_INSERT_ID(nextid+1) where store_id=1;
This will give you the next id in an atomic way, and avoid replication problems. It means
your insert will take two queries, but that's a small price to pay for correctness.
From: Johnny Withers [mailto:johnny@stripped]
Sent: Monday, March 08, 2010 1:31 PM
To: MySQL General List
Subject: UDF - Sequence Numbers
I have two servers, both running 5.0.77-log, one is setup as a master, the
other as a replication slave.
The database contains a table that holds records of loans for financial
lending stores. This table has an ID column this is defined as auto
increment. There is another column called "store_seq_num" that holds the
sequence number for each loan done in each store. This column needs to work
like the auto-increment field; however, it's value is dependent upon which
store created the loan. Currently there is a UDF called fnNextStoreSeqNum
that returns the next sequence number for the new loan for the given store.
It does this by executing:
SELECT MAX(store_seq_num)+1 AS NextId FROM trans_adv WHERE
It uses the store_seq_num key and explain says "Select tables optimized
away." in the extra column.
The INSERT statement for this table looks something like this:
INSERT INTO trans_adv(store_id,store_seq_num,...)
The problem comes in on the replication server. Sometimes the sequence
numbers do not match the master. The root cause of the problem seems to be
when two loans are created in the same store at the same time (same second
-- or even 2 seconds apart sometimes). The master duplicates the sequence
number and the slave writes the correct sequence numbers. This seems to
happen when the server is under heavy load (600+ queries per sec). I hvae a
feeling it's due to the loan being created in a single transaction;
therefore the sequence number for the "first" loan really didn't exist to
any other connections until COMMIT was issued.
Is there a better way to do these sequence numbers? Should the key be
defined as UNIQUE? If it is defined as UNIQUE how can this key be added to
the existing table that has duplicate sequence numbers?
A partial create table statement is below for the trans_adv table.
CREATE TABLE `trans_adv` (
`id` int(10) unsigned NOT NULL auto_increment,
`store_id` int(10) unsigned NOT NULL default '0',
`store_seq_num` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `store_key` (`store_id`),
KEY `store_seq_num_key` (`company_id`,`store_id`,`store_seq_num`),
) ENGINE=InnoDB AUTO_INCREMENT=3049363 DEFAULT CHARSET=latin1
This message contains confidential information and is intended only for the individual
named. If you are not the named addressee, you are notified that reviewing,
disseminating, disclosing, copying or distributing this e-mail is strictly prohibited.
Please notify the sender immediately by e-mail if you have received this e-mail by mistake
and delete this e-mail from your system. E-mail transmission cannot be guaranteed to be
secure or error-free as information could be intercepted, corrupted, lost, destroyed,
arrive late or incomplete, or contain viruses. The sender therefore does not accept
liability for any loss or damage caused by viruses or errors or omissions in the contents
of this message, which arise as a result of e-mail transmission. [FriendFinder Networks,
Inc., 220 Humbolt court, Sunnyvale, CA 94089, USA, FriendFinder.com