2013-05-03 13:37
浏览 88

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

We have a table scheme like following:

  `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`)

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:

datadir                         = /var/lib/mysql
tmpdir                          = /var/lib/mysqltmp
socket                          = /var/lib/mysql/mysql.sock
skip-external-locking           = 1
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
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

4条回答 默认 最新

  • doujiang2641
    doujiang2641 2013-05-03 19:48

    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

    点赞 评论
  • douzhang1926
    douzhang1926 2013-05-03 13:41

    MySQL query caching.

    MySQL will check on the first click and return an empty result, then it will insert the record, then each subsequent click MySQL returns the cached value of the empty result and allows another insert.

    We had something similar happen on a site I was working on with requests very close together.

    We ended up having to run this first

    SET SESSION query_cache_type=0;
    点赞 评论
  • douhao6271
    douhao6271 2013-05-03 14:14

    Did you tried to use SQL Triggers on Update / Insert events to check for instance if

    SELECT count(*) FROM `offers` 
    WHERE campaign_id = [Your_value] AND user_id = [Your_value];

    is equal to 0, and act according to it. (If already existing, don't do any modification [...])

    Edit : By the way, it seems more an Ajax / Php / whatever you're using problem rather than an incorrect use of the MySQL database. You should prevent the user from sending many request in a short interval.

    点赞 评论
  • douqiong8412
    douqiong8412 2013-05-03 15:00

    Have you tried explicitly wrapping your insert into a transaction? Doing something like:

    INSERT ...
    SELECT ...

    This should guarantee that SELECT will return the last inserted data.

    More on InnoDB isolation levels: [1] and [2]

    点赞 评论