All, We continue to struggle with this. We *never* get the lock, and found one issue in which the retry logic gets in an infinite loop. We submitted a JIRA for that (https://issues.apache.org/jira/browse/HIVE-11934), and patched our version (HDP 2.3, Hive 1.2.1) with a fix in which the deadlockCount variable is no longer managed in the lock() method. That works, but we still couldn't get the lock, and the exception was thrown after ten retries. At least we knew it was broken earlier ;)
We have made the changes to the HIVE transaction tables to enable ROWDEPENDENCIES, but are still plagued with serialization errors that are never resolved. We have only a single writer, as the Hive database environment is used as a target for an existing EDW dataset. The job loader is the only one making changes. However, it is for analytics, so we have a lot of readers. We have considered changing the TRANSACTION_SERIALIZABLE for the dbConn() method call in the TxnHandler class to READ_COMMITTED, as Oracle provides consistent reads. Of course, the serialization exception is thrown when one thread (a read or a writer I guess) attempts to lock a hive table (or in our case, several hundred daily hive table partitions) attempts to update the row, and another thread has changed and committed it in the meantime. Unless I missing it, this will always be an issue since we have readers and writers and each appears to take a lock. If we know we will have a single writer, the largest risk is that the reader thinks the data hasn't changed, when it has. For our needs, that isn't a huge issue. Are we missing something? Any ideas? Thanks, Steve On Fri, Sep 18, 2015 at 3:39 PM, Steve Howard <stevedhow...@gmail.com> wrote: > 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 <ser...@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 <stevedhow...@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()); >> } >> }} >> >> >
