2

I have table "tbl_data", where have a lot of rows (3 - 5 million).
I have JAVA (*.jar) module from which I INSERT/UPDATE data into this table.
If I start *.jar file (for example, "run.jar") and after INSERT/UPDATE process I start "run.jar" sequentially, there no problems - works OK.
If I start "run.jar" and then start "run_01.jar" (jar file with the similar logic: INSERT/UPDATE into "tbl_data"), I can get error:

com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction

All actions at "run.jar" executes in transaction, like:

START TRANSACTION;
INSERT INTO tbl_data ...;
UPDATE tbl_data ...;
COMMIT;

So, I need to have ability make INSERT/UPDATE from several sources at the same time without blocking table for modification (Every jar file UPDATES/INSERTS its own rows in "tbl_data" so it is no problem for data in modification data at the same time).

Improve this question
1
  • Can you post deadlock trace? It can be found in mysql error log if "innodb_print_all_deadlocks" server flag is set, or using SHOW ENGINE INNODB STATUS; command which gives just last deadlock information (it should be enough though since all your deadlock seem to be pretty much similar).
    – a1ex07
    Mar 27, 2014 at 14:16

2 Answers 2

Reset to default
1

If you are totally sure about the separation status of your data, you can set the global isolation level to READ COMMITTED or even to READ UNCOMMITTED. The first blocks less from the index, which should be enough in your case. The second one arranges for all SELECT statements to be non blocking. This is as low as you can go.

Whatever you choose, you should set the global isolation level back to REPEATABLE READ as soon as you finished inserting your data.

global means that this applies to all connections, which are established. So this change also affects connections beside your jarfiles. If you have the possibility to change the jarfiles, you should set the connection based isolation level with the SESSION keyword instead in both jarfiles.

Improve this answer
7
  • 1
    Changing global isolation level is not a good idea. First of all, it affects only new sessions, and most applications use one or the other implementation of connection pool which means they obtain physical connections in a bulk and very rare. Secondly, changing global isolation level requires more permissions and giving application SUPER privilege doesn't sound right to me...
    – a1ex07
    Mar 27, 2014 at 14:49
  • @a1ex07 But if one cannot change the jar file, what other solution would you propose?
    – Ulrich Thomas Gabor
    Mar 27, 2014 at 14:58
  • 2
    Deadlocks mean there is something wrong in the application logic. All I can see from the question that there are several UPDATEs hitting the same table, so deadlocks may be attributed to the fact that mysql performs gap locks for non-unique search conditions (assuming default isolation level repeatable read is used). I'd focus on fixing the application[s]; if that is not possible for any reason then just execute them sequentially...
    – a1ex07
    Mar 27, 2014 at 15:42
  • @a1ex07 but if this is just inputting initial data, while no other connections are established, why take the slow sequential way, when one can change the global isolation level temporarily and perform the inserts in parallel?
    – Ulrich Thomas Gabor
    Mar 27, 2014 at 15:44
  • If it's one time operation, then maybe it's worth trying (on the other hand, for one time operation performance should not be an issue)... But it looks like a hack to me even if it works. I'm used to think that database is "owned" by enterprise, not by a particular application. Also, changing global isolation level may not work if the app sets it explicitly for the session.
    – a1ex07
    Mar 27, 2014 at 15:51
0

It would be important to see the complete error message. It should mention the name of the locked index. If it is the primary key or a unique index, multiple transaction try to insert the same value at the same time. If this is the case, you should fix this behaviour.

If it is a non-unique index, the problem is that the locks are not granular enough (gap lock). I suggest to:

  • Set innodb_lock_wait_timeout = 0
  • Every time you get an error 1205 (a row is locked), retry the transaction

Even if you don't follow my suggestion, in case of deadlocks it is a good idea to retry a reasonable number of times (1?). Because, even if your programs follow all the best practices, probably sometimes you will still see deadlock.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.