Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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).

share|improve this question
    
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 at 14:16

1 Answer 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.

share|improve this answer
    
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 at 14:49
    
@a1ex07 But if one cannot change the jar file, what other solution would you propose? –  GhostGambler Mar 27 at 14:58
    
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 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? –  GhostGambler Mar 27 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 at 15:51

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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