hive-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Steve Howard <stevedhow...@gmail.com>
Subject Re: ORA-8177 with Hive transactions
Date Fri, 18 Sep 2015 19:39:45 GMT
I think ROWDEPENDENCIES on an Oracle table also covers this issue, so I
don't think a separate JIRA is needed for the INITRANS change.

On Fri, Sep 18, 2015 at 2:51 PM, Sergey Shelukhin <sergey@hortonworks.com>
wrote:

> There’s HIVE-11831 <https://issues.apache.org/jira/browse/HIVE-11831> and
> https://issues.apache.org/jira/browse/HIVE-11833 that try to address this.
> We can do a patch similar to the first one; can you file a JIRA?
>
> From: Steve Howard <stevedhoward@gmail.com>
> Reply-To: "user@hive.apache.org" <user@hive.apache.org>
> Date: Friday, September 18, 2015 at 10:54
> To: "user@hive.apache.org" <user@hive.apache.org>
> Subject: ORA-8177 with Hive transactions
>
> While troubleshooting an issue with transactions shortly after enabling
> them, I noticed the following in an Oracle trace, which is our metastore
> for hive...
>
> ORA-8177: can't serialize access for this transaction
>
> These were thrown on "insert into HIVE_LOCKS..."
>
> Traditionally in Oracle, if an application actually needs serializable
> transactions, the fix is to to set initrans and maxtrans to the number of
> concurrent writers.  When I ran what is below on a table similar to
> HIVE_LOCKS, this exception was thrown everywhere.  The fix is to recreate
> the table with higher values for initrans (only 1 is the default for
> initrans, and 255 is the default for maxtrans).  When I did this and re-ran
> what is below, the exceptions were no longer thrown.
>
> Does anyone have any feedback on this performance hint?  The exceptions in
> hive are thrown from the checkRetryable method in the TxnHandler class, but
> I couldn't find what class.method throws them.  Perhaps the exceptions are
> not impactful, but given the fact the method expects them as it checks for
> the string in the exception message, I thought I would ask for feedback
> before we recreate the HIVE_LOCKS table with a higher value for INITRANS.
>
> import java.sql.*;public class testLock implements Runnable {
>   public static void main (String[] args) throws Exception {
>     Class.forName("oracle.jdbc.driver.OracleDriver");
>     for (int i = 1; i <= 100; i++) {
>       testLock tl = new testLock();
>     }
>   }
>
>   public testLock() {
>     Thread t = new Thread(this);
>     t.start();
>   }
>
>   public void run() {
>     try {
>       Connection conn = DriverManager.getConnection("jdbc:oracle:thin:username/pwd@dbhost:1521/dbservice");
>       conn.createStatement().execute("alter session set isolation_level = serializable");
>       PreparedStatement pst = conn.prepareStatement("update test set a = ?");
>       for (int j = 1; j <= 10000; j++) {
>         pst.setInt(1,j);
>         pst.execute();
>         conn.commit();
>         System.out.println("worked");
>       }
>     }
>     catch (Exception e) {
>       System.out.println(e.getMessage());
>     }
>   }}
>
>

Mime
View raw message