一、问题
有一次线上系统运行过程中,爆出更新数据库死锁的异常,联系DBA排查原因:在批量更新库存时,发生死锁。
二、解决方案
对调用DAL API的参数List进行排序,保证获取资源的顺序一致,从而避免数据库死锁,于是就有了这篇blog。
三、利用java.List.Collections对List排序
1. 待排序的POJO类,OfferSaleItemStatDAOUpdateParam.java:
public class OfferSaleItemStatDAOUpdateParam {
/**
* 排序条件1
*/
private Long offerId;
/**
* 排序条件2
*/
private String specId;
/**
* 当前变化的订购数量,即detA值
*/
private Long orderAmount;
.. 忽略get and set method ...
public String toString() {
return ToStringBuilder.reflectionToString(this);
}
2. 编写比较器OfferSaleItemStatDAOUpdateParamComparator,继承了Comparator接口:
public class OfferSaleItemStatDAOUpdateParamComparator implements Comparator<OfferSaleItemStatDAOUpdateParam> {
/**
* <pre>
* 1. 先比较offerId
* 2. 然后再比较specId
*
* <pre>
*/
@Override
public int compare(OfferSaleItemStatDAOUpdateParam o1, OfferSaleItemStatDAOUpdateParam o2) {
if (o1 == null || o1.getOfferId() == null || o2 == null || o2.getOfferId() == null) {
return 0;
}
if (o1.getOfferId() < o2.getOfferId()) {
return -1;
} else if (o1.getOfferId() > o2.getOfferId()) {
return 1;
} else {
String o1SpecId = (o1.getSpecId() == null) ? "" : o1.getSpecId();
String o2SpecId = (o2.getSpecId() == null) ? "" : o2.getSpecId();
int tempResult = o2SpecId.compareTo(o1SpecId);
if (tempResult < 0) {
return -1;
} else if (tempResult > 0) {
return 1;
} else {
return 0;
}
}
}
}
3. 测试结果,使用java.List.Collections对List排序
public class OfferSaleItemStatDAOUpdateParamComparatorTest extends JTester {
@Test
public void testCompare() {
OfferSaleItemStatDAOUpdateParamComparator comparator = new OfferSaleItemStatDAOUpdateParamComparator();
OfferSaleItemStatDAOUpdateParam p1 = new OfferSaleItemStatDAOUpdateParam();
OfferSaleItemStatDAOUpdateParam p2 = new OfferSaleItemStatDAOUpdateParam();
OfferSaleItemStatDAOUpdateParam p3 = new OfferSaleItemStatDAOUpdateParam();
OfferSaleItemStatDAOUpdateParam p4 = new OfferSaleItemStatDAOUpdateParam();
p1.setOfferId(2L);
p1.setSpecId("bc");
p2.setOfferId(1L);
p2.setSpecId(null);
p3.setOfferId(3L);
p3.setSpecId("gdf");
p4.setOfferId(3L);
p4.setSpecId("ac");
List<OfferSaleItemStatDAOUpdateParam> ll = new ArrayList<OfferSaleItemStatDAOUpdateParam>();
ll.add(p1);
ll.add(p2);
ll.add(p3);
ll.add(p4);
Collections.sort(ll, comparator);
System.out.println(ll.get(0));
System.out.println(ll.get(1));
System.out.println(ll.get(2));
System.out.println(ll.get(3));
}
}
输出结果,可以看出,先按照offerId(数字)进行排序,然后在按照specId(字母)排序:
OfferSaleItemStatDAOUpdateParam@a6d8cb6b[offerId=1,specId=<null>,orderAmount=<null>]
OfferSaleItemStatDAOUpdateParam@478e4327[offerId=2,specId=bc,orderAmount=<null>]
OfferSaleItemStatDAOUpdateParam@13a78071[offerId=3,specId=gdf,orderAmount=<null>]
OfferSaleItemStatDAOUpdateParam@59e184cb[offerId=3,specId=ac,orderAmount=<null>]