473,326 Members | 2,192 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

SET CURRENT LOCK TIMEOUT

Hi all,
First of all, just want to let you know that I'm coming from
Informix background, and we just recently migrated to DB2. It's
been a great new learning experience. For the time being, I have
some questions regarding LOCK TIMEOUT and its compatibility with
LOCK MODE WAIT in INFOMIX.
Last week, I just found out that the following statement was
complied and built in Stored Procedure

SET LOCK MODE TO WAIT 5;

What I would like to achieve is for the application to wait for 5
seconds before it returns lock timeout error (SQLSTATE 40001).
And after I did some reading from DB2 Docs, that statement should
be equivalent to

SET CURRENT LOCK TIMEOUT 5;

But when I tested the stored procedure, I did not get the lock
timeout.
The Procedure waited indefinitely until I released the lock on the
table.
Does anyone know if this is a bug ? I am in contact with the support,
but
I would like to find out if anyone is experiencing the same thing. The
following is taken out of DB2 Information Center :

|WAIT integer-constant
| Specifies an integer value between -1 and 32767. A value of -1 is
| equivalent to specifying the WAIT keyword without an integer value.
| A value of 0 is equivalent to specifying the NOT WAIT clause. If the
| value is between 1 and 32767, the database manager will wait that
| number of seconds (if a lock cannot be obtained) before an error
|(SQLSTATE 40001 or SQLSTATE 57033) is returned.
|
|For compatibility with Informix:
|
| MODE can be specified in place of TIMEOUT.
| TO can be specified in place of the equals (=) operator.
| SET LOCK WAIT can be specified in place of
| SET CURRENT LOCK TIMEOUT WAIT.
| SET LOCK NO WAIT can be specified in place of
| SET CURRENT LOCK TIMEOUT NOT WAIT.

DB2 V.8.2 FIXPACK 8
LINUX SUSE 8

Thanks
N.

Nov 12 '05 #1
5 10999
<ha*********@gmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
Hi all,
First of all, just want to let you know that I'm coming from
Informix background, and we just recently migrated to DB2. It's
been a great new learning experience. For the time being, I have
some questions regarding LOCK TIMEOUT and its compatibility with
LOCK MODE WAIT in INFOMIX.
Last week, I just found out that the following statement was
complied and built in Stored Procedure

SET LOCK MODE TO WAIT 5;

What I would like to achieve is for the application to wait for 5
seconds before it returns lock timeout error (SQLSTATE 40001).
And after I did some reading from DB2 Docs, that statement should
be equivalent to

SET CURRENT LOCK TIMEOUT 5;

But when I tested the stored procedure, I did not get the lock
timeout.
The Procedure waited indefinitely until I released the lock on the
table.
Does anyone know if this is a bug ? I am in contact with the support,
but
I would like to find out if anyone is experiencing the same thing. The
following is taken out of DB2 Information Center :

Thanks
N.


The SET CURRENT LOCK TIMEOUT is an override for only for application that
invokes that command. So if you set in one application, the other
application will use the db configuration setting (see below). Of course, an
application is not blocked by locks taken by SQL statements in the same
application thread.

If you want to set it for all applications in a database, use the following
from the command line:

db2 connect to <database-name>
db2 update db cfg using locktimeout 5
Nov 12 '05 #2
Hi Mark,
Thank you for your reply. Yep, we have our db cfg SET the
LOCK TIMEOUT to 10. But one this particular application, I
just want it to wait for 5 seconds. So we issued the SET
LOCK MODE TO WAIT 5; statement for it. :)

Thanks
Mark A wrote:
<ha*********@gmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...
Hi all,
First of all, just want to let you know that I'm coming from
Informix background, and we just recently migrated to DB2. It's
been a great new learning experience. For the time being, I have
some questions regarding LOCK TIMEOUT and its compatibility with
LOCK MODE WAIT in INFOMIX.
Last week, I just found out that the following statement was
complied and built in Stored Procedure

SET LOCK MODE TO WAIT 5;

What I would like to achieve is for the application to wait for 5
seconds before it returns lock timeout error (SQLSTATE 40001).
And after I did some reading from DB2 Docs, that statement should
be equivalent to

SET CURRENT LOCK TIMEOUT 5;

