CODEWITHSUNDEEP
mysqlsqldatabase
lolalola
lolalola

Reputation: 3823

Mysql: Deadlock found when trying to get lock, need remove key?

i count page view statistics in Mysql and sometimes get deat lock.

How can resolve this problem? Maybe i need remove one of key?

But what will happen with reading performance? Or is it not affect?

Table:

CREATE TABLE `pt_stat` (
  `stat_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `post_id` int(11) unsigned NOT NULL,
  `stat_name` varchar(50) NOT NULL,
  `stat_value` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`stat_id`),
  KEY `post_id` (`post_id`),
  KEY `stat_name` (`stat_name`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8

Error: "Deadlock found when trying to get lock; try restarting transaction".

UPDATE pt_stat SET stat_value = stat_value + 1 WHERE post_id = "21500" AND stat_name = 'day_20170111';

Upvotes: 0

Views: 14471

Answers (1)

Chris Travers
Chris Travers

Reputation: 26454

When dealing with deadlocks, the first thing to do, always, is to see whether you have complex transactions deadlocking against eachother. This is the normal case. I assume based on your question that the update statement, however, is in its own transaction and therefore there are no complex interdependencies among writes from a logical database perspective.

Certain multi-threaded databases (including MySQL) can have single statements deadlock against themselves due to write dependencies within threads on the same query. MySQL is not alone here btw. MS SQL Server has been known to have similar problems in some cases and workloads. The problem (as you seem to grasp) is that a thread updating an index can deadlock against another thread that updates an index (and remember, InnoDB tables are indexes with leaf-nodes containing the row data).

In these cases there are three things you can look at doing:

  1. If the problem is not severe, then the best option is generally to retry the transaction in case of deadlock.
  2. You could reduce the number of background threads but this will affect both read and write performance, or
  3. You could try removing an index (key). However, keep in mind that unindexed scans on MySQL are slow.

Upvotes: 1

Related Questions