UPDATE waits for lock too long after SELECT in MySQL (Innodb)

Question

I have PHP function Pics::getPicture (yii2 ORM) which selects one row from table and then updates one field from this row. The table structure contains only id (PK), path (VARCHAR(500)) and seen (int(1)). Seen column is indexed.

My pseudo code:

SELECT * FROM pics WHERE id=:id LIMIT 1;
UPDATE pics SET seen=1 WHERE id=:id LIMIT 1;

And when client requests 10+ pictures in parallel (for different Id) then some UPDATE have unexpectedly hung for 1-10 seconds. I see Innodb_row_lock_waits is increased every time in this case.

Without UPDATE my code works very fast all time.

I've tried use transaction, UPDATE DELAYED (I'm not sure that I tried it correctly).

Are there some best practices for selecting and updating same rows? What additional information should I provide to clarify the question?

UPDATE 1 Server Configuration

Server: CPU: 1.5 Ghz, 1 Core, RAM 8Gb
Debian: 7.8
MySQL: 5.5.41

UPDATE 2

I've changed the SQL code to this but situation did't change. Also I removed INDEX for 'seen' column without any effects to this code.

BEGIN;
SELECT * FROM pics WHERE id=:id LIMIT 1 FOR UPDATE;
UPDATE pics SET seen=1 WHERE id=:id LIMIT 1;
END;

In mysql-slow.log I see next entries:

# Time: 150210  2:03:24
# User@Host: user[user] @ localhost [127.0.0.1]
# Query_time: 13.011485  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
SET timestamp=1423530204;
commit;
# Time: 150210  2:03:34
# User@Host: user[user] @ localhost [127.0.0.1]
# Query_time: 9.765468  Lock_time: 0.000037 Rows_sent: 0  Rows_examined: 1
SET timestamp=1423530214;
UPDATE `pics` SET seen=1 WHERE id=315 LIMIT 1;

UPDATE 3

Amount of rows in pics table are about 70.

SHOW CREATE TABLE pics;

