title: How to avoid ORA-00060 (deadlock detected) error when updating Oracle table on Spark
body: <p>I have a weird error in my spark job and I would use some explanation if possible.</p>
  
  <p>So, my Spark job loads data from a Hive table, transform it into a Dataframe and then updates an already existing Oracle table based on some columns. </p>
  
  <p>When the dataframe is not very huge, the job runs with no problems. 
  When the dataframe is pretty huge, the job runs for several hours and then stops with an Oracle error:</p>
  
  <pre><code>exception caught: org.apache.spark.SparkException: Job aborted due to stage failure: Task 104 in stage 43.0 failed 4 times, most recent failure: Lost task 104.3 in stage 43.0 (TID 5937, lxpbda55.ra1.intra.groupama.fr, executor 227): java.sql.BatchUpdateException: ORA-00060: deadlock detected while waiting for resource
  </code></pre>
  
  <p>This is how my code works: </p>
  
  <pre><code>//This is where the error appears
  modification(df_Delta_Modif, champs, conditions, cstProp)
  
  //This is its definition
  def modification(df: DataFrame, champs: List[String], conditions: List[String], cstProp: java.util.Properties) {
      val url = Parametre_mod.oracleUrl
      val options: JDBCOptions = new JDBCOptions(Map("url" -&gt; url, "dbtable" -&gt; Parametre_mod.targetTableBase, "user" -&gt; Parametre_mod.oracleUser,
        "password" -&gt; Parametre_mod.oraclePassword, "driver" -&gt; "oracle.jdbc.driver.OracleDriver", "batchSize" -&gt; "30000"))
      Crud_mod.modifierbatch(df, options, champs, conditions)
    }
  
  //This is the definition of modifierbatch. It starts with establishing a connection to Oracle.
  //Which surely works because I use the same thing on other scripts and it works fine
  def modifierbatch(df: DataFrame,
                options : JDBCOptions,
                 champs: List[String],
                 conditions: List[String]) {
      val url = options.url
      val tables = options.table
      val dialect = JdbcDialects_mod.get(url)
      val nullTypes: Array[Int] = df.schema.fields.map { field =&gt;
        getJdbcType(field.dataType, dialect).jdbcNullType
      }
      val rddSchema = df.schema
      val getConnection: () =&gt; Connection = createConnectionFactory(options)
      val batchSize = options.batchSize
      val chainestmt = creerOdreSQLmodificationSimple(champs, conditions, tables) //definition below
      val listChamps: List[Int] = champs.map(rddSchema.fieldIndex):::conditions.map(rddSchema.fieldIndex)
      df.foreachPartition { iterator =&gt;
        //savePartition(getConnection, table, iterator, rddSchema, nullTypes, batchSize, dialect)
        executePartition(getConnection, tables, iterator, rddSchema, nullTypes, batchSize, chainestmt, listChamps, dialect, 0, "")
      }
    }
  
  //This is the definition of creerOdreSQLmodificationSimple
  def creerOdreSQLmodificationSimple(listChamps: List[String], listCondition: List[String], tablecible: String): String = {
      val champs = listChamps.map(_.toUpperCase).mkString(" = ?, ")
      val condition = listCondition.map(_.toUpperCase).mkString(" = ? and ")
  
      s"""UPDATE ${tablecible} SET ${champs} = ? WHERE ${condition} = ?"""
    }
  </code></pre>
  
  <p>So as you can see the principal is not very complicated. I just execute an Oracle function (update) using a batch. I don't know what causes the deadlock problem. I did not use any repartition in Spark.</p>
  
  <p>Please let me know if you need further details. Thank you</p>