mysql 锁优化

INSERT INTO T_DriverTop(driverId,breakPer,orderNum,totalOrderNum,breakNum,createDate) 
SELECT t1.DriverId,t1.breakPer,IFNULL(t2.orderNum,0) AS orderNum,t1.totalOrderNum,t1.breakNum,SYSDATE() FROM 
(
SELECT (b.breakNum/o.totalOrderNum) AS breakPer,o.DriverId,o.totalOrderNum,b.breakNum FROM
(SELECT COUNT(1) AS breakNum,DriverId FROM T1  WHERE DriverComment IN (3,8,9) GROUP BY DriverId) AS b
INNER JOIN
(SELECT COUNT(1) AS totalOrderNum,DriverId FROMT1 GROUP BY DriverId) AS o
ON b.DriverId = o.DriverId
WHERE (b.breakNum/o.totalOrderNum) < 0.1
) t1
LEFT JOIN 
(
SELECT COUNT(1) AS orderNum,t.DriverId FROM T1 t 
WHERE DATE_FORMAT(t.UpTime,'%Y%m%d')>=(SELECT TIMESTAMPADD(DAY,-30,DATE_FORMAT(SYSDATE(),'%Y%m%d')))
GROUP BY t.DriverId
) t2
ON t1.DriverId=t2.DriverId
ORDER BY t2.orderNum DESC

LIMIT 0,#{max}

虽然 mysql  repeatable read或以下的事务隔离级别，使用MCC，当MCC的使用仅限于 单个select 查询。

像上面那种， insert from select 这种嵌套是无法使用mcc，直接使用x锁。 导致其他insert 或者update T1表的地方都抛

 java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
; SQL []; Lock wait timeout exceeded; try restarting transaction; nested exception is java.sql.SQLException: Lock wait timeout excee
ded; try restarting transaction异常

解决办法：将select的结果放临时表，，，再做跟insert做关联就可以避免锁住主表

参考 

事务隔离级别

      MCC