Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2002
    Posts
    37

    Unanswered: Question about locks and 'skip locked'

    Hello,

    The situation is as follows;

    TableA contains thousands of records. The 'Status' column of exactly 3 records is '3'.

    There's an application running right now, where background jobs try to perform selects similar to this every 2 or 3 seconds :

    select [...] from TableA where status = 3 for update skip locked

    Right now (at the time I write this) when I perform this select manually, I get no records.

    But when I perform a select like this (without the 'for update skip locked' clause at the end) :

    select [...] from TableA where status = 3 <-- returns 3 rows

    exactly 3 records are shown.

    select [...] from TableA where status = 3 for update <-- timeout

    select [...] from TableA where status = 3 for update skip locked <-- returns nothing



    My question is; a session is locking these rows, ok, but how can I select them by performing :
    select [...] from TableA where status = 3

    afaik Oracle doesn't allow 'uncommitted reads' ?

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >My question is; a session is locking these rows, ok, but how can I select them by performing :
    >select [...] from TableA where status = 3

    I am not clear to what "them" refers to above.
    I suspect the culprit is you.
    What happens if you log out from DB & then start a new session?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Oct 2002
    Posts
    37
    Thanks for the quick answer.

    'them' < three rows with status column = 3


    Ok, I have to explain that in detail :

    I have a web app running on Websphere. The app and the background jobs get their db connections through a connection pool.

    Normally, I wouldn't have any access to the database. so I just prepared a jsp where I can perform DMLs. the jsp does get its connection from the pool as well.

    so, unfortunately I don't have permission to restart the server, so I can't log out.

    the background jobs lock them (by 'select ... from tableA where status = 3 for update skip locked'), read those 3 rows and release the locked rows after reading the ID column of those 3 rows (they hand each ID over to other threads, which in turn lock the row explicitly with a 'where ID = .. for update skip locked' select).

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >'them' < three rows with status column = 3
    select [...] from TableA where status = 3;

    SELECT above will return the rows, but you already knew that.
    I guess I still do not understand what problem needs to be solved.
    How would an independent observer know when a correct response has been posted?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Oct 2002
    Posts
    37
    Quote Originally Posted by anacedent View Post
    >'them' < three rows with status column = 3
    select [...] from TableA where status = 3;

    SELECT above will return the rows, but you already knew that.
    I guess I still do not understand what problem needs to be solved.
    How would an independent observer know when a correct response has been posted?
    My question :

    1) select [...] from TableA where status = 3 for update <-- timeout
    2) select [...] from TableA where status = 3 for update skip locked <-- returns nothing
    3) select [...] from TableA where status = 3 <-- returns 3 rows

    '1)' is ok, since rows with status column set to 3 are locked
    '2)' is ok, since all rows with status column set to 3 are locked, they are being skipped

    but '3)' :
    Let's say, someone starts a transaction and select row(s) for update :

    select [...] from TableA where status = 3 for update skip locked

    Now at the time I try to select the very same rows with a simple select statement, they are locked, how am I able to read those rows ?

    I'd expect that attempt '3)' would return nothing or just be blocked !??

    (But that's obviously not the case, hence I need help on understanding that.)

  6. #6
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    >Now at the time I try to select the very same rows with a simple select statement, they are locked, how am I able to read those rows ?
    With Oracle readers do NOT block writers & writers do [B]NOT[B/] block readers.
    Oracle provider everyone a Read Consistent View of the data.

    Introduction to the Oracle Database
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  7. #7
    Join Date
    Oct 2002
    Posts
    37
    that was exactly what I needed.

    I will read that, and in case something is still not clear, I'll ask again through this thread.

    Thanks again.

Posting Permissions

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