-
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
-
SELECT on Oracle database does not lock records. There must be some DML activities going on that caused deadlock.
-
Selecting for update
Missed adding this at the end of the query.
it was a
Select
xxx
for update
-
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?
-
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.
-
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
-
Forum Rules
|
|