Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2003

    Unanswered: to block table readers ( select )

    hi all,

    How can we block table readers in oracle ?

    The lock statement :
    lock table tabname in exclusive mode
    blocks only the writers ( insert, update and delete ) upto the unlock; but not the readers ( select ) .

    But How we can block a select query's execution until unlock ?

    ie. If one user inserting a row to tabname
    and other user reading => select max(colname) from tabname.
    Here, the reader may get the previous max value upto a commit.

    Can I block this reading upto the commit execution ?


  2. #2
    Join Date
    Sep 2002
    Provided Answers: 1
    Sounds like you are trying to avoid sequences (deja vu all over again...)

    No, you can't block readers. What you can do is build code using explicit locking on both sides:

    Session 1:
    - lock something (e.g. a row in a master table)
    - insert

    Session 2:
    - lock same something as above
    - select max(col) ...

    There is also a package called DBMS_LOCK for performing bespoke locking.

  3. #3
    Join Date
    Oct 2002
    excellent way to turn a $160,000 database into dbase III.

Posting Permissions

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