I have a java multithread program, all the threads try to lock the table when i do update.
1. Will select 100 records from table A
2. Each records selected will be processed and updated
3. Records selected must be differ from other threads
The program suffers lock from table and it made the program hangs, can anybody suggest me?
I believe you are not doing what you want the best way.
First of all, from what you say, it seems like you select the rows you want to update and then do one-row updates : this is not efficient at all and should be avoided. If you can (which is the general case), use an set-update on all the rows you want to modify. If you can't, then you'd better use a stored procedure to do the 100 rows job because you will have much less client-server round-trips.
Secondly, it is quite rare to have to lock tables in Oracle (while it can be done often on other RDBMSs like SQL Server 2000). In Oracle readers never block writers and still are always guaranteed to get a set of results that existed in the database at some point in time (in general at the beginning of the query). This is called read-consistency. To achieve that, Oracle uses UNDO data to read blocks in the state they were at the beginning of the query, which is why no data needs to be locked. In SQL Server you can either lock the data you want to read or do "dirty reads" (read blocks in their current state as you get them, no matter if the last block you read have been modified several times since the beginning of the query, so that you end up with a result that might never have existed in the database as a whole).
Now in order to help you I need to understand what you want to do. What is your goal ? Could you be clearer about what each thread has to do ? How are you locking the table right now and why exactly ?
ORA-000TK : No bind variable detected... Shared Pool Alert code 5 - Nuclear query ready .