Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2005
    Posts
    1

    Question Unanswered: JDBC - Using SET LOCK MODE TO WAIT s

    My Java application performs an SQL query including all rows from a table that's sometimes being modified at the time of the query executing.
    Thus, I'm experiencing SQL Exceptions because the default behaviour in Dynamic Server is "LOCK MODE SET TO NOT WAIT".
    I have set the lock mode for the table to "LOCK MODE ROW", to minimize the lockng scope.
    What i need now is to set "SET LOCK MODE TO WAIT 5" on each Connection created against the database. Now my questions are fairly simple.

    1. Is it necessary to execute an SQL call for every single JDBC connection against the database?
    2. Is there a way to make all connections to that specifix database obey the "WAIT 5" rule?


    Please note that I cannot set the "WAIT 5" on the entire dbms instance (even if it was possible) since there are other databases not needing this setting.

    TIA!

    Regards
    Thor

  2. #2
    Join Date
    May 2004
    Posts
    45
    Hi Thor,

    I believe it has to be issued for each connection. As far as I'm aware there is no ONCONFIG or environment variable that can turn it on for all connections to either a particular db or informix server.
    Keith Brownlow
    ServerMetrics DB Monitoring (www.servermetrics.com)

  3. #3
    Join Date
    Oct 2005
    Posts
    6
    Yes, as far as I am awre each session must have lock mode set.

    There is an environment variable IFX_TABLE_LOCKMODE which sounds like it might be in this league, however I cannot find any information on it other than a brief reference to it in the SQL Syntax Guide under the SET LOCK MODE section.

  4. #4
    Join Date
    May 2004
    Posts
    45
    The IFX_TABLE_LOCKMODE env. variable and DEF_TABLE_LOCKMODE ONCONFIG parameter unfortunately are not what you are looking for. They deal with setting the default locking mode for data.

    Informix can lock either a specific row on a page or all rows on a page that the row being locked resides on. This can improve performance if there is a high probablility you want locked access to many rows, but obviously decreases concurrency. The manual mentions them in conjunction with SET LOCK MODE as they can influence how SET LOCK MODE works - page level locking produces a higher probability that a connection with SET LOCK MODE enabled will run into a locked page. Hope this helps.
    Keith Brownlow
    ServerMetrics DB Monitoring (www.servermetrics.com)

  5. #5
    Join Date
    Sep 2012
    Posts
    1
    While there is nothing to set the lock mode wait on a configuration parameter - perhaps the DBA could setup the sysdbopen() routine to default it for the connecting user session.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •