Running multiple sql queries in a single thread?

Question

I'm trying to run multiple SQL queries in a single SQL server thread through a java code. I tried using batch run for my queries, but seeing the logs, my queries were running in different threads.

Is there a way to run all my queries under a single thread?

I have enabled concurrency flag for my queries so that read/write operations do not conflict and resulting in an exception.

Do you mean "one transaction"? What do you see in the logs? What do you mean by "_I have enabled concurrency flag_"? — Kai, May 04 '12 at 06:04
If you want to run them in a single thread I doubt there will be any concurrency issues, however user714965 is right there's not enough information available to understand the problem properly. BTW user714965 change your name will you its weird to see the default user tag in someone with your score! — Thihara, May 04 '12 at 06:18

score 2 · Accepted Answer · answered May 04 '12 at 06:22

2

You have to handle the transaction manually by turning off auto commit and make a commit after you run your statements:

connection.setAutoCommit(false);
statement.executeUpdate();
connection.commit();

answered May 04 '12 at 06:22

Kai

38,985
14
88
103

Will this guarantee that I have a single thread for queries execution? – Ankit Garg May 07 '12 at 09:24
Your queries will run in one transaction in order to avoid concurrent modifications. See [Using Transactions](http://docs.oracle.com/javase/tutorial/jdbc/basics/transactions.html) for reference. – Kai May 07 '12 at 10:02
But the point is, I'm not using my queries for transaction related purposes. I'm just creating a group of select statements to execute after a certain limit so that sql overheads are kept to minimum. – Ankit Garg May 07 '12 at 10:44
Ok, I don't get this. A batch can't be used to execute SELECT-statements. – Kai May 07 '12 at 10:53
But I have successfully used batch to exectue SELECT statements. Can you tell me why we cannot use batch for SELECT statements? – Ankit Garg May 07 '12 at 10:55
Your question is not clear. Please rephrase it and add the statements you want to execute. – Kai May 07 '12 at 11:01

Subhrajyoti Majumder · Answer 2 · 2012-05-04T06:20:47.943

1

You can create pl/sql function and can put all your query into that function.

or execute multiple statement with single connection whit out closing it.

edited May 04 '12 at 06:20

answered May 04 '12 at 06:14

Subhrajyoti Majumder

40,646
13
77
103

@Quoi I think when executing in batch, there is a single MySQL server connection and what i hoped was that the queries would run under one mysql thread. But this is not happening. I was wondering if anyone would have leads on this one. – Ankit Garg May 07 '12 at 09:23

score 0 · Answer 3 · edited May 23 '17 at 10:08

0

I am not sure but you can create procedure for multiple sql queries and then call it from your java code. This and this may lead to the way.

edited May 23 '17 at 10:08

Community

1
1

answered May 04 '12 at 05:59

Ved

8,577
7
36
69

Running multiple sql queries in a single thread?

3 Answers3