MySQL: Transaction and Deadlock

deadlockMySQLtransaction

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

Best Answer

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.

Related Solutions

Sql-server – Shared and IX locks causing deadlock (Sql server)

You'll want to change your code to something like this.

DECLARE @AssetIssueId INT
INSERT INTO [Issue] ([IssueId], [AssetId]) VALUES (?, ?)
SET @AssetIssueID = scope_identity()
INSERT INTO [IssuePropertyValues] 
@AssetIssueID, ?, ?

As to the why...

If Java is starting a transaction for you automatically, and the insert of 10k+ rows is taking a few moments, then this thread is taking an IX on the Issue table. Another thread is then taking an IX on the issue table (which is blocked). This thread is then attempting to take an S on the issue table (for the INSERT SELECT FROM statement) which is blocked waiting for the other thread, and we now have a deadlock.

Mysql – How does thesql transaction works

Does all of the rows affected by the insert statement from db1 gets transfered into db2 immediately and are waiting for the commit line to execute in order to make the insert official?

Transafer is made immediatly, but other sessions will not see inserted data until commit (depends on transaction isolation level)

Or, are the rows affected by the insert statement are still inside db1 and are just waiting to be transfered into db2 once the "commit" command executes?

Data are not waiting for commit, just storage engine wait for commit to make changes persisted.

I wanted to know this facts because I will be copying data from a database through the internet and I'm worried that I might lose some data in the process... Any help would be greaty appreciated.

Every TCP based connection guarantee data integrity.

You should use replication instead of this approach

Best Answer

Related Solutions

Sql-server – Shared and IX locks causing deadlock (Sql server)

Mysql – How does thesql transaction works

Related Question