order item table
order_item_id
order_id
quantity
unit_price
shipping_price
business_id
workflow_id
delivery_id
item_id

Orders table
billing_address_id
shipping_address_id
payment_mode
total_price
shipping_price
customer_id

UPDATE `order_items` t1 INNER JOIN Orders t2 ON t2.order_id = t1.order_id SET t1.workflow_id = ?  WHERE t1.order_item_id = ? and t2.order_id = ? and t2.customer_id = ? and t1.delivery_id = ? 

UPDATE `order_items` t1 SET t1.workflow_id = ?
           WHERE t1.order_item_id = ? and t1.business_id = ? and t1.delivery_id = ?

UPDATE `order_items` t1 INNER JOIN Orders t2 ON t2.order_id = t1.order_id SET t1.workflow_id = ? WHERE t1.order_item_id = ?  and t2.order_id = ? and t1.delivery_id = ?"

These queries are fired on different scenarios from my java rest service. (at any point of time, only one query will be used). Previously I didn't use the inner join in my update sql and it worked well.

Now after I modified the query, it throws the following exception and the query is stuck and doesn't return for a minute.

   java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:996)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3887)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3823)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2435)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2582)

UPDATE

This was happening because we forgot to set the autocommit mode to true again in the finally block. After which we didnt see this error.

Point1: You should not update query with join in application instead get primary key and then update the table based on primary key.

Point2: Show your tables structure with index you can get details by "show create table mytable" command, so that it can be checked that your update query is optimized or not.

Point3: If still you want to update based on join due to any specific reason and also your query is optimized then need to change your wait_timeout variable. So check what value is set in this variable on your server. you can check by below command-

SHOW GLOBAL VARIABLES LIKE 'wait_timeout';

more