CLOUDSTACK-2486: Deleting the host_details and inserting them back can lead to mysql deadlock
- Instead of using separate delete and insert, we will use ON DUPLICATE KEY UPDATE to avoid the MySQL deadlock Project: http://git-wip-us.apache.org/repos/asf/cloudstack/repo Commit: http://git-wip-us.apache.org/repos/asf/cloudstack/commit/a13dd59d Tree: http://git-wip-us.apache.org/repos/asf/cloudstack/tree/a13dd59d Diff: http://git-wip-us.apache.org/repos/asf/cloudstack/diff/a13dd59d Branch: refs/heads/object_store Commit: a13dd59d167e70b3991ea666b83bdd240aa5e786 Parents: 7e0006a Author: Prachi Damle <pra...@cloud.com> Authored: Tue May 14 14:43:15 2013 -0700 Committer: Prachi Damle <pra...@cloud.com> Committed: Wed May 15 11:16:41 2013 -0700 ---------------------------------------------------------------------- .../entity/api/db/dao/HostDetailsDaoImpl.java | 43 +++++++++------ 1 files changed, 27 insertions(+), 16 deletions(-) ---------------------------------------------------------------------- http://git-wip-us.apache.org/repos/asf/cloudstack/blob/a13dd59d/engine/orchestration/src/org/apache/cloudstack/engine/datacenter/entity/api/db/dao/HostDetailsDaoImpl.java ---------------------------------------------------------------------- diff --git a/engine/orchestration/src/org/apache/cloudstack/engine/datacenter/entity/api/db/dao/HostDetailsDaoImpl.java b/engine/orchestration/src/org/apache/cloudstack/engine/datacenter/entity/api/db/dao/HostDetailsDaoImpl.java index 02f8c2c..e0ae778 100644 --- a/engine/orchestration/src/org/apache/cloudstack/engine/datacenter/entity/api/db/dao/HostDetailsDaoImpl.java +++ b/engine/orchestration/src/org/apache/cloudstack/engine/datacenter/entity/api/db/dao/HostDetailsDaoImpl.java @@ -16,6 +16,8 @@ // under the License. package org.apache.cloudstack.engine.datacenter.entity.api.db.dao; +import java.sql.PreparedStatement; +import java.sql.SQLException; import java.util.HashMap; import java.util.List; import java.util.Map; @@ -30,18 +32,19 @@ import com.cloud.utils.db.GenericDaoBase; import com.cloud.utils.db.SearchBuilder; import com.cloud.utils.db.SearchCriteria; import com.cloud.utils.db.Transaction; +import com.cloud.utils.exception.CloudRuntimeException; @Component(value="EngineHostDetailsDao") @Local(value=HostDetailsDao.class) public class HostDetailsDaoImpl extends GenericDaoBase<DetailVO, Long> implements HostDetailsDao { protected final SearchBuilder<DetailVO> HostSearch; protected final SearchBuilder<DetailVO> DetailSearch; - + public HostDetailsDaoImpl() { HostSearch = createSearchBuilder(); HostSearch.and("hostId", HostSearch.entity().getHostId(), SearchCriteria.Op.EQ); HostSearch.done(); - + DetailSearch = createSearchBuilder(); DetailSearch.and("hostId", DetailSearch.entity().getHostId(), SearchCriteria.Op.EQ); DetailSearch.and("name", DetailSearch.entity().getName(), SearchCriteria.Op.EQ); @@ -53,7 +56,7 @@ public class HostDetailsDaoImpl extends GenericDaoBase<DetailVO, Long> implement SearchCriteria<DetailVO> sc = DetailSearch.create(); sc.setParameters("hostId", hostId); sc.setParameters("name", name); - + DetailVO detail = findOneIncludingRemovedBy(sc); if("password".equals(name) && detail != null){ detail.setValue(DBEncryptionUtil.decrypt(detail.getValue())); @@ -65,7 +68,7 @@ public class HostDetailsDaoImpl extends GenericDaoBase<DetailVO, Long> implement public Map<String, String> findDetails(long hostId) { SearchCriteria<DetailVO> sc = HostSearch.create(); sc.setParameters("hostId", hostId); - + List<DetailVO> results = search(sc, null); Map<String, String> details = new HashMap<String, String>(results.size()); for (DetailVO result : results) { @@ -77,12 +80,12 @@ public class HostDetailsDaoImpl extends GenericDaoBase<DetailVO, Long> implement } return details; } - + @Override public void deleteDetails(long hostId) { SearchCriteria sc = HostSearch.create(); sc.setParameters("hostId", hostId); - + List<DetailVO> results = search(sc, null); for (DetailVO result : results) { remove(result.getId()); @@ -91,19 +94,27 @@ public class HostDetailsDaoImpl extends GenericDaoBase<DetailVO, Long> implement @Override public void persist(long hostId, Map<String, String> details) { + final String InsertOrUpdateSql = "INSERT INTO `cloud`.`host_details` (host_id, name, value) VALUES (?,?,?) ON DUPLICATE KEY UPDATE value=?"; + Transaction txn = Transaction.currentTxn(); txn.start(); - SearchCriteria<DetailVO> sc = HostSearch.create(); - sc.setParameters("hostId", hostId); - expunge(sc); - + for (Map.Entry<String, String> detail : details.entrySet()) { - String value = detail.getValue(); - if("password".equals(detail.getKey())){ - value = DBEncryptionUtil.encrypt(value); - } - DetailVO vo = new DetailVO(hostId, detail.getKey(), value); - persist(vo); + String value = detail.getValue(); + if ("password".equals(detail.getKey())) { + value = DBEncryptionUtil.encrypt(value); + } + try { + PreparedStatement pstmt = txn.prepareAutoCloseStatement(InsertOrUpdateSql); + pstmt.setLong(1, hostId); + pstmt.setString(2, detail.getKey()); + pstmt.setString(3, value); + pstmt.setString(4, value); + pstmt.executeUpdate(); + } catch (SQLException e) { + throw new CloudRuntimeException("Unable to persist the host_details key: " + detail.getKey() + + " for host id: " + hostId, e); + } } txn.commit(); }