But when I tested the stored procedure, I did not get the lock
timeout.
The Procedure waited indefinitely until I released the lock on the
table.
Does anyone know if this is a bug ? I am in contact with the support,
but
I would like to find out if anyone is experiencing the same thing. The
following is taken out of DB2 Information Center :

Thanks
N.


The SET CURRENT LOCK TIMEOUT is an override for only for application that
invokes that command. So if you set in one application, the other
application will use the db configuration setting (see below). Of course, an
application is not blocked by locks taken by SQL statements in the same
application thread.

If you want to set it for all applications in a database, use the following
from the command line:

db2 connect to <database-name>
db2 update db cfg using locktimeout 5


Nov 12 '05 #3
<ha*********@gmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...
Hi Mark,
Thank you for your reply. Yep, we have our db cfg SET the
LOCK TIMEOUT to 10. But one this particular application, I
just want it to wait for 5 seconds. So we issued the SET
LOCK MODE TO WAIT 5; statement for it. :)

Thanks

I assume you are still having a problem?

An application does block itself with DB2 locks, it only blocks other
applications. The definition of an application is a particular instance of
an program that is running and connected to DB2.

If that is not your problem, then you may have discovered a bug with the
Informix compatibility syntax using SET LOCK MODE TO WAIT 5, and you might
want to try SET CURRENT LOCK TIMEOUT 5. If that does not work, and you have
the latest fixpack (FP10) open a PMR with DB2 support.
Nov 12 '05 #4
Yep, Thank you Mark. The problem is

"SET CURRENT LOCK TIMEOUT 5;" works like a champ.
"SET LOCK MODE TO WAIT 5;" waits indefinitely. I believe
those 2 statements should be equivalent, am I correct ? I
know I can easily fix the statement and use LOCK TIMEOUT
on all of them. I just want to verify that I'm not doing anything
wrong and if this is a bug.

Thanks
N.

Nov 12 '05 #5
ha*********@gmail.com wrote:
Yep, Thank you Mark. The problem is

"SET CURRENT LOCK TIMEOUT 5;" works like a champ.
"SET LOCK MODE TO WAIT 5;" waits indefinitely. I believe
those 2 statements should be equivalent, am I correct ? I
know I can easily fix the statement and use LOCK TIMEOUT
on all of them. I just want to verify that I'm not doing anything
wrong and if this is a bug.

Thanks
N.

The two statements should be identical. I tried to repro (on Win Xp with
DB2 V8.2.3) but could not. Using SET LOCK MODE TO WAIT 5; properly times
my example out. Can you post a complete script?

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Nov 12 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

14
by: Antoon Pardon | last post by:
The queue and condition class allow threads to wait only a limited time. However this currently is implemented by a polling loop. Now for those who like to avoid polling I have here a Tlock...
2
by: john.livermore | last post by:
I am tracing a SQL Server 2000 production server that gets a query about every second. The Event I chose to watch was "Lock:Timeout". To my surprise I see many of these come through the trace. ...
8
by: Mark A | last post by:
The DB2 8.2 SQL Reference says SET CURRENT LOCK TIMEOUT is not under transaction control. I assume that means that changing it via the SQL command affects all connections to the database in the...
14
by: Sharon | last post by:
Hi all. I have an ArrayList and sometimes while enumerating through, i get an exception because another thread has added to the ArrayList. To solve this problem, i lock the enumeration, passing...
8
by: Komandur Kannan | last post by:
We have a smart device application running on handhelds(Symbol MC9000G). The backend is Oracle and a middle tier web services development done in Vb.net. We use pessimistic Locking due to...
2
by: Kevin Frey | last post by:
Hello, I've been reading that ASP.NET serialises (ie. processes one at a time) HTTP requests if two simultaneous requests need to access the same session state. It also makes note that ASP.NET...
1
by: pike | last post by:
DB2 8.1 FP7a on AIX 5.2. $ db2 SET CURRENT LOCK TIMEOUT NOT WAIT DB20000I The SQL command completed successfully. $ db2 values current lock timeout 1 ----------- 0
0
by: cwho.work | last post by:
Hi! We are using apache ibatis with our MySQL 5.0 database (using innodb tables), in our web application running on Tomcat 5. Recently we started getting a number of errors relating to...
4
by: jankhana | last post by:
Hi all, I'm having an application in that i use Sql Compact 3.5 with VS2008. I'm running multiple threads in my application which contacts the compact database and accesses the row. It...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.