0

I am doing insertion into a Oracle table using batchUpdate. Five different parallel threads are invoking batchUpdate method to insert into a single table. Table is of Global Temporary Table type.

Program execution is not completing, its getting stuck after establishing database connection.

Will multiple threads operating on a single global temporary table cause table lock and hold the execution of the program?

I can see the locked table using below query-

 select object_name, object_type from all_objects where object_id in (select object_id from v$locked_object);
Mithun Khatri
  • 636
  • 3
  • 9
  • 22
  • 2
    Why five threads? Why not have one thread do the insertions and the other five "push" values to it? – fge May 21 '14 at 06:19
  • is it blocking or locking? Assuming the first thread commits the transaction, then the second thread should be able to do its work. How else would data pools work otherwise. – Scary Wombat May 21 '14 at 06:39
  • 1
    Do the threads have a connection/session each, or are they sharing and competing for the same one? If it really is a GTT then sessions shouldn't intefere with each other, as they only see their own data anyway. Maybe one thread is holding the single connection and the other four threads are waiting on that, at Java level, not in the DB? – Alex Poole May 21 '14 at 07:03
  • @fge there are 5000 records to be inserted. So its devided into 5 parts and inserted using 5 parallel threads to improve performance. Its done under save session. I suspect in same session, one thread may be causing a table lock for the other thread. – Mithun Khatri May 23 '14 at 07:10
  • @IwishIcouldthinkofagood Its in a single transaction. And commit is done once all 5 threads are executed. – Mithun Khatri May 23 '14 at 07:11
  • @AlexPoole Yeah Alex, it can be the reason. Lets investigate. – Mithun Khatri May 23 '14 at 07:12

2 Answers2

2

Yes they will.

If 2 different sessions are updating the same table at the same time, the second session will not be able to access the table until the first table is done updating(COMMIT executed). The first session actually deploys a 'LOCK' on that table until it is done updating.

You cannot really update exactly the same block of data by two threads at exactly the same time can you?

It's a ORACLE feature to maintain data consistency.

A Nice Guy
  • 2,676
  • 4
  • 30
  • 54
  • 3
    Two sessions updating a global temporary table only see their own data though, so it sounds like multiple thres accessing the same connection/session? If it really is a GTT. – Alex Poole May 21 '14 at 07:00
0

Please send output of that query. Oracle supports many types of locks. Some of them can be shared (for example TM ones). The fact that you see something in v$locked_object view does not mean that some session is blocked.

Most likely you table is locked only for DDLs (ALTER TABLE) statements. But not for DML.

ibre5041
  • 4,903
  • 1
  • 20
  • 35