InnoDb update deadlock

390 Views Asked by At

I have a 10 millions rows innodb-table.

CREATE TABLE IF NOT EXISTS `books` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `t` text NOT NULL,
  `book_id` int(8) NOT NULL,
  `key` varchar(7) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `key` (`key`),
  KEY `book_id` (`book_id`)
) 

At the beginning, columns book_id and key are zero/empty, and then it filling with query:

update books set book_id='123213', `key`='SOME_VARCHAR' where book_id='0' limit 1

And sometimes I have hard traffic on site and deathlocks on this query. Is there any solution for this or advice? Or maybe I need two tables for this.

p.s. Percona MySQL 5.5 with buffer pool size 3G, log size 512M. Dedicated server with Ubuntu with 8Gb RAM.

0

There are 0 best solutions below