| How to read UNCOMMITED data in Oracle? [message #605384] |
Thu, 09 January 2014 01:38  |
 |
usaha1
Messages: 4
Registered: January 2014
Location: India
|
Junior Member |
|
|
I have a table:
create table Task_actn
(
actn_id number(19) primary key,
task_cde char(5),
task_stat_dsc varchar2(256),
task_actn char(1) constraint ck_01 check (task_actn in ('A','N'))
);
where 'A' stands for "Active" and 'N' stands for "New"
As per business task_actn will be 'N' for a newly created task and it'll be 'A' when it is assigned to any agent. Hence a transaction update the value of task_actn to 'A' once it read any newly created task.
Our requirement is we want to allow other transactions to read the updated value of task_actn before commit occur. As per my knowledge oracle allow only read committed and serializable isolation level and dirty read is not possible.
Is there any workaround to make this possible? Please help....
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
| Re: How to read UNCOMMITED data in Oracle? [message #605403 is a reply to message #605392] |
Thu, 09 January 2014 02:47  |
flyboy
Messages: 1903
Registered: November 2006
|
Senior Member |
|
|
usaha1 wrote on Thu, 09 January 2014 09:30Basically we are using multiple listners from application end and those listners will read the tasks from the table Continuously and Concurrently. So when a listner will be reading any particular data it'll update the 'task_actn' to 'A' at the same time. So we want if any other listner will try to read the same record before commit it'll see the 'task_actn' for that task as 'A'. So it'll not process the same record...
This is our requirement. Would you kindly suggest any workaround to implement this?
SELECT ... FOR UPDATE SKIP LOCKED;
from all listners.
http://docs.oracle.com/cd/E11882_01/server.112/e41084/statements_10002.htm#SQLRF55369
|
|
|
|
|
|
| Re: How to read UNCOMMITED data in Oracle? [message #605407 is a reply to message #605405] |
Thu, 09 January 2014 03:06 |
cookiemonster
Messages: 13920
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
|
It doesn't need to. They find a record and update it (but don't commit it). Other sessions need to skip those records. OP wanted to do it by checking the value that's updated, but that's not necessary since the record is locked.
|
|
|
|
|
|
| Re: How to read UNCOMMITED data in Oracle? [message #605430 is a reply to message #605410] |
Thu, 09 January 2014 05:46 |
|
usaha1
Messages: 4
Registered: January 2014
Location: India
|
Junior Member |
|
|
'Have each listener lock the records it's dealing with' - this is only work around as per my understanding. It'll not allow other listner to read the uncommit data however also not allow to read the locked record untill commit.
Thanks to all for your inputs.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
