OCI8 + cursor_sharing=force x INSERT ... RETURNING INTO :returning_id hangs in SQL*Net half-duplex deadlock

[yahonda]

Summary

A bare INSERT ... VALUES ('literal') RETURNING "ID" INTO :returning_id
issued through connection.insert reliably hangs (~indefinitely) on
Oracle Database 23.26.1.0.0 + ruby-oci8 when the session has
cursor_sharing = FORCE (the adapter default).

The hang is a SQL*Net half-duplex deadlock: the server is stuck in
SQL*Net more data from client while ruby-oci8's main thread is stuck
in OCIStmtExecute -> nttfprd -> read(). Both sides are waiting for
the other to send the next packet.

Reproduction

Surfaced by the spec
spec/active_record/connection_adapters/oracle_enhanced/primary_key_trigger_spec.rb:320
("does not raise NoMethodError for :returning_id Symbol when logging").
That example was skipped in PR #2615 to unblock the branch; reverting
the skip reproduces the hang locally.

Minimal manual reproduction (after bundle exec rspec setup):

ActiveRecord::Base.establish_connection(CONNECTION_PARAMS)
conn = ActiveRecord::Base.lease_connection
conn.execute "CREATE TABLE t (id NUMBER PRIMARY KEY, name VARCHAR2(50))"
conn.insert("INSERT INTO t (name) VALUES ('alpha')", nil, "id")
# hangs forever

Pass cursor_sharing: :exact to establish_connection and the same
INSERT completes normally.

Diagnosis

Server-side (v$session)

SID  USERNAME           STATUS  EVENT                              BLOCKING_SESSION  SECONDS_IN_WAIT  SQL_ID
---  ----------------   ------  ---------------------------------  ----------------  ---------------  -------------
 38  ORACLE_ENHANCED    ACTIVE  SQL*Net more data from client      (none)            564              9ymnvkfmz0ay5

SQL_TEXT for 9ymnvkfmz0ay5:

INSERT INTO test_pk_triggers (name) VALUES (:"SYS_B_0") RETURNING "ID" INTO :returning_id

The literal 'alpha' was rewritten to :"SYS_B_0" by Oracle because
oracle-enhanced runs ALTER SESSION SET CURSOR_SHARING = FORCE on
connect (lib/active_record/connection_adapters/oracle_enhanced_adapter.rb:843).
System-wide cursor_sharing is EXACT.

blocking_session is empty, no rows in v$lock with BLOCK = 1 for
this session. There is no row-lock contention, no DDL contention, no
sequence wait.

Client-side (macOS sample of the rspec PID)

Main thread, deepest frames:

oci8_stmt_execute        (oci8lib_400.bundle)
oci8_OCIStmtExecute_nb   (oci8lib_400.bundle)
oci8_call_without_gvl    (oci8lib_400.bundle)
oci8_OCIStmtExecute_cb   (oci8lib_400.bundle)
OCIStmtExecute           (libclntsh.dylib.23.1)
kpuexec / kpurcsc / upirtrc / nioqwa / ttcdrv / nioqrc / nsbasic_brc
nttfprd                  (libclntsh.dylib.23.1)
read                     (libsystem_kernel.dylib)

nttfprd is SQL*Net's transport receive. Ruby is blocked reading from
the TCP socket while Oracle is asking for the client to send more data.
Classic mutual wait.

Versions

• Oracle Database 26ai Free Release 23.26.1.0.0
• compatible = 23.6.0, optimizer_features_enable = 23.1.0
• Ruby 4.0.3 (mise install)
• ruby-oci8 from current Gemfile.lock
• macOS / arm64

Likely cause

cursor_sharing = FORCE adds an implicit IN bind (:"SYS_B_0") to a
statement that already declares an explicit OUT bind (:returning_id).
The RETURNING ... INTO OUT bind uses OCI's piecewise / DATA_AT_EXEC
protocol; with the additional implicit bind, the protocol exchange
between ruby-oci8 and the Oracle 23ai server gets out of sync, neither
side flushes, and both block on read.

