RE: Question regarding global variable.

Jonathan, Thank you very much for your reply. I have tried to implement the code to use global variable. It works for different connections. But I run into one problem. Actually my previous example wasn't accurate enough. The function contains some insert/update for several tables. The key value input to the function is the unique id in one table. So the desired value for global variable is "SP in Progress for Entry ID 1". When App1 and App2 both execute the function SP1 for entry 1 simultaneously from different connections, I can see deadlock detected and one transaction was rolled back. Now my questions are: 1. How will the transaction be handled inside a function? I have been told that all statements in a function is considered in a single transaction. If a deadlock is detected, does that mean all statements in the function will be automatically rolled back completely? 2. How will the lock be handled inside a function? For example, there are insert/delete for two tables. When will the exclusive lock be issued, when the insert/delete statement is executed? When will the exclusive lock be released, at the end of the function? If there is documentation regarding this, could you please point me to the link? Thanks a lot in advance, Feng -----Original Message----- From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] On Behalf Of Jonathan Leffler Sent: Thursday, December 15, 2005 12:32 AM To: informix-list@iiug.org Subject: Re: Question regarding global variable. Feng Huang (huangcf) wrote: > Hi, > > I am new to Informix. I would like to get some help on how to use > global variable. > > I use IBM Informix Dynamic Server Version 10.00.UC3X9 on a Linux server. > I have a function. Inside the function, I would like set the value of > a global variable when entering and reset it back to null right before > exiting. Something like below: > > CREATE FUNCTION SP1(appName varchar(128)) RETURNS BOOLEAN AS retval;> > DEFINE retval BOOLEAN; > DEFINE GLOBAL gvar varchar(1024) DEFAULT ""; > > LET retval = 't'; > LET gvar = "SP in progress for " || appName; > > ... > > LET gvar = "": > > RETURN retval; > > END FUNCTION; > > The function could be called by multiple instances of application. I > wonder whether there is a way I can protect the global variable to > only be set by one instance at a time. For example, App1 calls SP1, > set gvar to be "SP in progress for App1". When App2 calls SP1, App2 > has to wait until App1 exit the function (gvar is set back to ""). I > don't want to throw exception immediately for App2 either. In terms of concurrency, what you've got is fine - the 'GLOBAL' variable is global to the session only, and not visible to any other session. In terms of your specification, the zero length non-null VARCHAR is different from a NULL - use LET gvar = NULL to meet your specification. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/ sending to informix-list

Feng Huang (huangcf) wrote: > I have tried to implement the code to use global variable. It works for > different connections. But I run into one problem. Actually my previous > example wasn't accurate enough. The function contains some insert/update > for several tables. The key value input to the function is the unique id > in one table. So the desired value for global variable is "SP in > Progress for Entry ID 1". When App1 and App2 both execute the function > SP1 for entry 1 simultaneously from different connections, I can see > deadlock detected and one transaction was rolled back. Inside a database, shared data is best manipulated via a table - especially where you need an array of global variables, so that you can have on value indicate "SP in progress for ID 1", another for "SP in progress for ID 2", and so on for an indefinite number of different IDs. Otherwise, you really don't care which ID is being updated; you just care about ensuring that the procedure is single-threaded; no two people can be using its main body at the same time. > Now my questions are: > > 1. How will the transaction be handled inside a function? I have been > told that all statements in a function is considered in a single > transaction. If a deadlock is detected, does that mean all statements in > the function will be automatically rolled back completely? IDS does not automatically rollback a transaction on error (nor does it place the transaction into a 'cannot proceed' state). If there is a transaction in progress when the function starts, it will still be in progress when the function ends. If the function starts a transaction and does not terminate it, the statement will not terminate until the next reboot. I don't think the body of a function is atomic - treated as a single transaction - though each SQL statement inside the function is treated as a single (sub-)transaction. If a deadlock is detected, the statement that triggered the deadlock will be rolled back to the savepoint set internally when the statement started. > 2. How will the lock be handled inside a function? For example, there > are insert/delete for two tables. When will the exclusive lock be > issued, when the insert/delete statement is executed? When will the > exclusive lock be released, at the end of the function? This depends on lots of thinsg - including MODE ANSI vs logged database, isolation level and whether you fetch with a cursor for update or not. IDS follows the 2PL - two-phase locking protocol. Locks are released at the end of transaction. > If there is documentation regarding this, could you please point me to > the link? > > Thanks a lot in advance, > Feng > > -----Original Message----- > From: owner-informix-list@iiug.org [mailto:owner-informix-list@iiug.org] > On Behalf Of Jonathan Leffler > Sent: Thursday, December 15, 2005 12:32 AM > To: informix-list@iiug.org > Subject: Re: Question regarding global variable. > > Feng Huang (huangcf) wrote: >>I am new to Informix. I would like to get some help on how to use >>global variable. >> >> I use IBM Informix Dynamic Server Version 10.00.UC3X9 on a Linux >> server. I have a function. Inside the function, I would like set >> the value of a global variable when entering and reset it back to >> null right before exiting. Something like below: >> >>CREATE FUNCTION SP1(appName varchar(128)) RETURNS BOOLEAN AS retval;>> DEFINE retval BOOLEAN; >> DEFINE GLOBAL gvar varchar(1024) DEFAULT ""; >> LET retval = 't'; >> LET gvar = "SP in progress for " || appName; >> ... >> LET gvar = "": >> RETURN retval; >>END FUNCTION; >> >> The function could be called by multiple instances of application. >> I wonder whether there is a way I can protect the global variable >> to only be set by one instance at a time. For example, App1 calls >> SP1, set gvar to be "SP in progress for App1". When App2 calls SP1, >> App2 has to wait until App1 exit the function (gvar is set back to >> ""). I don't want to throw exception immediately for App2 either. > > In terms of concurrency, what you've got is fine - the 'GLOBAL' > variable is global to the session only, and not visible to any other > session. > > In terms of your specification, the zero length non-null VARCHAR is > different from a NULL - use LET gvar = NULL to meet your > specification. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/