Problems using serializable transaction

Problems using serializable transaction

Post by Jack » Thu, 18 Feb 1999 04:00:00



Hello,

I am working on an OLTP/OLAP hybrid application.  I need to read from a
large table more than once consistently within a stored procedure.  In
the stored procedure, I also stored the intermediate data in a temporary
table for further processing (select, update, and delete) by other SQL
statements.  In the Oracle manual, it says that 'set transcation
isolation level serializable' statement will allow repeatable reads and
avoid phantom rows.

However, when I tried this, I have been getting the following error:

"ORA-08177: Cannot serialize access for this transaction"

At the time that this error occurred, there was only one user on the
database.  I have set the INITRANS to 5 for the affected tables.

Is there a limit on how long a transaction can be when using
serializable transaction?   What's the proper design for using
serializable transaction?  Is there another way of getting repeatable
reads without using serializable transaction?  

Thanks in advance!

Jack

 
 
 

1. Problem with updation in Serializable Transaction level

Hi,

In Serializable transaction level when I do an update it blocks the entire
table.
This update statement make use of index.  For some cases if the proper
index is used, this was working. In this case also If I use the primary
index
then it will work. Can anybody give some information regarding this problem

create table TASKTEST
(
TASK_ID int IDENTITY(1, 1) primary key,
SUB_CODE int,
DOC_TYPE int,
DOC_CODE int
)

Create Index Task_Test_Ind on TaskTest(DOC_TYPE, DOC_CODE)

insert into TASKTEST(SUB_CODE, DOC_TYPE, DOC_CODE) values(111, 10, 1446)
insert into TASKTEST(SUB_CODE, DOC_TYPE, DOC_CODE) values(12, 9, 1226)
insert into TASKTEST(SUB_CODE, DOC_TYPE, DOC_CODE) values(11, 9, 1443)

set transaction isolation level serializable
begin transaction
UPDATE TASKTEST  SET  SUB_CODE = 1118  Where DOC_TYPE = 10 AND DOC_CODE =
1446

Open another query analyzer
set transaction isolation level serializable
begin transaction
UPDATE TASKTEST   SET  SUB_CODE = 1118  Where DOC_TYPE = 9 AND DOC_CODE =
1443

The second query will be waiting till I give ROLLBACK or COMMIT in the first
query.
ROWLOCK also doesn't seem to be helping.

TIA,
Ashi

2. How to Filter Objects in DTS

3. Problem setting SERIALIZABLE in a distributed transaction.

4. Error #1028 Dsi Exec

5. serializable transaction problem

6. IDS / SCO / Raw Disk / 2gb Limit

7. Transaction Isolation Level Serializable Problems

8. Acc97->ODBC->SQL 7, 30% more rec, 12000% more time to get ?

9. transaction isolation level setting serializable - deadlocks

10. Serializable Transactions and Aggregate functions

11. Set Transaction ISOLATION LEVEL Serializable

12. Locking vs. serializable transactions