Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Welcome To Ask or Share your Answers For Others

Categories

0 votes
471 views
in Technique[技术] by (71.8m points)

c# - Oracle.DataAccess.Client.OracleException ORA-03135: connection lost contact

I have a .Net service that connects to an Oracle database on every request. It works fine at the beginning, but after some number of requests I start getting:

Oracle.DataAccess.Client.OracleException ORA-03135: connection lost contact
   at Oracle.DataAccess.Client.OracleException.HandleErrorHelper(Int32 errCode, OracleConnection conn, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src, String procedure)
   at Oracle.DataAccess.Client.OracleException.HandleError(Int32 errCode, OracleConnection conn, String procedure, IntPtr opsErrCtx, OpoSqlValCtx* pOpoSqlValCtx, Object src)
   at Oracle.DataAccess.Client.OracleCommand.ExecuteReader(Boolean requery, Boolean fillRequest, CommandBehavior behavior)
   at Oracle.DataAccess.Client.OracleCommand.ExecuteReader()
   at MyApp.Services.OracleConnectionWithRetry.ExecuteReader(OracleCommand command)
   ...

Any idea what might be the problem? I dispose all the connections, results and parameters. The load on this service is, well, very low.

See Question&Answers more detail:os

与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
Welcome To Ask or Share your Answers For Others

1 Reply

0 votes
by (71.8m points)

It happens because your code requests a connection from the Oracle Connection Pool and the connection pool returns a disconnected / stale connection to the Oracle DB. ODP.NET does not itself test the connection status of the connection sent to client.

So to be safe, either you check the connection status == Open for the connection received from the pool when you do a Connection.Open()

OR

let ODP.NET do the checking for you by setting Validate Connection = true in your connection string in web.config.

Both this methods have a impact on performance as they test the connection status each time you need to connect to the database.

A third option which I use is use of exceptions. First be optimistic and use whateven connection is returned from the connection pool. If you get a ORA - 3135 then request a new connection and execute your query again like a while loop. In best case, you can get your 1st connection as valid and your query will execute. In worst case, all the connections in your pool are stale in which case the code will be executed N time (where N is the connection pool size).


与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…
OGeek|极客中国-欢迎来到极客的世界,一个免费开放的程序员编程交流平台!开放,进步,分享!让技术改变生活,让极客改变未来! Welcome to OGeek Q&A Community for programmer and developer-Open, Learning and Share
Click Here to Ask a Question

...