We have a table scheme like following:
CREATE TABLE IF NOT EXISTS `offers` ( `id` int(11) NOT NULL AUTO_INCREMENT, `campaign_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `price` double NOT NULL, `ip` varchar(15) NOT NULL, `cdate` int(11) NOT NULL, PRIMARY KEY (`id`), KEY `campaign_id` (`campaign_id`,`price`) ) ENGINE=InnoDB DEFAULT CHARSET=latin5 AUTO_INCREMENT=190514 ;
On each new offer by the user we check if the last order is given by the same user with:
"select user_id from offers where campaign_id='".$campaign['id']."' order by id desc limit 1"
if the user_id is same we prevent the new offer to protect the user from accidental double clicks.
if there is not any problem with the offer we insert the offer with:
"insert into offers(campaign_id,user_id,price,ip,cdate) values (".$campaign['id'].",".$user['id'].",'".$price."','".$_SERVER['REMOTE_ADDR']."',".time().")"
But the problem is the select only returns the last inserted row after about 1 second. That means the user can insert multiple offers if they click the button too fast.
We use 5.5.30-30.2-log Percona Server as our database server. Following is our my.cnf file:
[mysqld] datadir = /var/lib/mysql tmpdir = /var/lib/mysqltmp socket = /var/lib/mysql/mysql.sock skip-external-locking = 1 skip-name-resolve open-files-limit = 40000 max_heap_table_size = 64M tmp_table_size = 64M log-error = /var/log/mysqld.log thread-cache-size = 50 table-cache = 4096 table-open-cache = 4096 table-definition-cache = 512 query-cache-size = 0 query-cache-limit = 16M query-cache-type = 0 sort-buffer-size = 1M read-buffer-size = 1M read-rnd-buffer-size = 8M join-buffer-size = 1M tmp-table-size = 64M max-heap-table-size = 64M back-log = 100 max-connections = 10000 max-connect-errors = 10000 max-allowed-packet = 256M interactive-timeout = 360 wait-timeout = 360 innodb = FORCE key-buffer-size = 32M myisam-sort-buffer-size = 4M innodb-buffer-pool-size = 60G innodb-log-file-size = 256M innodb_log_files_in_group = 2 innodb-log-buffer-size = 4M innodb-file-per-table = 1 innodb-thread-concurrency = 8 innodb-flush-log-at-trx-commit =2 server-id = 1 slow-query-log = 1 slow-query-log-file = /var/lib/mysqllogs/slow-log