hi all ,
i have an oracle database and i want user not to block the table while doing update or insert and others will wait till they finish.

i thought of a FIFO file , that i can create under Linux , where i can put all the transactions there and i will not lock the users from work ..
this fifo file , will face IN and OUT pointers , one is used to read and the other to write .
the transactions will be executed one by one and i will not have lock or freeze system .


can i achieve that with mkfifo in Linux ?
am i using the right commands and is it true what i thought doing ?

tx for ur replies.
Regards

I don’t fully understand your problem and I don’t think a pipe will help you, as the data would still be read from the table to the pipe

But locking is an important function. Don’t just bypass it unless you are 100% sure your code can handle it.

e.g.
Your partner goes to the bank to withdraw $1000
At the same time you want to withdraw $1000 from another bank
Transaction 1 read the value to check for available funds and hold a read lock
Transaction 2 read the value to check for available funds and hold a read lock
Transaction 1 try to update value to 0 but wait for read lock held by transaction 2
Transaction 2 try to update value to 0 but wait for read lock held by transaction 1
A deadlock occur and transaction 2 is terminated while transaction 1 completes
And if transaction 2 is resubmitted it can fail with insufficient funds
If you didn’t have locking both will be able to withdraw $1000

Obviously it is better for concurrency if you can commit more often and read without holding a lock.
In this case you change the update to update amount=amount+-value where amount=the amount you read
If the update can’t find the record it means it was changed and you start again at the read.

Hi,

in fact i am facing a problem when people are doing a search for a criteria ( ex: A*) or ( BC*) and suppose i have 100 person doing that search using wildcards , i have a slow response and i have to wait for a time before i will got the results of the search.
and maybe 2 persons are doing the same search so my system will be very slow .

so i thought about FIFO to minimize the wait time .
put all these select in the FIFO file and later on create something to retrieve them and execute them.

am i thinking right ? is this feasible ?

but i dunno how to use this FIFO , to be able to write to it and read from it .

Tx for the replies
Regards

Searching with a wildcard can use an index provided the search criteria doesn’t start with a wildcard and the column is indexed.

You can only read the data once from a fifo file. If you want to read a 2nd time you’ll have to write a 2nd time.

You can try and copy the data to a temp table where people can search without affecting the base table but with a lot of updates and inserts your temp table/file/whatever will become outdated quickly and the search will not find the correct results. Investigate using NOLOCK for your query and submitting your contention problem on the Oracle forum.

But Below is an example of how I use a pipe to copy data from one Sybase server to another.mkfifo make the FIFO special file
bcp is a bulk copy program that can transfer data from a table to a file or vice versa
The bcp out is submitted in background to write to the pipe
And bcp in then read from the pipe