Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2005
    Posts
    48

    Question Unanswered: mkfifo and oracle

    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

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    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.

  3. #3
    Join Date
    Feb 2005
    Posts
    48

    Exclamation mkfifo and oracle

    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

  4. #4
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    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
    Quote Originally Posted by alfrednone
    how to use this FIFO
    Below is an example of how I use a pipe to copy data from one Sybase server to another.
    Code:
    mkfifo bcp.tmp
    bcp maydb.dbo.mytable out bcp.tmp –Ssrv1 -Usa -Pxxxxxx -T1024000 -n &
    bcp testdb.dbo.mytable in bcp.tmp -Ssrv2 -Usa -Pxxxxxx -T1024000  -E -n -b10000
    rm bcp.tmp
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •