Mysql Innodb不会使用desc顺序返回select的最后一行

Question

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

Answer 1

I think the problem was caused by multiple connections selecting the same row before the insert like following:

php connection 1: select the last offers user_id

php connection 2: select the last offers user_id (give the same one)

php connection 1: insert the offer

php connection 2: insert the offer

I solved the problem by locking the table before select and unlocking it after insert like:

lock table offers write

select the last offer's user_id

if it is different from the current user insert offer

unlock table offers