This is not the Symbol-vs-String bind-name bug the spec was originally
guarding against; the Ruby-side logging path is never reached because
the OCIStmtExecute call itself never returns.

Possible directions for a fix

Listed here for discussion, not as a recommendation:

1. Per-statement cursor_sharing toggle. Switch the session to
   cursor_sharing = exact around code paths that issue
   RETURNING ... INTO, restore on exit. Heavyweight but localized.
2. Convert OUT bind name from Symbol to String. Test whether the
   ruby-oci8 OUT bind path takes a Symbol-specific shortcut that
   trips the protocol; not yet verified.
3. Avoid implicit literal capture. Pre-bind the 'alpha'-style
   literal in the adapter so cursor_sharing has nothing to rewrite.
4. ruby-oci8 / Instant Client upgrade. Confirm whether a newer
   ruby-oci8 (or a different OCI client minor version) avoids the
   deadlock against 23.26 server.

Workaround for callers

establish_connection(... cursor_sharing: :exact) avoids the hang.

CI impact

The spec is currently skip-ped in PR #2615 with a reference back to
this issue.

[yahonda]

Reduced reproduction (no ActiveRecord)

After narrowing with a stand-alone ruby-oci8 driver, the bug reproduces with
5 lines and zero ActiveRecord involvement. Tested against
Oracle Database 23.26.1.0.0 + ruby-oci8 2.2.14 (kubo/ruby-oci8@d1daf45).

db = OCI8.new(user, password, dsn)
db.exec("ALTER SESSION SET CURSOR_SHARING = FORCE")
cur = db.parse("INSERT INTO t (name) VALUES ('a') RETURNING id INTO :returning_id")
cur.bind_param(1, nil, Integer)
cur.exec   # ok, returning_id populated
cur.exec   # hangs forever; server: SQL*Net more data from client

Minimal trigger conditions

All three are required; removing any one of them avoids the hang:

1. CURSOR_SHARING = FORCE (or SIMILAR) at session level.
2. SQL contains a literal and RETURNING ... INTO :out_bind together.
3. The same parsed cursor is executed more than once.

Conditions that turned out NOT to matter

The earlier hypothesis included some red herrings; matrix testing ruled them
out:

• Symbol vs String bind name (case 4 vs 5/6): all parse fine, only Symbol
  retrieves OUT correctly, but neither triggers the hang on a fresh cursor.
• Double-binding the same position (String IN -> Integer OUT) as
  oracle-enhanced's bind_params + bind_returning_param does (case 7
  is single exec OK; case 12 with single Integer-only bind also hangs).
• Re-binding before the second exec (case 10 hangs without rebind).
• Dropping and recreating the underlying table between execs (case 9 is the
  same as case 8).

Confirmed workarounds (each independent)

• W1. cursor_sharing: :exact on the connection. Avoids the implicit
  literal-to-bind rewrite by Oracle.
• W2. Bind all literal values as explicit IN binds in the SQL. Same
  effect as W1 from the OCI protocol's point of view.
• W3. Close and re-parse the cursor between executions, bypassing the
  client-side statement cache.

Implications for oracle-enhanced

The adapter sets cursor_sharing = FORCE by default
(oracle_enhanced_adapter.rb:843) and caches prepared statements
(database_statements.rb:67-71). Any caller that issues an INSERT with a
RETURNING clause and SQL-embedded literals against a cached cursor will
trip this hang on the second execution. In real Rails code paths the bind
parameters are usually already extracted, so the bug surfaces mainly via
hand-written connection.insert("INSERT ... VALUES ('literal') ...") such
as in tests.

Repro driver lives at tmp/repro_2619.rb on branch
oci8-returning-deadlock-2619 for anyone wanting to verify locally.

Next steps

1. Verify whether this also reproduces on a recent Oracle 19c / 21c server
   (server-side rewrite logic differs).
2. Open a corresponding issue against
   kubo/ruby-oci8 with the 5-line
   repro; the deadlock most likely sits on the OCI bind-state side.
3. Decide oracle-enhanced's response: change default cursor_sharing,
   guard RETURNING paths, or document the hazard.