cx-oracle-users

[cx-oracle-users] Possible to make SessionPool.acquire() block until connection available?

[Jim F. <jim...@sb...>]

I have an app which has more worker threads than my session pool max setting, because these threads spend most of their time doing wotk other than DB calls.
 
My problem is when acquire() is called and there are no available connections, a cx_Oracle.DatabaseError is immediately thrown.  Ideally I'd like acquire() to block up to some timeout period, and if multiple threads are blocked on acquire(), they received connections in the same order they blocked.
 
Maybe the way the Oracle DRCP interface works (which I know little about) prevents this?

Re: [cx-oracle-users] Possible to make SessionPool.acquire() block until connection available?

[Vladimir R. <gre...@gm...>]

Hello, Jim,

Did you consider using language synchronization primitives, like
threading.Semaphore or multiprocessing.Semaphore?


2014-08-01 0:46 GMT+04:00 Jim Freeman <jim...@sb...>:

> I have an app which has more worker threads than my session pool max
> setting, because these threads spend most of their time doing wotk other
> than DB calls.
>
> My problem is when acquire() is called and there are no available
> connections, a cx_Oracle.DatabaseError is immediately thrown.  Ideally I'd
> like acquire() to block up to some timeout period, and if multiple threads
> are blocked on acquire(), they received connections in the same order they
> blocked.
>
> Maybe the way the Oracle DRCP interface works (which I know little about)
> prevents this?
>
>
> ------------------------------------------------------------------------------
> Infragistics Professional
> Build stunning WinForms apps today!
> Reboot your WinForms applications with our WinForms controls.
> Build a bridge from your legacy apps to the future.
>
> http://pubads.g.doubleclick.net/gampad/clk?id=153845071&iu=/4140/ostg.clktrk
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

Re: [cx-oracle-users] Possible to make SessionPool.acquire() block until connection available?

[Krishna M. IV <kri...@or...>]

You need to use cx_Oracle. 
<http://cx-oracle.readthedocs.org/en/latest/module.html#constants>SPOOL_ATTRVAL_WAIT 
<http://cx-oracle.readthedocs.org/en/latest/module.html#constants>
in the call
cx_Oracle.SessionPool(/user/, /password/, /database/, /min/, /max/, 
/increment/[, /connectiontype/, /threaded/, 
/getmode=cx_Oracle.SPOOL_ATTRVAL_NOWAIT/, /homogeneous=True/])

The default seems to be NOWAIT.

thanks,
krishna

On 8/1/2014 10:55 AM, Vladimir Ryabtsev wrote:
> Hello, Jim,
>
> Did you consider using language synchronization primitives, like 
> threading.Semaphore or multiprocessing.Semaphore?
>
>
> 2014-08-01 0:46 GMT+04:00 Jim Freeman <jim...@sb... 
> <mailto:jim...@sb...>>:
>
>     I have an app which has more worker threads than my session pool
>     max setting, because these threads spend most of their time doing
>     wotk other than DB calls.
>     My problem is when acquire() is called and there are no available
>     connections, a cx_Oracle.DatabaseError is immediately thrown.
>     Ideally I'd like acquire() to block up to some timeout period, and
>     if multiple threads are blocked on acquire(), they received
>     connections in the same order they blocked.
>     Maybe the way the Oracle DRCP interface works (which I know little
>     about) prevents this?
>
>     ------------------------------------------------------------------------------
>     Infragistics Professional
>     Build stunning WinForms apps today!
>     Reboot your WinForms applications with our WinForms controls.
>     Build a bridge from your legacy apps to the future.
>     http://pubads.g.doubleclick.net/gampad/clk?id=153845071&iu=/4140/ostg.clktrk
>     _______________________________________________
>     cx-oracle-users mailing list
>     cx-...@li...
>     <mailto:cx-...@li...>
>     https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
>
>
> ------------------------------------------------------------------------------
> Want fast and easy access to all the code in your enterprise? Index and
> search up to 200,000 lines of code with a free copy of Black Duck
> Code Sight - the same software that powers the world's largest code
> search on Ohloh, the Black Duck Open Hub! Try it now.
> http://p.sf.net/sfu/bds
>
>
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

Re: [cx-oracle-users] Possible to make SessionPool.acquire() block until connection available?

[Jim F. <jim...@sb...>]

Thanks Vladimir and Krishna!
Using cx_Oracle.SPOOL_ATTRVAL_WAIT causes the behavior I need --  acquire() now blocks until a connection is free.
 
Still one minor issue: say I have 4 threads and a 2 connection pool.  In my test app the threads do a block of acquire/sleep/release in a tight loop of N iterations.  Threads 3 and 4 get completely starved until Threads 1 and 2 have finished all N iterations.  If I put a 1 second sleep after the release, then the connections are fair-shared between the 4 requesters.  
 
Same behavior if I use SPOOL_ATTRVAL_WAIT and put a sempahore(2) around the acquire/sleep/release block.  That's because there's no fair semaphore (i.e. FIFO) in Python.
 
In my real app, there is lots of other code to execute between connection aquires, so hopefully thread starvation in acquire() won't be a real issue for me.
From: Krishna Mohan IV <kri...@or...>
To: cx-...@li... 
Sent: Thursday, July 31, 2014 10:40 PM
Subject: Re: [cx-oracle-users] Possible to make SessionPool.acquire() block until connection available?

You need to use cx_Oracle.SPOOL_ATTRVAL_WAIT
in the call 
cx_Oracle.SessionPool(user, password, database, min, max, increment[, connectiontype, threaded, getmode=cx_Oracle.SPOOL_ATTRVAL_NOWAIT, homogeneous=True])

The default seems to be NOWAIT.

thanks,
krishna

On 8/1/2014 10:55 AM, Vladimir Ryabtsev wrote:

Hello, Jim, 
>
>Did you consider using language synchronization primitives, like threading.Semaphore or multiprocessing.Semaphore?
>
>
>2014-08-01 0:46 GMT+04:00 Jim Freeman <jim...@sb...>:
>
>I have an app which has more worker threads than my session pool max setting, because these threads spend most of their time doing wotk other than DB calls.
>>
>>My problem is when acquire() is called and there are no available connections, a cx_Oracle.DatabaseError is immediately thrown.  Ideally I'd like acquire() to block up to some timeout period, and if multiple threads are blocked on acquire(), they received connections in the same order they blocked.
>>
>>Maybe the way the Oracle DRCP interface works (which I know little about) prevents this?
>>------------------------------------------------------------------------------
>>Infragistics Professional
>>Build stunning WinForms apps today!
>>Reboot your WinForms applications with our WinForms controls.
>>Build a bridge from your legacy apps to the future.
>>http://pubads.g.doubleclick.net/gampad/clk?id=153845071&iu=/4140/ostg.clktrk
>>_______________________________________________
>>cx-oracle-users mailing list
>>cx-...@li...
>>https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>>
>
> 
>
>------------------------------------------------------------------------------
Want fast and easy access to all the code in your enterprise? Index and
search up to 200,000 lines of code with a free copy of Black Duck
Code Sight - the same software that powers the world's largest code
search on Ohloh, the Black Duck Open Hub! Try it now. http://p.sf.net/sfu/bds
> 
>
>_______________________________________________
cx-oracle-users mailing list cx-...@li... https://lists.sourceforge.net/lists/listinfo/cx-oracle-users 


------------------------------------------------------------------------------
Want fast and easy access to all the code in your enterprise? Index and
search up to 200,000 lines of code with a free copy of Black Duck
Code Sight - the same software that powers the world's largest code
search on Ohloh, the Black Duck Open Hub! Try it now.
http://p.sf.net/sfu/bds

_______________________________________________
cx-oracle-users mailing list
cx-...@li...
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

Re: [cx-oracle-users] Possible to make SessionPool.acquire() block until connection available?

[Jim F. <jim...@sb...>]

Oops, meant to say "Same behavior if I use SPOOL_ATTRVAL_NOWAIT..." in 3rd paragraph below.

