Results 1 to 6 of 6

Thread: Records not locked in the order specified

  1. #1
    Join Date
    May 2011
    Posts
    3

    Records not locked in the order specified

    I have a query that runs like this:

    Select * from
    table1,
    table2
    where
    table1.field1 = table2.field1 and
    table1.field2 = table2.field2 and
    table1.field3 = table2.field3 and
    table1.field4 = table2.field4 and
    order by
    table2.field1,
    table2.field2,
    table2.field3,
    table2.field4

    But when i run parallel instances of my application (approx 20), this query is dead-locking on itself. Which is surprising.

    I expect that all instances of my application should lock records in the same order specified by the 'order by' clause. But apparently that is not happening.
    I'm using Oracle version 11g

  2. #2
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    SELECT on Oracle database does not lock records. There must be some DML activities going on that caused deadlock.

  3. #3
    Join Date
    May 2011
    Posts
    3

    Selecting for update

    Missed adding this at the end of the query.

    it was a

    Select
    xxx

    for update

  4. #4
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    Oracle may parallelize your query and different queries may still collide as they may fetch rows in different sequence.

    How many rows the query returns?. Are the fields in the query indexed?

  5. #5
    Join Date
    May 2011
    Posts
    3
    The query returns approx 20000 records (from same table). And I'm running 10 parallel instances of the application, each returning 20000 records.

    But the order by clause is on the primary key (UKP actually) on the table.
    So records being fetched & locked in various orders should not happen theoritically.

  6. #6
    Join Date
    Nov 2002
    Location
    New Jersey, USA
    Posts
    3,932
    ORDER BY happens at the end of query plan on the result set, it does not dictate the order of table scan. It is not a good idea to have for update cursor for such a large result set running in parallel.

Posting Permissions

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