Does oracle undo sequence increase after transaction rollback?

Question

I have a Spring application with Hibernate as ORM & Oracle database as RDBMS. Assume my table name in the database is entity_tbl and entity_seq is the sequence of my table.

In the save method with @Transaction, the entity was save and after saving the line, I throw an exception for rollback transaction. like below,

@Service
class EntityService extends GenericService<Entity>{

    @Autowired
    EntityRepository repo;

    @Transactional
    @Override
    public void save(Entity entity) {
       repo.save(entity);
       if(true)
           throw ApplicationException("just for Transaction rollback...")
    }
}

In application console I see this line:

select entity_seq.nextval from dual

If I run this query on PL/SQL, the entity_seq was increased and entity_seq.currval had a new value. But after transaction rollback in above code entity_seq.currval has past value and not increased.

So my question is: Does Oracle decrease sequence after transaction rollback? or oracle's temp table undo this increase? or Hibernate manage it? in other words, my question is why sequence after when I see select entity_seq.nextval from dual in application console was unchanged?

Any help would be appreciated!

I'm not sure if your observation is correct, as per [this answer](https://stackoverflow.com/questions/12104407/oracle-sequence-transactionality) and many other Oracle doesn't rollback sequence value. Are you sure the value was decreased? — Karol Dowbecki, Mar 04 '19 at 12:17
Possible duplicate of [Oracle Sequence Transactionality](https://stackoverflow.com/questions/12104407/oracle-sequence-transactionality) — Rene, Mar 04 '19 at 12:21
@karol-dowbecki my question is why sequence after `nextval` was unchanged? — Morteza Asadi, Mar 04 '19 at 12:23
@MortezaAsadi the problem is that common knowledge is that sequence are not rolled back. Are you sure that `select entity_seq.nextval from dual` was actually executed in the database? For example it could be identifier caching in Hibernate, the ids are loaded in bulk to avoid getting sequence value on every insert. — Karol Dowbecki, Mar 04 '19 at 12:46
@karol-dowbecki I'm not sure this query was actually executed in the database, But it logged in my application console, like other `select/insert/delete/update` queries. in fact I expect that like other logged queries this one also would executed in the database. — Morteza Asadi, Mar 04 '19 at 12:52

score 1 · Answer 1 · answered Mar 04 '19 at 13:00

But after transaction rollback in above code entity_seq.currval has past value and not increased.

currval never increases the sequence it contains the last assigned value independent of the ROLLBACK.

Check in pure DB without the ORM stuff.

SQL> insert into tab(x)
  2  select seq.nextval from dual;

1 row created.

SQL> insert into tab(x)
  2  select seq.nextval from dual;

1 row created.

SQL>
SQL> select * from tab;

         X
----------
         1
         2

SQL>
SQL> select seq.currval from dual;

   CURRVAL
----------
         2

SQL>
SQL> rollback;

Rollback complete.

SQL>
SQL> select * from tab;

no rows selected

SQL>
SQL> select seq.currval from dual;

   CURRVAL
----------
         2

SQL>

You see the last assigned value (2) is returned from the currval. Check for some caching effects if you see other results.

Does oracle undo sequence increase after transaction rollback?

1 Answers1