Tables are Locking up !!!
I am running an Informix database on an IBM SCO Machine. My Java Program is connecting through a JDBC Connection which is working well. However my problem is when many users are inserting records into the database using my program there appears to be an SQL Exception thrown can not insert into <table> because it is locked. Does anyone know how I can fix this. If they use Informix in the banking industry they must receive hundreds of transactions at any given time. Any HELP would be greatly appreciated. Please respond to this group as well as the EMail below. troyd@hfx.andara.com Thanks.
1. Make sure that your table has row-level locking - examine "locklevel" in systables. 2. Allow your processes to wait on locks. Use the SQL statement "SET LOCK MODE TO WAIT 2" to allow your process to wait upto 2 seconds for a lock to be released. You can do this immediately after CONNECTing. Rudy Troy DesBarres wrote: > I am running an Informix database on an IBM SCO Machine. > > My Java Program is connecting through a JDBC Connection which is > working well. > > However my problem is when many users are inserting records into the > database using my program > there appears to be an SQL Exception thrown can not insert into > <table> because it is > locked. > > Does anyone know how I can fix this. If they use Informix in the > banking industry > they must receive hundreds of transactions at any given time. > > Any HELP would be greatly appreciated. > > Please respond to this group as well as the EMail below. > > troyd@hfx.andara.com > > Thanks.
It could be caused by referential constraints as well. Your Java engine may be interpreting a lock on a referenced table as a problem as well. Without using LOCK MODE ROW in your schema, or using SET LOCK MODE TO WAIT x in your program you will undoubtedly see this error when performing many inserts. Informix needs to gain a lock on ALL referenced tables before the row to be inserted can actually be inserted. Another thing to do would be to PREPARE your insert statement. This can also speed up your rows being inserted. -- "Troy DesBarres" <troyd@hfx.andara.com> wrote in message news:oFMkOWJ0M8B1SO6p=4oUb4x58S16@4ax.com... > I am running an Informix database on an IBM SCO Machine. > > My Java Program is connecting through a JDBC Connection which is > working well. > > However my problem is when many users are inserting records into the > database using my program > there appears to be an SQL Exception thrown can not insert into > <table> because it is > locked. > > Does anyone know how I can fix this. If they use Informix in the > banking industry > they must receive hundreds of transactions at any given time. > > Any HELP would be greatly appreciated. > > Please respond to this group as well as the EMail below. > > troyd@hfx.andara.com > > Thanks.