If you’re working with Odoo and PostgreSQL, the “could not obtain lock on row in relation ‘ir_cron'” error might have caught your attention. It’s a well-known issue, often emerging unexpectedly and although many different solutions to address it appear to exist, there is a fundamental factor which could trigger this error in any Odoo environment. Code reviews of the Odoo source code show that Odoo versions as early as version 8 can exhibit this behaviour. For those who have tried all known solutions unsuccessfully, this post will take you through the intricacies of this error, demonstrating its origin and how to address it from the Odoo code itself.
The Mysterious Error
When running Odoo, the PostgreSQL server logs may reflect these entries:
2025-01-03 10:22:32.637 CET [3545675] odoo@database LOG: statement:
SELECT id
FROM "ir_cron"
WHERE id IN (6)
FOR NO KEY UPDATE NOWAIT
2025-01-03 10:22:32.637 CET [3545675] odoo@database ERROR: could not obtain lock on row in relation "ir_cron"
2025-01-03 10:22:32.637 CET [3545675] odoo@database STATEMENT:
SELECT id
FROM "ir_cron"
WHERE id IN (6)
FOR NO KEY UPDATE NOWAIT
This error occurs when Odoo, while attempting to manage scheduling via ir_cron, fails to acquire the row lock. And, despite appearing critical, this error usually resolves itself as the same operation is retried and completed successfully immediately after the failure.
Investigating the Cause
This error originates from Odoo’s ‘fetch_mail' function, part of the model fetchmail.server, which handles receiving e-mail messages. When reviewing the source code the context defined (‘additionnal_context’) immediately stands out. This will be important later but for now, review the function code called for each incoming mail server below:
def fetch_mail(self):
""" WARNING: meant for cron usage only - will commit() after each email! """
additionnal_context = {
'fetchmail_cron_running': True
}
MailThread = self.env['mail.thread']
for server in self:
_logger.info('start checking for new emails on %s server %s', server.server_type, server.name)
additionnal_context['default_fetchmail_server_id'] = server.id
count, failed = 0, 0
# ... Email fetching logic ...
server.write({'date': fields.Datetime.now()})
return True
Filename: addons/mail/models/fetchmail.py
By debugging the code, it became apparent that execution of the ‘server.write’ statement aligned with the exact moment the error was logged in the PostgreSQL log file. But, the server object refers to a fetchmail.server type record and the PostgreSQL error refers to an ir.cron type record. Clearly we need to dig a little deeper to understand what causes this.
Note: In Odoo models are described using a period like ir.cron. However, in database table names the period is replaced with an underscore so the model ir.cron lives in database table ir_cron.
Code Path and Analysis
To pinpoint the error, let’s follow the trail through all functions involved. These flow of function calls is as follows: fetch_mail > server.write() > _update_cron() > toggle() > try_write()
Now, let’s first have a look at the function ‘try_write’.
def try_write(self, values):
try:
with self._cr.savepoint():
self._cr.execute(f"""
SELECT id
FROM "{self._table}"
WHERE id IN %s
FOR NO KEY UPDATE NOWAIT
""", [tuple(self.ids)], log_exceptions=False)
except psycopg2.OperationalError:
pass
else:
return super(ir_cron, self).write(values)
return False
The try_write function contains the exact statement which triggers the error. Great… however, in order to find out what causes the actual problem, we need to navigate two levels back up and visit the function ‘_update_cron’:
@api.model
def _update_cron(self):
if self.env.context.get('fetchmail_cron_running'):
return
try:
# Enable/Disable cron based on certain conditions
cron = self.env.ref('mail.ir_cron_mail_gateway_action')
cron.toggle(model=self._name, domain=[('state', '=', 'done'), ('server_type', '!=', 'local')])
except ValueError:
pass
Remember the context variable ‘additionnal_context’ defined in the function ‘fetch_mail’? Well, here it is again. However, for it to be accessible here, the calling code would need to call this function or a parent function like ‘write’ using the helper function ‘with_context’. This happens in various places in ‘fetch_mail’ but not at the final ‘write’ statement where it causes this error message. Clearly some developer knew this would cause problems, otherwise what would be the point of adding this code to the function ‘_update_cron’.
So, the solution seems simple enough, just call the write function with the custom context defined at the beginning of the fetch_mail function and we should be good to go. But before we get to the solution, let’s make sure we’re not fixing something not caused by Odoo itself.
What Causes the Lock?
The core issue arises because Odoo itself locks the row before fetch_mail attempts its update operation. Here’s how we can confirm using PostgreSQL’s pg_locks and pg_stat_activity to verify:
-- Find blocking PIDs
SELECT pg_blocking_pids(pid) AS wait_for
FROM pg_locks
WHERE relation = 'ir_cron'::regclass;
The result initially proves that there indeed is a blocking PID active at the time ‘fetch_mail’ runs.
wait_for
----------
{}
(1 row)
So, high time to find out who is responsible:
-- Examine lock details
SELECT relation::regclass, locktype, database, virtualtransaction, pid, mode, granted, fastpath
FROM pg_locks
WHERE relation = 'ir_cron'::regclass;
relation | locktype | database | virtualtransaction | pid | mode | granted | fastpath
----------+----------+----------+--------------------+------+--------------+---------+----------
ir_cron | relation | 61185 | 6/125 | 1407 | RowShareLock | t | t
(1 row)
-- Find query blocking the lock
SELECT pid, usename, state, query
FROM pg_stat_activity
WHERE pid=1407;
pid | usename | state | query
------+---------+---------------------+---------------------------------------------------------------------------------------------------
1407 | odoo | idle in transaction |
| | | SELECT *
| | | FROM ir_cron
| | | WHERE active = true AND numbercall != 0
| | | AND (nextcall <= (now() at time zone 'UTC')
| | | OR EXISTS (
| | | SELECT cron_id
| | | FROM ir_cron_trigger
| | | WHERE call_at <= (now() at time zone 'UTC')
| | | AND cron_id = ir_cron.id
| | | )) ORDER BY priority LIMIT 1 FOR NO KEY UPDATE SKIP LOCKED
| | |
(1 row)
So, there we have it, Odoo itself is locking the row in the ir_cron table prior to calling fetch_mail in the function ‘_get_all_ready_jobs’. The function fetch_mail then tries to update the row after updating the incoming mail servers timestamp through the function ‘_update_cron’ but, because the row is locked, the SELECT … FOR NO KEY UPDATE NOWAIT statement fails. (After the fetch_mail function finishes, ir_cron is updated by the process owning the lock.)
You may be thinking ‘But… no errors are logged in my Odoo log file? Surely this doesn’t happen in my environment.’ and you may be right. However do note the following important detail in the code of the ‘try_write’ function:
self._cr.execute(f"""
SELECT id
FROM "{self._table}"
WHERE id IN %s
FOR NO KEY UPDATE NOWAIT
""", [tuple(self.ids)], log_exceptions=False)
Notice the parameter ‘log_exceptions’? If this is set to false, no exceptions will be logged in Odoo’s log file. So, even though there may be no errors logged in the Odoo log file, this doesn’t necessarily imply that your environment is not affected. The symptom could have been simply hidden from your sight.
Note: It is very hard to find the row lock using pg_locks in real time. The row lock here was found while debugging the ‘fetch_mail’ code and freezing Odoo’s execution upon entry into the function ‘fetch_mail’ and then querying the PostgreSQL database for locks.
Implementing the Solution
The solution is surprisingly straightforward: ensure that the problematic write is called with the custom context initially defined:
# Original line: server.write({'date': fields.Datetime.now()})
server.with_context(additionnal_context).write({'date': fields.Datetime.now()})
return True
By calling the ‘write’ function with the helper function ‘with_context’, the variable ‘fetchmail_cron_running’ defined in it will be passed down to the ‘_update_cron’ method which can then check it for the value assigned to it. If the value is true, updating cron is skipped and the PostgreSQL error is avoided.
Deployment Steps
To address this effectively:
- Modify Code: Update the fetch_mail function in Odoo’s source.
- Reinstall Mail Module: Re-run updates to propagate changes safely:
sudo systemctl stop <odoo-service-name>odoo-bin -d <odoo-database> -u mail -c <odoo-configuration-file> --stop-after-init- sudo systemctl start <odoo-service-name>
Important: Code modifications like this need to be delivered through GitHub repository updates. Understand that this will fix the problem only temporarily. Any updates to the file addons/mail/models/fetchmail.py will overwrite the custom modifications during code updates.
Conclusion
Understanding the internal dynamics between Odoo and PostgreSQL reveals the seemingly complex error’s simplicity. By tracing locking origins and applying targeted code adaptations, we resolve potential discrepancies before they manifest visibly. By fixing the mail server code, you maintain robust Odoo operation, harmonized by aligned PostgreSQL interactions.
Stay connected for further deep dives into Odoo and database administration, exploring common obstacles and articulate solutions.
