Insert Command Creating Table Locking Problem

UNIX for Advanced & Expert Users
1

Hi,

i have a java based tool which does insert operation in a TABLE, and in parallel the same table is used by my C++ code which does select Query.

the Table will be always busy, but sometimes the table is getting locked when i try to make an insert, am bit confused whether the lock is because of SELECT query from my C++ code or by the INSERT QUERY from the java Tool.

Do i need to modify the Query if so how..? and from the google i learnt that select puts an internal LOCK.

currently am locking the table before insert below is my query

lock table table_name in exclusive mode nowait

and giving a commit after performing the DML operation so that lock will be removed automatically.

can anyone please solve my query.?

thanks,
senthil

2

First dont post duplicate posts
http://www.unix.com/programming/223771-table-locking-insert-command.html\#post302806459

As far as I know SELECT wont put any type of locks on table. Even when you apply exlusive lock Select work on the table.

Just check your java code whether its not calling one more instance before commit?

3

hi,

thanks for your reply and sorry for double posting. you mean to say i should not do commit after all the insert operations are done but should do one by one

eg: Lock ,Insert and commit one by one.

not overall commit for all insert..?

is that correct..?

thanks,
senthil

4

Overall commit is fine however just make sure when you have applied the exclusive lock using java and no other thread of same java code access the table..

Also are you sure no other process other than your java and C++ code access this table?

5

Let me explain the Process

C++ code do a select every 30 secs during busy hours and evry 5 minute during non busy hours.

and my tool will be inserting anytime accessing the same table used by select operation from C++ code.

will exclusive Lock create any problem if the thread is not handled properly..? and since am locking the table for each and every insert , will that be a problem.

thanks,
senthil

6

Thanks got it!!

Since you are inserting the data and not updating it I dont think you need table level exclusive lock. Insert will have its own row level lock which is sufficient.

Table level locks are usually applied when you want to update some data and make sure user access the updated data only..

7

thanks,

so can i remove exclusive mode nowait from the lock query ,when am trying to insert the values into the table.

---------- Post updated at 06:48 PM ---------- Previous update was at 06:19 PM ----------

the Tool will be used by three four users in parallel ..? will that have any impact if i remove the lock?

8

Since all users are inserting the new data into the table I dont think it will impact any of them unless table has any specific constraint in which case user might get few constraint related issues.

9

thanks,

so for insert i can do direct insert query no need to put an external lock and then do insert..? even if am inserting 30 rows at time..

10

Yes thats correct... as per my knowledge it shoudn't cause any issues :b: