You Asked
Hi,
We have two sessions:
Setup Data:
insert into table1 values(table1_seq.nextval, 'A', 'AA');
insert into table1 values(table1_seq.nextval, 'B', 'BB');
commit;
Session 1:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
select * from table1;
declare
begin
/* Updating row 1 */
update table1 set name = 'A-Updated' where id = 22;
DBMS_LOCK.sleep(5);
commit;
end;
/
Session 2:
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
select * from table1;
declare
begin
/* Updating row 2 */
DBMS_LOCK.sleep(5);
update table1 set name = 'B-Updated' where id = 23;
DBMS_LOCK.sleep(5);
commit;
end;
/
Both sessions are running in parallel and mostly I get an error on the update trigger.
ORA-08177: can't serialize access for this transaction
ORA-06512: at TU_TABLE1", line 10
ORA-04088: error during execution of trigger 'TU_TABLE1'
Can you please suggest whats the issue here? Also, our application does this using the ojdbc6 driver as our's is a java application. I've tried to mimic the scripts and behavior on the db side.
Please see below the sample scripts we use to create the db objects:
CREATE TABLE TABLE1
(
ID NUMBER(10) NOT NULL PRIMARY KEY,
NAME VARCHAR2(10) NOT NULL,
IDENTIFIER VARCHAR2(10 CHAR)
)
INITRANS 1
MAXTRANS 255;
CREATE SEQUENCE TABLE1_SEQ
START WITH 21
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER;
CREATE SEQUENCE Z_TABLE1_SEQ
START WITH 61
MAXVALUE 999999999999999999999999999
MINVALUE 1
NOCYCLE
CACHE 20
NOORDER;
CREATE TABLE Z_TABLE1
(
UPDATE_SEQ NUMBER(10) NOT NULL PRIMARY KEY,
UPDATE_TIME TIMESTAMP(8) NOT NULL,
ACTION VARCHAR2(1 CHAR) NOT NULL,
USER_ID VARCHAR2(50 CHAR),
APPL_ID VARCHAR2(200 CHAR),
ID NUMBER(10),
NAME VARCHAR2(10),
IDENTIFIER VARCHAR2(10 CHAR)
)
INITRANS 1
MAXTRANS 255;
CREATE OR REPLACE TRIGGER ti_TABLE1
AFTER insert ON TABLE1
FOR EACH ROW
BEGIN
insert into z_TABLE1
VALUES (z_TABLE1_seq.nextVal , systimestamp , 'i' , 'USER' , 'APP' , :NEW.id , :NEW.name , :NEW.identifier) ;
return ;
End ;
/
CREATE OR REPLACE TRIGGER tu_TABLE1
AFTER update ON TABLE1
FOR EACH ROW
BEGIN
insert into z_TABLE1
VALUES (z_TABLE1_seq.nextVal , systimestamp , 'i' , 'USER' , 'APP' , :NEW.id , :NEW.name , :NEW.identifier) ;
return ;
End ;
/
and Chris said...
The trigger is a red herring. You can still see this effect without it!
Why?
Well, as the docs say:
Under some circumstances, Oracle Database can have insufficient history information to determine whether a row has been updated by a too recent transaction. This can occur when many transactions concurrently modify the same data block, or do so in a very short period. You can avoid this situation by setting higher values of INITRANS for tables that will experience many transactions updating the same blocks. Doing so enables Oracle Database to allocate sufficient storage in each block to record the history of recent transactions that accessed the block. https://docs.oracle.com/cd/B28359_01/server.111/b28318/consist.htm#CIHGIAJJ So the problem is you have two sessions updating the same block at the same time.
To avoid this problem you could try:
- Increasing the value for initrans for your tables.
- Enabling rowdependencies (see MOS note 160593.1). Note you'll need to recreate the table to set rowdependencies.
- Changing the update to catch the ORA-8177 exception. Then retry if you hit it, e.g.:
declare
cant_serialise exception;
pragma exception_init(cant_serialise, -8177);
begin
/* Updating row 2 */
<<retry>>
begin
update table1 set name = 'B-Updated' where id = 23;
exception
when cant_serialise then
rollback;
goto retry;
end;
commit;
end;
/
- Stop using serializable transactions! ;)
If you're sticking with serializable and you're on 11.2+ you may want to make your tables "segment creation immediate" (deferred is the default from this release). Shouldn't be an issue for your updates. But may cause problems with new tables. See MOS note 1285464.1.