Getting "Lock wait timeout exceeded" exception while updating a table in DB

Asked Oct 30 '19 at 05:59

Active Oct 30 '19 at 08:12

Viewed 59 times

I am executing the below MySQL UPDATE query,

Query query = em.createNamedQuery("User.unSubscribeUserByEmailId");
        query.setParameter("emailId", emailId);
        query.executeUpdate();

Whereas, User.unSubscribeUserByEmailId says,

query = "UPDATE User u set u.unsubscribed=1,u.unsubscribedReason='unsubscribed'  WHERE u.emailId = :emailId"),

One more thing I want to mention here is that the "User" table is the table frequently used/hit by other tables also. Also I'm not using a large chunk of emailId's updating at once, instead we have a foreach loop where single Id's are updating the unsubscription status like,

for(String emailId : existingEmailIds.getListItems()){
   unsubscribeEmailId(emailId, whitelistDomains, blacklistDomains);
}

Don't understand why am I getting "lock wait timeout exceeded" exception.

edited Oct 30 '19 at 08:12

asked Oct 30 '19 at 05:59

Anu

Is the user table indexed on emailId? – Martin'sRun Oct 30 '19 at 06:04
Yes, User table is indexed on emailId. – Anu Oct 30 '19 at 06:09
Looks like the table is locked by another transaction. Also see https://stackoverflow.com/questions/6000336/how-to-debug-lock-wait-timeout-exceeded-on-mysql – The Nail Oct 30 '19 at 08:17
Why try restarting transaction? However, no transaction is being created here in my code. – Anu Oct 30 '19 at 08:25

Getting "Lock wait timeout exceeded" exception while updating a table in DB

0 Answers0