Juan, Eric: According with coments in code, the overall transaction strategy has changed from a default ISOLATION_LEVEL_REPEATABLE_READ as default plus special code for some critical situations (login, stock reservation, cron updates: SELECT ... FOR UPDATE in order to early lock the tables) to a new strategy (PostgreSQL >= 9.1) where ISOLATION_LEVEL_READ_COMMITTED is used as default (leading to a higher exception rate), compensated by a standard retry policy on every transaction with 3 attempts randomly delayed.
Conceptually, I think the new strategy is essentially correct. Nevertheless, there are some code that was not adjusted to take advantage of the new strategy. For example, in the case of cron the standard process reading task to be executed from the list of waiting tasks was rewritten and several problems were solved with the new strategy (even sometimes some concurrency conflicts are still visible on the log) For the case of the procurement scheduler most of the problem is that the whole loop exploring products with low stocks is run as single long transaction with the standard setup. There is a parameter on cron task activation (line 35, addons/procurement/schedulers.py) called new_cursor that can be pased on the cron record to activate a per product transaction that reduces dramatically contention on schedulers. Unfortunatly, the code is not well designed and you must be carefull of using the name of the database to setup the use_new_cursor flag, instead a simple True or False In the case of stock, the logic does not reflect the new strategy and still asumes the old way of doing things. If you check in addons/stock/stock.py line 426 in V7, in case of errors it simply waits for a retry from the user side (in order to activate the new retry logic, the postgresql exception should be thrown all the way out). I guess it was simply overlook on strategy change. From my point of view, using the retry strategy, it makes little sense to still use the SELECT for UPDATE, and probably just retrying is the best tradeoff to get the most transactions completed at the end of the day On the login case, contention is just a matter of updating last login date, and in case of concurrent operations, simply a log message is recorded without aborting the transaction, what means that there is little damage. >From Eric's original questions, I believe the change in concurrent transaction strategy is a step in the right direction, and we should fix any code that stills asume the old behaviour. Commiting often is allways something good to achieve (unless the slower transaction commits set a limit on this). This should be the standard behaviour on procurement I believe the only reason those remaining SELECT FOR UPDATEs are just code not reanalyzed under the new transaction strategy. See the history of cron modifications (I could not find the exact bug #) where you can see that after many iterations, the last version is a lot simpler than the original implementation and more important, free of subtle bugs from unconsidered situations I suggest to modify whatever is needed to reduce as much as possible any user requested retry, trying to deal with concurrency problems from within the server code. My 2 cents Gustavo Adrian Marino 2013/5/16 Juan Cristobal Lopez Arrieta <[email protected]> > We deal with incoming interfaces from 2 other systems (java and php) and a > realtime envirnment, and some days is hard to analize a log or have some > decent monioring and the likes... under some circunstances the > concurrency/serialization problem produces roolback of complex transactions > that involve outgoing interfaces with other systems.... it can be easly a > nightmare...for nothing :-) > > > On Wed, May 15, 2013 at 10:40 PM, Eric Caudal > <[email protected]>wrote: > >> Let's say that before going to some extreme solutions (we are thinking >> of strongly modifying as well the 3 mentioned files), I would rather find a >> standard one. >> >> We are not dealing with so many transactions (around 3000 to 5000 Sales >> order lines per day from the website) that I am surprised this has not been >> issued/solved/discarded before. >> >> >> >> Eric Caudal*CEO* >> --*Elico Corporation, Shanghai branchOpenERP Premium Certified Training >> Partner * >> Cell: + 86 186 2136 1670 >> Office: + 86 21 6211 8017/27/37 >> Skype: [email protected]http://www.elico-corp.com >> >> [image: Elico Corp] >> On 05/16/2013 11:06 AM, Juan Cristobal Lopez Arrieta wrote: >> >> The concurrent updates are giving us so much trouble, that i have been >> thinking to just comment the update for the login in res.user !! >> or replace it with an insert into another table (ie: res_user_login) >> .... >> Sounds too much? >> >> >> On Wed, May 15, 2013 at 10:00 PM, Eric Caudal <[email protected] >> > wrote: >> >>> We are in 7.0: we are checking whether this behavior in 6.1 is already >>> available in 7.0 but so far not conclusive. >>> By the way, we are using the latest python-psycopg. >>> >>> >>> Eric Caudal*CEO* >>> --*Elico Corporation, Shanghai branchOpenERP Premium Certified Training >>> Partner * >>> Cell: + 86 186 2136 1670 >>> Office: + 86 21 6211 8017/27/37 >>> Skype: [email protected]http://www.elico-corp.com >>> >>> [image: Elico Corp] >>> On 05/15/2013 06:01 PM, Leonardo Pistone wrote: >>> >>> Dear Eric, >>> >>> I didn't have time to investigate, so that might not be relevant, but >>> I've seen this commit on 6.1 recently: >>> >>> >>> http://bazaar.launchpad.net/~openerp/openobject-server/6.1/revision/4337 >>> >>> Leonardo >>> >>> >>> On Wed, May 15, 2013 at 11:55 AM, Eric Caudal < >>> [email protected]> wrote: >>> >>>> Hi, >>>> We have a case where our procurement scheduler (with >>>> procurement_confirm and action_assign) is blocking other transactions (such >>>> as website synchronization) or vice versa with an error such as "Serialized >>>> Access due to concurrent update". >>>> So far we have made some researches and we discover that this is maybe >>>> due to an error " Repeatable Read Isolation Level", documented here: >>>> http://www.postgresql.org/docs/9.2/static/transaction-iso.html >>>> >>>> A simple case would be like this: >>>> >>>> Session1: BEGIN; >>>> Session2: BEGIN; >>>> Session1: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; >>>> Session2: SET TRANSACTION ISOLATION LEVEL SERIALIZABLE; >>>> Session1: SELECT * FROM tunnels WHERE un = 1 FOR UPDATE; >>>> un | password >>>> ----+---------- >>>> 1 | bugsy >>>> (1 row) >>>> >>>> Session1: UPDATE tunnels SET password = 'malone' WHERE un = 1; >>>> Session2: SELECT * FROM tunnels WHERE un = 1 FOR UPDATE; >>>> ...waits on Session1's lock... >>>> Session1: COMMIT; >>>> Session2: ERROR: could not serialise access due to concurrent update >>>> >>>> In current OpenERP code, we have check and there are 3 main places >>>> where this kind of transactions are used: >>>> base/res_users.py for function login, line 334, >>>> stock/stock.py, for function _product_reserve line 442 >>>> stock_prodlot_wizard/prodlot_wizard, class >>>> stock_move_split_lines_exist, line 279 >>>> >>>> My questions: >>>> 1. Is it something mandatory in standard code? What if we by >>>> pass/modify them >>>> 2. Would it be an option to commit more often? >>>> 3. Is there any other reason why we could have this concurrent update? >>>> >>>> Suggestions are more than welcome! >>>> >>>> -- >>>> >>>> Eric Caudal*CEO* >>>> --*Elico Corporation, Shanghai branchOpenERP Premium Certified Training >>>> Partner * >>>> Cell: + 86 186 2136 1670 >>>> Office: + 86 21 6211 8017/27/37 >>>> Skype: [email protected]http://www.elico-corp.com >>>> >>>> [image: Elico Corp] >>>> >>>> _______________________________________________ >>>> Mailing list: https://launchpad.net/~openerp-community >>>> Post to : [email protected] >>>> Unsubscribe : https://launchpad.net/~openerp-community >>>> More help : https://help.launchpad.net/ListHelp >>>> >>>> >>> >>> >>> _______________________________________________ >>> Mailing list: https://launchpad.net/~openerp-community >>> Post to : [email protected] >>> Unsubscribe : https://launchpad.net/~openerp-community >>> More help : https://help.launchpad.net/ListHelp >>> >>> >> >> >> -- >> Juan Cristobal Lopez Arrieta >> http://www.openerp.com/node/560 >> skype : jclopezar >> >> >> > > > -- > Juan Cristobal Lopez Arrieta > http://www.openerp.com/node/560 > skype : jclopezar > > > _______________________________________________ > Mailing list: https://launchpad.net/~openerp-community > Post to : [email protected] > Unsubscribe : https://launchpad.net/~openerp-community > More help : https://help.launchpad.net/ListHelp > > -- Gustavo Adrian Marino Mobile: +54 911 5498 2515 Email: [email protected] Skype: gustavo.adrian.marino
_______________________________________________ Mailing list: https://launchpad.net/~openerp-community Post to : [email protected] Unsubscribe : https://launchpad.net/~openerp-community More help : https://help.launchpad.net/ListHelp
