Re: UDF - Sequence Numbers
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 gto...@ffn.com 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 -- - Johnny Withers 601.209.4985 joh...@pixelated.net
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
RE: UDF - Sequence Numbers
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
