1

While practicing DBMS and SQL using Oracle Database, when I tried to fire 2 select queries on a table the database always wait for the first query to finish executing and keeps the other one in pipeline apparently.

Consider a table MY_TABLE having 1 million records with a column 'id' that holds the serial number of records.

Now my queries are:-

Query #1 - select * from MY_TABLE where id<500001; --I am fetching first 500,000 records here

Query #2 - select * from MY_TABLE where id>500000; --I am fetching next 500,000 records here

Since these are select queries, these must be acquiring a read lock on the table which is a shared lock. Then why this phenomenon happens? Please note the sample space or domain for both queries are mutually exclusive to the best of my knowledge here because of the filters that I applied via where clause and this further aggravates my confusion.

Also, I am visualizing this in form of that, there must be some process which is evaluating my query and then doing a handshake with the memory(i.e. resource) for fetching the result. So, any resource in shared lock mode should be accessible to all process which hold that lock.

Secondly, is there any way to override this behavior or execute multiple select queries concurrently.

Note:- I want to chunk down a particular task(i.e. data of a table) and enhance the speed of my script.

Sharad Nanda
  • 406
  • 1
  • 15
  • 27
  • How are you running the queries? Command line or some GUI tool? – Shiva Sep 06 '19 at 06:40
  • 4
    Your reasoning is not correct - repeat the same with *two different tables* and you will observe the same behaviour. **Per session you can have only one running query**. Simple set up *more sessions*. – Marmite Bomber Sep 06 '19 at 07:09
  • @Shiva I am running these queries via SQL Developer (Oracle) – Sharad Nanda Sep 06 '19 at 08:19
  • 1
    @MarmiteBomber Yes, you are correct and sorry it's my bad I missed checking that. But please guide, in cases like these, shouldn't we be allowed to run multiple queries in parallel as their execution is independent of each other. Is there any DBMS(DB Management System) or ANSI-SQL concept that you feel I am missing out here. I am aware of ACID part but does running queries from different worksheets also count as same transaction? – Sharad Nanda Sep 06 '19 at 08:38
  • 1
    The problem is with the way you are using the client(SQL Developer). Check the answers here - https://stackoverflow.com/questions/3237894/run-2-queries-at-the-same-time-on-oracle-sql-developer – Shiva Sep 06 '19 at 09:41

1 Answers1

5

The database doesn't keep queries in a pipeline, it's simply the fact that your client is only sending one query at a time. The database will quite happily run multiple queries against the same data at the same time, e.g. from separate sessions.

Jeffrey Kemp
  • 59,135
  • 14
  • 106
  • 158
  • @Jefferey so that means it's client application which is doing batch processing even though I tried with different SQL worksheets also. Can we overcome this limitation somehow as by DBMS(Database Management System) principles this intended operation is legitimate. – Sharad Nanda Sep 06 '19 at 08:30
  • 1
    @SharadNanda - are your different worksheets sharing the same connection/session? (See Tools->Preferences->Database->Worksheet->New Worksheet to use unshared connection) – Alex Poole Sep 06 '19 at 08:50
  • @AlexPoole thank you so much! yes now I can the queries in parallel. – Sharad Nanda Sep 06 '19 at 10:48