You are viewing a plain text version of this content. The canonical link for it is here.

Posted to dev@ambari.apache.org by Jonathan Hurley <jh...@hortonworks.com> on 2015/07/28 21:54:06 UTC

Review Request 36895: Cluster creates stuck at 9x% (deadlock sql exception)

-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/36895/
-----------------------------------------------------------

Review request for Ambari, Alejandro Fernandez, Nate Cole, and Sumit Mohanty.

Bugs: AMBARI-12570
https://issues.apache.org/jira/browse/AMBARI-12570

Repository: ambari

Description
-------

Similar to AMBARI-12526, Ambari installation via a blueprint on SQL Azure gets stuck somewhere between 90% and 100% because of a SQL Database deadlock.

- We have dual X-locks on hostcomponentstate asking for U-locks when updating the CLUSTERED INDEX.
- Both dual X-locks, from different transactions and different processes, are on the same row (technically impossible) - based on the XML execution plan, we can see that the concurrent UPDATE statements are executing on different rows due to their CLUSTERED INDEX predicate.
- In Java, Ambari has locks which prevent concurrent U- or X-locks on the same row
- Only happens on SQL Server

My best suspicion right now is that we have a key hash collision happening on this table. That's why two processes appear to have the same lock even though they are on different rows.

Restricting row-level locking on this table will prevent locking on hash keys which could collide.

Diffs
-----

ambari-server/src/main/resources/Ambari-DDL-SQLServer-CREATE.sql 0ff1aff

Diff: https://reviews.apache.org/r/36895/diff/

Testing
-------

Deployed a clean cluster on SQL Server and then ran 10+ deployments on SQL Azure without seeing a deadlock.

Thanks,

Jonathan Hurley

Re: Review Request 36895: Cluster creates stuck at 9x% (deadlock sql exception)

Posted by Sumit Mohanty <sm...@hortonworks.com>.

-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/36895/#review93544
-----------------------------------------------------------

Ship it!


Ship It!

- Sumit Mohanty


On July 30, 2015, 1:11 a.m., Jonathan Hurley wrote:
> 
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/36895/
> -----------------------------------------------------------
> 
> (Updated July 30, 2015, 1:11 a.m.)
> 
> 
> Review request for Ambari, Alejandro Fernandez, Nate Cole, and Sumit Mohanty.
> 
> 
> Bugs: AMBARI-12570
>     https://issues.apache.org/jira/browse/AMBARI-12570
> 
> 
> Repository: ambari
> 
> 
> Description
> -------
> 
> Similar to AMBARI-12526, Ambari installation via a blueprint on SQL Azure gets stuck somewhere between 90% and 100% because of a SQL Database deadlock.
> 
> - We have dual X-locks on hostcomponentstate asking for U-locks when updating the CLUSTERED INDEX.
> - Both dual X-locks, from different transactions and different processes, are on the same row (technically impossible) - based on the XML execution plan, we can see that the concurrent UPDATE statements are executing on different rows due to their CLUSTERED INDEX predicate.
> - In Java, Ambari has locks which prevent concurrent U- or X-locks on the same row
> - Only happens on SQL Server
> 
> My best suspicion right now is that we have a key hash collision happening on this table. That's why two processes appear to have the same lock even though they are on different rows.
> 
> Restricting row-level locking on this table will prevent locking on hash keys which could collide.
> 
> 
> Diffs
> -----
> 
>   ambari-server/src/main/resources/Ambari-DDL-SQLServer-CREATE.sql 0ff1aff 
> 
> Diff: https://reviews.apache.org/r/36895/diff/
> 
> 
> Testing
> -------
> 
> Deployed a clean cluster on SQL Server and then ran 10+ deployments on SQL Azure without seeing a deadlock.
> 
> 
> Thanks,
> 
> Jonathan Hurley
> 
>

Re: Review Request 36895: Cluster creates stuck at 9x% (deadlock sql exception)

Posted by Nate Cole <nc...@hortonworks.com>.

-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/36895/#review93535
-----------------------------------------------------------

Ship it!


Ship It!

- Nate Cole


