Can I get ORA-08177 if there's only one connection to the database?

Question

I've been tasked with running the unit tests on a storm backend for oracle so that we can see if the backend is of sufficient quality to use in production. One problem that I'm running into is that I'm getting ORA-08177 (can't serialize access for this transaction) if I connect in serializable mode. The problem goes away when I use read committed mode.

Now I've read this ask tom article and it indicates that this is basically a concurrency issue.

Assuming that I've only got one thread connecting to the database and no one else in the world is connecting to this database, is it possible to get this error? And if so, could someone provide me with an example query that would generate this error?

Or is this likely indicative of a cursor or connection not being closed somewhere? Or a transaction not being committed or rolled back?

Answer 1

With only one session you shouldn't get this error. The following script however will spawn a secondary session that will update a row independantly of the first session, allowing us to trigger an ORA-8177. I'm not sure if this is what you want.

Consider:

SQL> alter session set isolation_level=serializable;

Session altered
SQL> create table test (a number);

Table created
SQL> insert into test values (1);

1 row inserted
SQL> commit;

Commit complete
SQL> declare
  2     pragma autonomous_transaction;
  3  begin
  4     update test set a = 2;
  5     commit;
  6  end;
  7  /

PL/SQL procedure successfully completed
SQL> update test set a = 3;

update test set a = 3

ORA-08177: can't serialize access for this transaction

Answer 2

"There are triggers involved, but I'm getting the errors on DDL statements, not on update or insert. "

DDL statements should handle their own committing. Basically they do a commit, then the metadata changes (which can involve a number of underlying objects) then a commit again (assuming the DDL succeeds - if it fails the change should be rolled back).

So if you were doing DDL, it should be safe to commit, change the transaction to read committed, do the DDL, then change the transaction back to serializable. If you can give a full test case (or at least the sort of DDL you are talking about) it may help. A materialized view creation or CREATE TABLE AS SELECT, for example, may be 'odd' as it would be DDL (with its peculiar committing) plus DML.