Re: More on .NET Timeout Problems with SDK 2.81

<snip> > > > > We think there's a bug in the IBM.Data.Informix classes. The database > > is IDS 7.2. > > > > I'm not sure what you are saying. Are you getting a timeout message > from the Windows Service, the provider, or something else? When you > say these are not 'real' timeouts, do you mean that the timeouts are > not working or that what is reported as a timeout error is really > something else? If you run the same query through a .Net app outside > of a service environment what happens? > We do only get these problems running under a Windows service, when running the same code from a Windows form we have not been able to reproduce the problem. The answer to why I don't think they are real timeouts is that there is no reason for these timeouts to occur, what is represented as a timeout error is not caused by the resource being unavailable for any reason that we can determine. The server is up, the database is available, it is not doing anything else, the table is not locked. The sql being executed is only selecting, there's no update cursor or anything like that. The select is one that reads a reasonable number of rows but only returns a small number of rows. It takes about three seconds to run from isql during the day (when the machine is busy). I should also say that the error occurs about one time in three. Here is the actual error - 2004-10-29 05:14 IBM.Data.Informix.IfxException: ERROR [HYT00] [Informix .NET provider]Timeout expired. ERROR [HY008] [Informix .NET provider][Informix]Statement interrupted by user. at IBM.Data.Informix.IfxConnection.HandleError(IntPtr hHandle, SQL_HANDLE hType, RETCODE retcode) at IBM.Data.Informix.IfxDataReader.Read() at CDb.ActivityDB.GetInvalidActivities(IDbConnection conn, IDbTransaction trans) at CDb.GenerateActivityFacade.CheckActivitiesStillValid(String strLogFileName) at TBCService.ServiceStartStop.RunOvernightJob() in C:\\VSProjects\\xxxxxx\\TBC\\TBCService\\ServiceStartStop.vb:line 186 at TBCService.ServiceStartStop.Timer1_Tick(Object sender, ElapsedEventArgs e) in C:\\VSProjects\\xxxxxx\\TBC\\TBCService\\ServiceStartStop.vb:line 146 Sequence: 0 Message: [Informix .NET provider]Timeout expired. Informix error number: -11094 SQL: HYT00 Sequence: 1 Message: [Informix .NET provider][Informix]Statement interrupted by user. Informix error number: -213 SQL: HY008 > <snip> > Couple of things: a) the ConnectionTimeout setting refers to the time > allowed to the provider to establish a connection to the server, _not_ > the time allowed a query to run. They are different settings. b) > Having said that, I do think there are real problems in the provider's > timeout settings for both connection and command. > I've done a little more research here. We established that if we were waiting for a locked record it waited about 30 seconds before raising the Timeout error (-11094 to be precise) if we have lock wait set ON. If the server is unavailable - for example it's in quiescent mode, or not running at all then we get an error within 5 seconds, and it isn't a timeout error (for example if it's in quiescent mode we get an error that says it cannot connect to a server in quiescent mode). If it's unavailable for any other reason that I have tried so far it raises a suitable error - not a timeout error. So how could I emulate the situation you describe above, where the timeout is raised because the provider could not establish a connection to the server ? If I could set up that scenario I could test to see if the ConnectionTimeout property affected the timeout and it might give me a clue as to the original problem with the Windows Service. > > > > You cannot set the ConnectionTimeout property in code but you can set > > it in the connect string by appending "; Timeout=nn" to it (where nn > > is the timeout in seconds). This is the same parameter as is used by > > SQL Server. > > > > This will affect the value of the ConnectionTimeout property as we can > > display it. > > > > However it has NO EFFECT WHATSOEVER on the actual Timeout used by the > > connection ! > > > > > 170027 CONNECTIONTIMEOUT IS NOT PROPERLY SUPPORTED. IT SHOULD ALLOW A > CONNECTION TO WAIT UNTIL SOME OTHER THREAD HAS RELEASED A CONNECTION > FOR USE > > Should be fixed at the 2.90 release. > > Problem is there doesn't appear to be a clear distinction between > application induced reasons for waiting and lower-level induced > reasons for waiting. For instance, say an multi-threaded application > has bumped into the limit set by Max Pool Size; presumably you'd want > a setting for allowing the provider to wait until a similar connection > is closed before returning an error. The setting of MaxPoolSize and > the number of connections opened at one time is controlled entirely by > the application; so, the mechanism within the provider for handling > this is going to be different from the mechanism for how long to wait > for a response after sending a connect request over the network. > Probably a lot of application programmers don't care about the > distinction, but it exists nonetheless. I guess the provider could > first wait for a connection object to become available and then > subtract the time that it waited for that from the original timeout in > order to tell the network communication module how long it is willing > to wait. It's still a young product and I wouldn't be surprised if > not all this is hooked together yet. > There is the well-documented problem with SQL Server if you use an Application Role to connect. Used connections are returned to the pool closed, another thread comes along, gets the closed connection and tries to use it. Bang ! Ouch ! Needs careful thinking through by them as writes this stuff ! Thanks for your help anyway. Richard