Howdeadlockcanoccurifonequerywaitsforthelockheldbyotherquery

I am facing following exception:

我面临以下异常:

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

What I can tell is that I have multiple threads executing queries on same tables in parallel. But I can't understand why this exception can occur. Since if one query has locked a table, then the same query(or other query on the same table) in another thread will wait for the lock. How come the deadlock is created?

我可以告诉我的是,我有多个线程并行地在同一个表上执行查询。但我无法理解为什么会发生这种异常。因为如果一个查询已锁定表,则另一个线程中的相同查询(或同一表上的其他查询)将等待锁定。怎么会造成僵局?

I repeat in different words, I understand the exception, but what I don't understand is that a query is waiting for other query, the other query should execute, leave the lock, and waiting query can acquire the lock, why deadlock?

我用不同的词重复,我理解异常,但我不明白的是,一个查询正在等待其他查询,另一个查询应该执行,离开锁,等待查询可以获取锁,为什么死锁?

If someone wants to look the actual queries(These queries are executed in multiple threads in the same order as printed here, without any transactions):

如果有人想查看实际查询(这些查询在多个线程中以与此处打印相同的顺序执行,没有任何事务):

UPDATE daily_stats, account, campaign SET campaign_messages_cost = campaign_messages_cost + (IF(current_free_sms >= ?, campaign.campaign_cost, campaign.campaign_cost + (account.sms_charging_rate * ? ))), campaign_messages_delivered_count = campaign_messages_delivered_count + 1 WHERE daily_stats_id = ? AND campaign.id = ? AND daily_stats.account_id = account.id

update account a JOIN campaign b ON a.id = b.account_id set campaign_cost = IF(current_free_sms >= ?, campaign_cost, campaign_cost + (a.sms_charging_rate * ? )), delivered_count = delivered_count + ?, credit = IF(current_free_sms >= ?, credit, credit - (a.sms_charging_rate * ?)), current_free_sms = IF (current_free_sms >= ?, current_free_sms - ?, current_free_sms) where b.id = ?

1 个解决方案