On July 29, 2015, 9:11 p.m., Jonathan Hurley wrote:
> 
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/36895/
> -----------------------------------------------------------
> 
> (Updated July 29, 2015, 9:11 p.m.)
> 
> 
> Review request for Ambari, Alejandro Fernandez, Nate Cole, and Sumit Mohanty.
> 
> 
> Bugs: AMBARI-12570
>     https://issues.apache.org/jira/browse/AMBARI-12570
> 
> 
> Repository: ambari
> 
> 
> Description
> -------
> 
> Similar to AMBARI-12526, Ambari installation via a blueprint on SQL Azure gets stuck somewhere between 90% and 100% because of a SQL Database deadlock.
> 
> - We have dual X-locks on hostcomponentstate asking for U-locks when updating the CLUSTERED INDEX.
> - Both dual X-locks, from different transactions and different processes, are on the same row (technically impossible) - based on the XML execution plan, we can see that the concurrent UPDATE statements are executing on different rows due to their CLUSTERED INDEX predicate.
> - In Java, Ambari has locks which prevent concurrent U- or X-locks on the same row
> - Only happens on SQL Server
> 
> My best suspicion right now is that we have a key hash collision happening on this table. That's why two processes appear to have the same lock even though they are on different rows.
> 
> Restricting row-level locking on this table will prevent locking on hash keys which could collide.
> 
> 
> Diffs
> -----
> 
>   ambari-server/src/main/resources/Ambari-DDL-SQLServer-CREATE.sql 0ff1aff 
> 
> Diff: https://reviews.apache.org/r/36895/diff/
> 
> 
> Testing
> -------
> 
> Deployed a clean cluster on SQL Server and then ran 10+ deployments on SQL Azure without seeing a deadlock.
> 
> 
> Thanks,
> 
> Jonathan Hurley
> 
>

Re: Review Request 36895: Cluster creates stuck at 9x% (deadlock sql exception)

Posted by Sumit Mohanty <sm...@hortonworks.com>.

-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/36895/#review93960
-----------------------------------------------------------


Changes look good. Will wait for the upgrade related changes.

- Sumit Mohanty


On Aug. 3, 2015, 6:39 p.m., Jonathan Hurley wrote:
> 
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/36895/
> -----------------------------------------------------------
> 
> (Updated Aug. 3, 2015, 6:39 p.m.)
> 
> 
> Review request for Ambari, Alejandro Fernandez, Nate Cole, and Sumit Mohanty.
> 
> 
> Bugs: AMBARI-12570
>     https://issues.apache.org/jira/browse/AMBARI-12570
> 
> 
> Repository: ambari
> 
> 
> Description
> -------
> 
> Similar to AMBARI-12526, Ambari installation via a blueprint on SQL Azure gets stuck somewhere between 90% and 100% because of a SQL Database deadlock.
> 
> - We have dual X-locks on hostcomponentstate asking for U-locks when updating the CLUSTERED INDEX.
> - Both dual X-locks, from different transactions and different processes, are on the same row (technically impossible) - based on the XML execution plan, we can see that the concurrent UPDATE statements are executing on different rows due to their CLUSTERED INDEX predicate.
> - In Java, Ambari has locks which prevent concurrent U- or X-locks on the same row
> - Only happens on SQL Server
> 
> My best suspicion right now is that we have a key hash collision happening on this table. That's why two processes appear to have the same lock even though they are on different rows.
> 
> Restricting row-level locking on this table will prevent locking on hash keys which could collide.
> 
> 
> Diffs
> -----
> 
>   ambari-server/src/main/java/org/apache/ambari/server/orm/dao/HostComponentStateDAO.java fb585fd 
>   ambari-server/src/main/java/org/apache/ambari/server/orm/entities/HostComponentStateEntity.java 45e036b 
>   ambari-server/src/main/java/org/apache/ambari/server/orm/entities/HostComponentStateEntityPK.java 86e0dee 
>   ambari-server/src/main/java/org/apache/ambari/server/state/svccomphost/ServiceComponentHostImpl.java 0850a79 
>   ambari-server/src/main/resources/Ambari-DDL-MySQL-CREATE.sql 1b67b24 
>   ambari-server/src/main/resources/Ambari-DDL-Oracle-CREATE.sql 16f6a0a 
>   ambari-server/src/main/resources/Ambari-DDL-Postgres-CREATE.sql cd6e27a 
>   ambari-server/src/main/resources/Ambari-DDL-Postgres-EMBEDDED-CREATE.sql c7138be 
>   ambari-server/src/main/resources/Ambari-DDL-SQLServer-CREATE.sql 0ff1aff 
>   ambari-server/src/test/java/org/apache/ambari/server/state/ServiceComponentTest.java 96bbb1d 
>   ambari-server/src/test/java/org/apache/ambari/server/state/cluster/ClustersTest.java ce1fd34 
>   ambari-server/src/test/java/org/apache/ambari/server/state/svccomphost/ServiceComponentHostTest.java f6ab0ec 
>   ambari-server/src/test/java/org/apache/ambari/server/upgrade/UpgradeCatalog170Test.java 93f7f8c 
>   ambari-server/src/test/java/org/apache/ambari/server/upgrade/UpgradeCatalog200Test.java 484c18d 
> 
> Diff: https://reviews.apache.org/r/36895/diff/
> 
> 
> Testing
> -------
> 
> Deployed a clean cluster on SQL Server and then ran 10+ deployments on SQL Azure without seeing a deadlock.
> 
> 
> Thanks,
> 
> Jonathan Hurley
> 
>