CREATE TABLE `pics` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `image` char(255) CHARACTER SET latin1 NOT NULL DEFAULT '',
 `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
 `archive` tinyint(1) NOT NULL DEFAULT '0',
 `seen` tinyint(1) NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 KEY `tbl_picture_timestamp` (`timestamp`),
 KEY `tbl_picture_archive` (`archive`),
 KEY `tbl_picture_seen` (`seen`),
) ENGINE=InnoDB AUTO_INCREMENT=319 DEFAULT CHARSET=utf8

SELECT COUNT(1) RowsReturned FROM pics WHERE id=315; (It took about 0.000035s)

RowsReturned 1

Answer 1

You are too focused on the details; back off. Let's look at the big picture, the benchmarking, the indexing, the transactions, etc.

How many simultaneous users are you benchmarking for? How many do you expect in reality? How many cores does your CPU(s) have? What version of MySQL are you running?

My points are: (a) The benchmark is stressing the limits, not looking for reality; (b) Oracle has made great strides recently in handling more connections.

When you go beyond the effective connection limit, latency of queries will suffer terribly. So, don't benchmark beyond that. Furthermore, throttle the users so that not "too many" get to MySQL 'simultaneously'. In older versions, it was so bad that (a) throughput would go down as you add more clients, and (of course) (b) latency would go through the roof. Now, throughput plateaus while latency climbs.

For a single item, be sure to wrap Rolando's SQL in a transaction:

BEGIN;
SELECT * FROM pics WHERE id=:id LIMIT 1 FOR UPDATE;
UPDATE pics SET seen=1 WHERE id=:id LIMIT 1;
COMMIT;

For handling more than one id at the same time, you should sort the ids to help avoid deadlocks. Then do them in a single transaction:

BEGIN;
SELECT * FROM pics WHERE id IN ($id_list) FOR UPDATE;
UPDATE pics SET seen=1 WHERE id IN ($id_list) LIMIT 1;
COMMIT;

You can simplify the code more: Do the

UPDATE...
Check rows_affected; exit if 0
SELECT ...

Since rows_affected is local to the 'session', you can discover whether the UPDATE grabbed the row. Note that there is no need for transactions (as far as this code snippet goes), and autocommit=1 would suffice.

Let me point out another issue with the design: Indexing a flag (seen) has two problems (a) The optimizer is unlikely to use the index, due to low cardinality; and (b) the update has to remove a row from that index and add a new row elsewhere; this is costly.

The logic you described does not seem to need INDEX(seen); does something else need it? If not, DROP that INDEX; that may solve the problem.

Answer 2

Perhaps you should impose a SELECT FOR UPDATE

SELECT * FROM pics WHERE id=:id LIMIT 1 FOR UPDATE;
UPDATE pics SET seen=1 WHERE id=:id LIMIT 1;

I have mentioned this before

• Mar 08, 2013 : How are DB locks tied to connections and sessions?
• Mar 12, 2013 : How I prevent deadlock occurrence in my application? (See MECHANISM #3)

UPDATE 2015-02-05 22:22 EST

There will always be row locks imposed, and there is no getting around it. Yet what is the difference between your code and my code ?

Think about it. Your running the SELECT in your code does not shield the rows from changes. Those row locks you are seeing would happen during the UPDATE. My code imposes the locks on rows and all corresponding index entries during the SELECT. This makes the UPDATE have less stress.

This is in agreement with the MySQL Documentation

A SELECT ... FOR UPDATE reads the latest available data, setting exclusive locks on each row it reads. Thus, it sets the same locks a searched SQL UPDATE would set on the rows.

UPDATE 2015-02-10 22:05 EST

Your issue seems to be pointed out already by @RickJames: the index on seen field.

Take a look at the InnoDB Architecture (Picture From Percona CTO Vadim Tkachenko)

Please note two structures from the Picture

• Insert Buffer With the InnoDB Buffer Pool (IB_IBP)
• Insert Buffer With the System Tablespace (better known as ibdata1) (IB_SYSTBLSPC)

These structures are responsible for updating nonunique indexes.

Now, please note the effect of setting seen=1 on id = 315.

Assuming seen is either 0 or 1 (Cardinality of 2)

• Having an index on seen with just 0 and 1 as values makes the index no different than managing two linked lists, each ordered by the PRIMARY KEY id.
• Changing a 0 to 1 (or 1 to 0) will place information into IB_IBP
• When it time to write the change to pics.ibd
  • Copy of the Changed Data Page is Written to the Double Buffer
  • Information Needed for Index Positioning is passed from IB_IBP into IB_SYSTBLSPC
• The index entry of id 315 is moved from the side of the index where seen=0 to the other side of the index where seen=1

If you are changing the archive column in your benchmarking, these same machinations would also apply to the archive index.

In addition to the indexes being churned into and out of the InnoDB Buffer Pool, don't forget where this question started: ALL THE ROW LOCKS.

Keep something in minds about row locks. When a row lock is issued, an entire page can also be locked. The page can be the location of multiple primary key values. I have another posts where I discussed page locks (Dec 31,2012 : Queries getting stuck on very simple COUNT queries)

Please note in your comment, that you said

And what is more important that in the most cases it works fast

Why not all cases ? Because of the reasons I just mentioned. @RickJames did mention first the seen index, and he is right to suggest removing that index. Along those same lines, I suggest removing the archive index as well (especially if the archive value is being changed in the benchmark). Without removing those indexes, look at all the stress you are imposing on the InnoDB Infrastructure even though the table has 70 rows. It would be far worse with thousands or millions of rows.

Answer 3

Row locks are held for the duration of a transaction. If there is contention, one of the first things you should do is make sure the code path between updating the row and committing is as tight as possible.

Useful diagnostics to be able to see these locks:

• SHOW CREATE TABLE pics
• SHOW ENGINE INNODB STATUS
• SELECT * FROM information_schema.innodb_trx

Index structure affects locking (hence SHOW CREATE TABLE).

SHOW ENGINE INNODB STATUS and innodb_trx will show information about which row locks are conflicting, and which statements.