From: Jim Freeman <jim...@sb...>
To: "cx-...@li..." <cx-...@li...> 
Sent: Friday, August 1, 2014 3:58 PM
Subject: Re: [cx-oracle-users] Possible to make SessionPool.acquire() block until connection available?



Thanks Vladimir and Krishna!
Using cx_Oracle.SPOOL_ATTRVAL_WAIT causes the behavior I need --  acquire() now blocks until a connection is free.
 
Still one minor issue: say I have 4 threads and a 2 connection pool.  In my test app the threads do a block of acquire/sleep/release in a tight loop of N iterations.  Threads 3 and 4 get completely starved until Threads 1 and 2 have finished all N iterations.  If I put a 1 second sleep after the release, then the connections are fair-shared between the 4 requesters.  
 
Same behavior if I use SPOOL_ATTRVAL_WAIT and put a sempahore(2) around the acquire/sleep/release block.  That's because there's no fair semaphore (i.e. FIFO) in Python.
 
In my real app, there is lots of other code to execute between connection aquires, so hopefully thread starvation in acquire() won't be a real issue for me.
From: Krishna Mohan IV <kri...@or...>
To: cx-...@li... 
Sent: Thursday, July 31, 2014 10:40 PM
Subject: Re: [cx-oracle-users] Possible to make SessionPool.acquire() block until connection available?

You need to use cx_Oracle.SPOOL_ATTRVAL_WAIT
in the call 
cx_Oracle.SessionPool(user, password, database, min, max, increment[, connectiontype, threaded, getmode=cx_Oracle.SPOOL_ATTRVAL_NOWAIT, homogeneous=True])

The default seems to be NOWAIT.

thanks,
krishna

On 8/1/2014 10:55 AM, Vladimir Ryabtsev wrote:

Hello, Jim, 
>
>Did you consider using language synchronization primitives, like threading.Semaphore or multiprocessing.Semaphore?
>
>
>2014-08-01 0:46 GMT+04:00 Jim Freeman <jim...@sb...>:
>
>I have an app which has more worker threads than my session pool max setting, because these threads spend most of their time doing wotk other than DB calls.
>>
>>My problem is when acquire() is called and there are no available connections, a cx_Oracle.DatabaseError is immediately thrown.  Ideally I'd like acquire() to block up to some timeout period, and if multiple threads are blocked on acquire(), they received connections in the same order they blocked.
>>
>>Maybe the way the Oracle DRCP interface works (which I know little about) prevents this?
>>------------------------------------------------------------------------------
>>Infragistics Professional
>>Build stunning WinForms apps today!
>>Reboot your WinForms applications with our WinForms controls.
>>Build a bridge from your legacy apps to the future.
>>http://pubads.g.doubleclick.net/gampad/clk?id=153845071&iu=/4140/ostg.clktrk
>>_______________________________________________
>>cx-oracle-users mailing list
>>cx-...@li...
>>https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>>
>
> 
>
>------------------------------------------------------------------------------
Want fast and easy access to all the code in your enterprise? Index and
search up to 200,000 lines of code with a free copy of Black Duck
Code Sight - the same software that powers the world's largest code
search on Ohloh, the Black Duck Open Hub! Try it now. http://p.sf.net/sfu/bds
> 
>
>_______________________________________________
cx-oracle-users mailing list cx-...@li... https://lists.sourceforge.net/lists/listinfo/cx-oracle-users 


------------------------------------------------------------------------------
Want fast and easy access to all the code in your enterprise? Index and
search up to 200,000 lines of code with a free copy of Black Duck
Code Sight - the same software that powers the world's largest code
search on Ohloh, the Black Duck Open Hub! Try it now.
http://p.sf.net/sfu/bds

_______________________________________________
cx-oracle-users mailing list
cx-...@li...
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users




------------------------------------------------------------------------------
Want fast and easy access to all the code in your enterprise? Index and
search up to 200,000 lines of code with a free copy of Black Duck
Code Sight - the same software that powers the world's largest code
search on Ohloh, the Black Duck Open Hub! Try it now.
http://p.sf.net/sfu/bds

_______________________________________________
cx-oracle-users mailing list
cx-...@li...
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users