We are facing ORA-08177: can't serialize access for this transaction
On execution of following sql's in the order of there appearance:
0-SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
1-update date_allocated_number set dan_number = 1 where sng_id = 1;
2-select min( dan_number ) from date_allocated_number dan where sng_id = 1 ;
3-update sequential_number_group set sng_number = sng_number + 1 where sng_id = 1;
4-select sng_number from sequential_number_group where sng_id = 1;
5-insert into date_allocated_number (dan_id,sng_id,dan_dttm,dan_number,dan_delete_fl,dan_version_id,ptn_id) values(2,1,sysdate,1,'N',1,1);
6-INSERT INTO SID.SEQUENTIAL_NUMBER_GROUP(SNG_ID, CMP_ID, SNG_NAME, SNG_NUMBER, SNG_DELETE_FL, SNG_VERSION_ID, PTN_ID)
VALUES(1, 374, 'Case Number Group', 1, 'N', 1, 1);
7-commit;
throws ORA-08177: can't serialize access for this transaction
This is observed in Oracle11g.
thanks,
Sid