Summary:	ASTERISK-20326: res_odbc blocks datastore while a transaction waits for a lock
Reporter:	Christoph Sitter (sitterch)	Labels:
Date Opened:	2012-08-27 09:25:27	Date Closed:	2012-08-27 11:58:26
Priority:	Major	Regression?	No
Status:	Closed/Complete	Components:	Resources/res_odbc
Versions:	10.7.0	Frequency of Occurrence	Constant
Related Issues:
Environment:	RHEL 2.6.32-220.el6.x86_64 GNU/Linux	Attachments:
Description:	I have set up res_odbc to use a pool of 10 connections per resource (oracle 11g connections) and I also wrote a C-Module which uses res_odbc for Oracle connection management. What I'm doing:  - Start a transaction  - select for update (read + lock)  - update  - commit/rollback When I now lock the row that is affected by the transaction from within another session, the "select for update" has to wait until the other session has committed it's work. This is an expected behaviour and res_odbc does not produce any errors here. The problem is to get a further connection. In my case, I want to read a different row from the same table, but ast_odbc_request_obj hangs until the work is committed and the transactional "select for update" stops waiting. I tried to find out where exactly the problem is, and I think that it is the following command: obj = ao2_callback(class-obj_container, 0, aoro2_obj_cb, EOR_TX); at the beginning of ast_odbc_request_obj_2 Hopefully this problem can be fixed soon ;) best regards Christoph
Comments:	By: Matt Jordan (mjordan) 2012-08-27 11:58:10.070-0500 This is not a bug.  How does res_odbc know that your next operation is going to work on a different row, and/or is safe to proceed before the previous transaction is finished?  Its possible that the logic could be put into res_odbc to make these determinations, but that would be an improvement, and thus a feature request. By: Matt Jordan (mjordan) 2012-08-27 11:58:20.596-0500 Features requests are no longer submitted to or accepted through the issue tracker. Features requests are openly discussed on the mailing lists [1] and Asterisk IRC channels and made note of by Bug Marshals. [1] http://www.asterisk.org/support/mailing-lists By: Christoph Sitter (sitterch) 2012-08-29 01:55:40.906-0500 Hello Matt, I'm sorry, but I don't agree with what you said. If I'm using a pool of n connections, and one connection is blocked (waits for a row-level lock on the database to be released), then it must be possible, to request a new connection and execute queries onto different rows. IF the second connection will operate on the same row, it will have to wait for the database to release the lock. It makes no sense to stop offering connections out of the pool. Also your argument just works for one Server, as soon as you're using two servers, the concurrency problem occurs again.