Transaction management

[vivekm]

I am looking to control transaction processing in my application. The requirement is as follows:
* If process 1 is reading rows from a table, process 2 should be able to read from the same table as well.
* If process 1 is reading rows from a table, process 2 should not be able to modify the data including inserts until the other process has completed selecting data. Therefore, process 2 should be in a wait state until process 1 has completed selecting data.

Suggestions please.

Thanks in advance,
Vivek

 

[faheemrao]

you would not believe that I am having the same problem , but right after reading your question I come with a Solution !!!


create a table in Database name it transaction_Control.

When Process 1 reads that table set a flag in that table.
Proces 1 Should send message to that table(transaction_Control) every a specific number of seconds ( say 10 seconds, ) and when process 1 complete unset that flag in that table.
Now process 2 before going to hit the the table should go to the "trascation_control" table and check the flag and if flag allows then go and hit the table( the Actuall data table).
Now if process 1 is get killed(crashes) in the middle , then who is going to clear the flag.
Write stored procedure in database that check the message from process 1 every specific number of seconds , and if last massege from the process 1 is tooo old ( you can find out by comparing last message time to system time )then clear the flag.



Now in this whole process if you think , is a kind of same sitution but the differnet is that trasaction of setting and unsetting a flag would not tooo long ). so wait state for process 2 would not be longer than milli seconds. )).

try to implement this and see if it helps . )


Faheem Rao

 

[carp]

An easier way might be to use "FOR UPDATE OF" in Process1.

Instead of "SELECT * FROM emp WHERE deptno = 10", Process1 should query
"SELECT * FROM emp WHERE deptno = 10 FOR UPDATE OF deptno;".
This will place a row lock on every row that the query returns. Consequently, Process2 can read the rows but not modify them.

If you use this approach, be sure to issue a COMMIT or ROLLBACK command. Otherwise, the locks remain in place as long as the session that invoked Process1 is running!

 

[faheemrao]

Dear Mr Carp
your Solution looks Good but the thing is that when process 1 Locks a row , and process 2 tries to update the same locked row then process 2 will be in a wait state and the whole issue is to avoid wait state for process 2. in your solution anyhow process 2 have to be in a wait state )

 

[sem]

The second process may also try to lock the same row but with nowait flag to return control immediately.
To avoid inserts you have to lock all table in exclusive mode.

 

[faheemrao]

Dear mr Sem,

As far as I know, if process 2 want to updated a row locked by process 1 , process 2 have to be in wait state untill process 1 say commit( release the lock).

if you dont agree to me ,
I would appreciate a good reason ))


Faheem Rao

 

[sem]

Of course, I don't agree, because you explain the default, though overridable behavior.

Open 2 sessions in sql*plus(s). Assuming you have some table named A run the following commands:

1st session: lock table A in exclusive mode;
2nd session: lock table A in exclusive mode nowait;

As you see, though the first session has an exclusive lock on table a, the second session doesn't wait and may continue to work after catching ORA-00054 error.