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!

Answer 1

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.