Re: Review Request 36895: Cluster creates stuck at 9x% (deadlock sql exception)

Posted by Robert Nettleton <rn...@hortonworks.com>.

-----------------------------------------------------------
This is an automatically generated e-mail. To reply, visit:
https://reviews.apache.org/r/36895/#review94257
-----------------------------------------------------------

Ship it!


Ship It!

- Robert Nettleton


On Aug. 5, 2015, 1:22 p.m., Jonathan Hurley wrote:
> 
> -----------------------------------------------------------
> This is an automatically generated e-mail. To reply, visit:
> https://reviews.apache.org/r/36895/
> -----------------------------------------------------------
> 
> (Updated Aug. 5, 2015, 1:22 p.m.)
> 
> 
> Review request for Ambari, Alejandro Fernandez, Nate Cole, Robert Levas, Robert Nettleton, and Sumit Mohanty.
> 
> 
> Bugs: AMBARI-12570
>     https://issues.apache.org/jira/browse/AMBARI-12570
> 
> 
> Repository: ambari
> 
> 
> Description
> -------
> 
> Similar to AMBARI-12526, Ambari installation via a blueprint on SQL Azure gets stuck somewhere between 90% and 100% because of a SQL Database deadlock.
> 
> - We have dual X-locks on hostcomponentstate asking for U-locks when updating the CLUSTERED INDEX.
> - Both dual X-locks, from different transactions and different processes, are on the same row (technically impossible) - based on the XML execution plan, we can see that the concurrent UPDATE statements are executing on different rows due to their CLUSTERED INDEX predicate.
> - In Java, Ambari has locks which prevent concurrent U- or X-locks on the same row
> - Only happens on SQL Server
> 
> There are two issues that have been identified as contributing to the problem:
> - With compound primary keys where most of the columns are the same, SQL Server does run into key lock (hash) collisions.
> - When using a compound PK to query, the first column in the index is used to perform the initial b-tree seek. This was the cluster ID. Since every row has the same cluster ID, the entire table was being locked to perform the update on a single row.
> 
> The solution was to move away from the compound PK and use a surrogate long ID instead for the PK.
> 
> 
> Diffs
> -----
> 
>   ambari-server/src/main/java/org/apache/ambari/server/orm/dao/HostComponentStateDAO.java fb585fd 
>   ambari-server/src/main/java/org/apache/ambari/server/orm/entities/HostComponentStateEntity.java 45e036b 
>   ambari-server/src/main/java/org/apache/ambari/server/orm/entities/HostComponentStateEntityPK.java 86e0dee 
>   ambari-server/src/main/java/org/apache/ambari/server/state/svccomphost/ServiceComponentHostImpl.java 0850a79 
>   ambari-server/src/main/java/org/apache/ambari/server/upgrade/AbstractUpgradeCatalog.java edb1606 
>   ambari-server/src/main/java/org/apache/ambari/server/upgrade/UpgradeCatalog220.java 4eb7a80 
>   ambari-server/src/main/resources/Ambari-DDL-MySQL-CREATE.sql 1b67b24 
>   ambari-server/src/main/resources/Ambari-DDL-Oracle-CREATE.sql 16f6a0a 
>   ambari-server/src/main/resources/Ambari-DDL-Postgres-CREATE.sql cd6e27a 
>   ambari-server/src/main/resources/Ambari-DDL-Postgres-EMBEDDED-CREATE.sql c7138be 
>   ambari-server/src/main/resources/Ambari-DDL-SQLServer-CREATE.sql 0ff1aff 
>   ambari-server/src/test/java/org/apache/ambari/server/state/ServiceComponentTest.java 96bbb1d 
>   ambari-server/src/test/java/org/apache/ambari/server/state/cluster/ClustersTest.java ce1fd34 
>   ambari-server/src/test/java/org/apache/ambari/server/state/svccomphost/ServiceComponentHostTest.java f6ab0ec 
>   ambari-server/src/test/java/org/apache/ambari/server/upgrade/UpgradeCatalog170Test.java 93f7f8c 
>   ambari-server/src/test/java/org/apache/ambari/server/upgrade/UpgradeCatalog200Test.java 484c18d 
> 
> Diff: https://reviews.apache.org/r/36895/diff/
> 
> 
> Testing
> -------
> 
> Deployed numerous clusters on PostgresSQL, SQL Server, and MySQL using both the Ambari UI and blueprints. Upgraded a 2.1 database on PostgresSQL, Oracle, MySQL, and SQL Server to verify that changes to the `hostcomponentstate` table were good.
> 
> 
> Thanks,
> 
> Jonathan Hurley
> 
>