After some tinkering around on one of my test database servers with this
sequence table design; I see what you mean.
Defining the table with store_id and seq_id columns allows me to UPDATE
table SET seq_id=LAST_INSERT_ID(seq_id+1) WHERE store_id=N and will block
any further updates on store_id=N to that table until the entire transaction
block is COMMIT'd. However, it does allow me to issue the same statement
with store_id=X and is not blocked.
I thought this design would allow for gaps in the sequence numbering, but it
works great.
Thanks!
On Mon, Mar 8, 2010 at 4:10 PM, Gavin Towey wrote:
> 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;
> select LAST_INSERT_ID();
>
> 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.
>
> Regards,
> Gavin Towey
>
> -Original Message-
> From: Johnny Withers [mailto:joh...@pixelated.net]
> 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
> trans_adv.store_id=N;
>
> 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,...)
> VALUES(fnNextStoreSeqNum(10),10,);
>
> 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
> ;
>
>
>
> --
> -
> Johnny Withers
> 601.209.4985
> joh...@pixelated.net
>
> 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
>
