openerp-india team mailing list archive

["Olivier Dony \(OpenERP\)" <713216@xxxxxxxxxxxxxxxxxx>]

Hi Kyle,

Thanks for sending the output of the lock analysis query. For some reason the "relname" column is empty, so we don't know the name of the tables that are locked, but that does not matter very much.
The rest of the analysis is pretty clear, the login() call tries to update the last login "date" of the user trying to login, while the scheduler transaction is apparently holding a lock to that same user row.

This is most likely because the tables on with the scheduler transaction is working (stock.move, procurement_order, etc.) have foreign key references to their creator/updater user in the `create_uid` `write_uid` columns.
For any UPDATE/INSERT/DELETE PostgreSQL not only locks the row being modified, but also acquires a RowShareLock on any referenced rows (FKs), to avoid the foreign keys from changing or disappearing before the referring transaction completes.
Therefore while the scheduler is running it is likely to be holding a RowShareLock on the res_users rows corresponding to the create_uid and write_uid values of all the records it is working on. The most likely user to be locked out is the Admin user (UID 1) because the scheduler is running as admin by default.

Postgres does not keep row-level locks in memory so that won't appear
directly in the lock analysis, but you can see this happening because
the scheduler transaction [1] has been granted a number of RowShareLocks
(on my system I can see one of them is for res_users), and the login
transaction is waiting for a ShareLock on the "transaction_id" of the
other transaction. This is how a transaction waiting for another to
release a row-level lock will appear [2].

 transid | virtrid|  relname |   locktype    |       mode    | grtd | cur_query
---------+--------+----------+---------------+---------------+------+----------
         | 7/10   | res_users| relation      | RowShareLock  | t    | <IDLE>...
 1077956 | 7/10   |          | transactionid | ExclusiveLock | t    | <IDLE>...
 1077957 | 8/38   |          | transactionid | ExclusiveLock | t    | update re
 1077956 | 8/38   |          | transactionid | ShareLock     | f    | update re


You could confirm this in several ways:
- logging in as a new user who has not created or updated any document should work normally even when scheduler is running
- creating a new user 'MRP Scheduler' with full access to all the relevant tables and configuring the scheduler job to run as that user may greatly reduce the chance of blocking other users (but because there are many existing rows this may not take effect for a while)
- disabling the code that updates the "last login" date in res_users or making it tolerant to locked rows should eliminate that issue altogether; 

The patch applied in revision 4049[3] should solve the issue by doing
the latter option: skipping the update of the login date if the user's
record is currently locked. The fix is for trunk/6.1 but you could use
the same code to replace the bulk of the login() method in 6.0.

There aren't many other situations where we alter the user record, but
those may still be prevented after the patch, so the use of a separate
"owner user" for long-running background jobs may still be a good idea.

Thanks for reporting and for your patience during the long analysis!


[1] Shown as <IDLE> in transation, because it is busy in Python code
[2] Because row-level locks are not kept in memory, see also full explanation at http://www.postgresql.org/docs/8.4/interactive/explicit-locking.html#LOCKING-ROWS
[3] trunk server r.4049 rev-id: odo@xxxxxxxxxxx-20120213183207-45ptopqm0szritgx
http://bazaar.launchpad.net/~openerp/openobject-server/trunk/revision/4049

** Changed in: openobject-server
   Importance: Undecided => Medium

** Changed in: openobject-server
       Status: Incomplete => Fix Released

** Changed in: openobject-server
    Milestone: None => 6.1

-- 
You received this bug notification because you are a member of OpenERP
Indian Team, which is subscribed to OpenERP Server.
https://bugs.launchpad.net/bugs/713216

Title:
  Cannot login while MRP scheduler is running

Status in OpenERP Server:
  Fix Released

Bug description:
  When using the Manufacturing scheduler in a medium sized environment
  the scheduler will lock down the server in a way where not a single
  user may connect to the server. The login window shows up but when you
  type your password and click login it goes to a white screen and
  nothing happens, because the manufacturing scheduler is preventing the
  server from letting anyone log in. If you were already logged in then
  you can use the program. Quite frustrating. Sure the manufacturing
  scheduler is supposed to run at night, but that is still a bug.

To manage notifications about this bug go to:
https://bugs.launchpad.net/openobject-server/+bug/713216/+subscriptions