Informix Session Information

Hello, We have the following situation. Our 4GL developers have a need to identify and save in a variable the current session isolation level. If the isolation level is not "dirty read" they would set it to dirty read. Then they would execute some lines of code. As a final step they would set the isolation level back to it's original state. Here is the code they were using to do this (yes we know it could be one SQL statement, though that is what the developer used): select odb_sessionid from sysmaster:sysopendb where odb_sessionid = dbinfo('sessionid') and odb_dbname in('bnh','bhi') and odb_iscurrent = 'Y' select te.flags, te.txt from sysmaster:sysopendb op, sysmaster:flags_text te, sysmaster:syssessions se where op.odb_isolation = te.flags and te.tabname = 'sysopendb' and op.odb_dbname in('bnh','bhi') and se.sid = ? Here is the issue. When they put this code into production we were seeing a lot of session - 'S' flag mutexes in onstat -u and it was slowing the system down. This piece of code is used very often by hundreds of users. The thing is that because the function where they put this SQL is called from dozens of other places the isolation level could be set to anything. We opened a case with Informix support and the bottom line they came back with was, "I have discussed this with a person who works regularly with the source code. He feels that this is what is going on; When that query is being run against the syssessions table, it is placing a lock (AKA a Mutex) on the underlying memory structures that the pseudo-table it was accessing. Being that it is accessing the session information, it is putting a mutex on the sessions themselves. Since you indicated that this code was running often, the session mutex was being put in place to keep them from changing while this read was occuring. This would hold up the actual user of those session for the moment this was done. If this were run often, it could easily bring the system to its knees." So the question is, how can our 4GL developers get the current isolation level for the current session without causing all those mutexes and locking the table?? Any help would be greatly appreciated. David Informix DBA in4mixdba@gmail.com

Dave wrote: > Hello, We have the following situation. Our 4GL developers have a need > to identify and save in a variable the current session isolation > level. If the isolation level is not "dirty read" they would set it > to dirty read. Then they would execute some lines of code. As a final > step they would set the isolation level back to it's original state. > Here is the code they were using to do this (yes we know it could be > one SQL statement, though that is what the developer used): > > select odb_sessionid > from sysmaster:sysopendb > where odb_sessionid = dbinfo('sessionid') > and odb_dbname in('bnh','bhi') > and odb_iscurrent = 'Y'> > select te.flags, > te.txt > from sysmaster:sysopendb op, > sysmaster:flags_text te, > sysmaster:syssessions se > where op.odb_isolation = te.flags > and te.tabname = 'sysopendb' > and op.odb_dbname in('bnh','bhi') > and se.sid = ?> > Here is the issue. When they put this code into production we were > seeing a lot of session - 'S' flag mutexes in onstat -u and it was > slowing the system down. This piece of code is used very often by > hundreds of users. The thing is that because the function where they > put this SQL is called from dozens of other places the isolation level > could be set to anything. > > We opened a case with Informix support and the bottom line they came > back with was, > > "I have discussed this with a person who works regularly with the > source code. He feels that this is what is going on; > > When that query is being run against the syssessions table, it is > placing a lock (AKA a Mutex) on the underlying memory structures that > the pseudo-table it was accessing. Being that it is accessing the > session information, it is putting a mutex on the sessions themselves. > Since you indicated that this code was running often, the session > mutex was being put in place to keep them from changing while this > read was occuring. This would hold up the actual user of those session > for the moment this was done. If this were run often, it could easily > bring the system to its knees." > > So the question is, how can our 4GL developers get the current > isolation level for the current session without causing all those > mutexes and locking the table?? > > Any help would be greatly appreciated. > > David > Informix DBA > in4mixdba@gmail.com Quite simply the 4gl developer should keep track of the isolation level within the application. Create a function that is used to set the isolation level, and have it set a global or modular variable every time the isolation level is changed. use that function throughout the code. The rest he can work out on his own. And if he gives you the "we have 4 million lines of code" speach, tell him to learn awk to script the required code changes. -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm