Bo Yi Bo Yi - 8 days ago 5
MySQL Question

MySQL concurrent insert, why does it happen and what is the best approach to deal with it?

I'm currently maintaining few projects dealing with vehicle tracking that were written by previous developers.
I've found that sometimes the system gets stuck for about a minute and than continues its job, and we've been trying to find the reason for quite a long time.

i could see "Lock wait timeout exceeded; try restarting transaction" in error.log and in the moment when this happens I always see two queries like these running in MySQL:

Insert into idling_events (gps_position_id, vehicle_id) values (23083665777 ,'46881')


and

insert into idling_events (gps_position_id, vehicle_id, created_at, updated_at) values (23083665761, null, null, null)


Both queries had always been running for the same number of seconds (so they started at the same time). One is executed by the project I'm maintaining and another one is hit by the legacy project that is doing similar job (but i guess it doesn't make any difference, as the same thing might probably happen even if the queries were hit from different threads of same project).

I tried to google and it seems that when two inserts into the same table happen at the same time, mysql might get dead locked. I don't understand why would it be like this? Why doesn't mysql put two queries in queue and execute them one by one in random order?

the isolation level we are using is REPEATABLE READ. I saw some recommendations to change it to READ COMMITTED on Stackoverflow, but i'm not sure it would solve our problem and also it might break the replication, as I understood.

the structure of the table is the following:

id bigint(14) unsigned, not null, primary key, auto_increment
gps_position_id bigint(14) unsigned, not null, key: mul
vehicle_id int(11), can be null, key: mul
created_at datetime, can be null
updated_at datetime, can be null


table is using innodb.
we also have gps_position_index and vehicle_index in this table. I wonder if the problem could be related to the fact we have indexes on the table?

the method we use for saving idling events is save() inside GenericHibernateDaoImpl:

@Override
public T save(T item) {
factory.getCurrentSession().saveOrUpdate(item);
return item;
}


class GenericHibernateDaoImpl has @Transactional directive, so, as I understand, the transaction is open before calling save() method and committed and saved when save() finishes.

and the legacy project uses no Hibernate, it's just using java.sql.Statement:

setStatement.execute(FleetManagerSqlHelper.insertIdlingEvent(deviceBinding.getVehicleId(), Long.toString(gpsPositionId)));


What is the best ways to deal with this situation? I can decrease innodb_lock_wait_timeout (at the moment it's 50) and catch TimeoutException every time it happens, retrying it until it makes the job (i even saw someone recommending it online), but is it the right approach? This situation can happen with other tables as well, I'd guess, does it mean I need to surround all the queries inside the project with retries?

I'd appreciate any advice, as I don't have that much experience with MySQL and I'm very confused by this behaviour.

Thank you very much!

Answer

We have changed the transaction isolation level to READ-COMMITTED and it seems to have solved the problem

Comments