RE: ORA-8177 with Hive transactions

[Mich Talebzadeh]

I have seen this one before as I use oracle database as my metastore.

 

Two things if I recall correctly

 

1.       Have you run the oracle sql script for concurrency against your 
metastore. For example hive-txn-schema-0.14.0.oracle.sql

2.       You also need to run zookeeper for concurrency access

 

I believe that will resolve the issue

 

HTH

 

Mich Talebzadeh

 

Sybase ASE 15 Gold Medal Award 2008

A Winning Strategy: Running the most Critical Financial Data on ASE 15

 
<http://login.sybase.com/files/Product_Overviews/ASE-Winning-Strategy-091908.pdf>
 http://login.sybase.com/files/Product_Overviews/ASE-Winning-Strategy-091908.pdf

Author of the books "A Practitioner’s Guide to Upgrading to Sybase ASE 15", 
ISBN 978-0-9563693-0-7. 

co-author "Sybase Transact SQL Guidelines Best Practices", ISBN 
978-0-9759693-0-4

Publications due shortly:

Complex Event Processing in Heterogeneous Environments, ISBN: 978-0-9563693-3-8

Oracle and Sybase, Concepts and Contrasts, ISBN: 978-0-9563693-1-4, volume one 
out shortly

 

 <http://talebzadehmich.wordpress.com/> http://talebzadehmich.wordpress.com

 

NOTE: The information in this email is proprietary and confidential. This 
message is for the designated recipient only, if you are not the intended 
recipient, you should destroy it immediately. Any information in this message 
shall not be understood as given or endorsed by Peridale Technology Ltd, its 
subsidiaries or their employees, unless expressly so stated. It is the 
responsibility of the recipient to ensure that this email is virus free, 
therefore neither Peridale Ltd, its subsidiaries nor their employees accept any 
responsibility.

 

From: Steve Howard [mailto:stevedhow...@gmail.com] 
Sent: 18 September 2015 18:55
To: 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());
    }
  }
}