Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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
share|improve this question
3  
If a user should not gain more than one offer per campaign, you can add a unique index on (campaign_id, user_id). –  ypercube May 3 '13 at 14:14
add comment

1 Answer

Transaction isolation levels and database locking can make it very difficult to handle the problem you've described.

Hopefully ypercube's suggestion does the trick.

If it's valid to have multiple (campaign_id, user_id) combinations, just not within seconds of each other, you might want to consider adding a column with the user's web/application session id, and putting a unique index on (campaign_id, user_id, session_id).

This would prevent a user from being able to enter the same data during a single login session - whether that's useful or not really depends on your application. You might be able to come up with some other unique index that serves your purposes.

It might also be worth trying to prevent data being submitted twice from the client side. You'll see that sort of behaviour all through StackExchange, for example - the interface changes in such a way that you can't actually hit the same button twice.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.