MySQL lock wait timeout and deadlock errors

Question

I'm developing a mobile application whose backend is developed in Java and database is MySQL.

We have some insert and update operations in database tables with a lot of rows (between 400.000 and 3.000.000). Every operation usually doesn't need to touch every register of the table, but maybe, they are called simultaneously to update a 20% of them.

Sometimes I get this errors:

Deadlock found when trying to get lock; try restarting transaction

and

Lock wait timeout exceeded; try restarting transaction

I have improved my queries making them smaller and faster but I still have a big problem when some operations can't be performed.

My solutions until now have been:

Increase server performance (AWS Instance from m2.large to c3.2xlarge)
SET GLOBAL tx_isolation = 'READ-COMMITTED';
Avoid to check foreign keys: SET FOREIGN_KEY_CHECKS = 0; (I know this is not safe but my priotity is not to lock de database)
Set this values for timeout variables (SHOW VARIABLES LIKE '%timeout%';):
- connect_timeout: 10
- delayed_insert_timeout: 300
- innodb_lock_wait_timeout: 50
- innodb_rollback_on_timeout: OFF
- interactive_timeout: 28800
- lock_wait_timeout: 31536000
- net_read_timeout: 30
- net_write_timeout: 60
- slave_net_timeout: 3600
- wait_timeout: 28800

But I'm not sure if these things have decreased performance.

Any idea of how to reduce those errors?

Note: these others SO answer don't help me:

MySQL Lock wait timeout exceeded

MySQL: "lock wait timeout exceeded"

How can I change the default Mysql connection timeout when connecting through python?

How do you connect to the databse? Which driver do you use? Do you use a connection pool? — Eduard, Jul 02 '15 at 13:37

Davide Lorenzo MARINO · Accepted Answer · 2015-07-02T13:43:15.993

1

Try to update less rows per single transaction.

Instead of updating 20% or rows in a single transaction update 1% of rows 20 times.

This will improve significantly your performances and you will avoid the timeout.

Note: ORM are not the good solution for big updates. It is better to use standard JDBC. Use ORM to retrieve, update, delete few records each time. It speed up the coding phase, not the execution time.

edited Jul 02 '15 at 13:43

answered Jul 02 '15 at 13:41

Davide Lorenzo MARINO

26,420
4
39
56

I'm sure this can help me, but, how to filter that? I mean, if I have to update 50.000 row of a table with 110.000 because those match with my criteria, how can I divide this query? – Isma Row Jul 02 '15 at 13:42
You have to choose a sort criteria. A good one is the id, but you can choose anyone you like – Davide Lorenzo MARINO Jul 02 '15 at 13:45
ORDER BY id DESC, for example? – Isma Row Jul 02 '15 at 13:49
I don't know exactly the source of modified data. If the source is another table you can sort the modified data by id. If the source of modified data is a file you can select contiguous group of rows. If the source of data is a java algorithm loop grouping the results. – Davide Lorenzo MARINO Jul 02 '15 at 13:52
Ok! I'm going to try it. Maybe I can divide the whole query in several by using `LIMIT x, y` – Isma Row Jul 02 '15 at 14:14
Yes if the source of modified data is a query you can split it with limit – Davide Lorenzo MARINO Jul 02 '15 at 14:15

score 0 · Answer 2 · answered Jul 06 '15 at 14:35

As a comment more than an answer, if you are in the early stages of development, you may wish to consider whether or not you actually need this particular data in a relational database. There are much faster and larger alternatives for storing data from mobile apps depending upon the planned use of the data. [S3 for large files, stored-once, read often (and can be cached); NoSQL (Mongo etc) for unstructured large, write-once, read many, etc.]

MySQL lock wait timeout and deadlock errors

2 Answers2