Calls to db.newid in separate processes can race, resulting in duplicate
ids and failed database insertion.

Traceback (most recent call last):
  File "/usr/lib/pymodules/python2.6/roundup/mailgw.py", line 1388, in
handle_Message
    return self.handle_message(message)
  File "/usr/lib/pymodules/python2.6/roundup/mailgw.py", line 1444, in
handle_message
    return self._handle_message(message)
  File "/usr/lib/pymodules/python2.6/roundup/mailgw.py", line 1522, in
_handle_message
    parsed_message.create_msg()
  File "/usr/lib/pymodules/python2.6/roundup/mailgw.py", line 1098, in
create_msg
    messageid=messageid, inreplyto=inreplyto, **msg_props)
  File "/usr/lib/pymodules/python2.6/roundup/backends/rdbms_common.py",
line 2945, in create
    newid = self.create_inner(**propvalues)
  File "/usr/lib/pymodules/python2.6/roundup/backends/rdbms_common.py",
line 1630, in create_inner
    self.db.addnode(self.classname, newid, propvalues)
  File "/usr/lib/pymodules/python2.6/roundup/backends/rdbms_common.py",
line 948, in addnode
    self.sql(sql, vals)
  File "/usr/lib/pymodules/python2.6/roundup/backends/rdbms_common.py",
line 219, in sql
    cursor.execute(sql, args)
IntegrityError: PRIMARY KEY must be unique

Recommend atomically incrementing the id first, and then reading it.

Hi Matt,
thanks for this report and for using or trying roundup!

Can you say more how to you encountered this issue?
This might make it easier to reproduce for me.
(Somehow I was always under the impression that write access to the
database would have been locked and then serialised by the lock,
but Richard will know better and I never checked.)

Thanks,
Bernhard

We get this on the Mercurial project when we close bugs in commit
messages. We have a robot that scans each commit for the close signature
and fires off an email to our bts mail gateway. Each message to the
gateway starts a separate roundup process.

We hit this race quite consistently. I think this is helped by Postfix,
which scans its input queues on a timer, and thus consistently fires off
messages simultaneously on different cores.

As I understand sqlite3/pysqlite's locking model, pysqlite opens the
database in shared mode and automatically opens and closes transactions
for certain kinds of SQL statements. So we end up with multiple
processes looking up the msgid simultaneously, adding one, then doing
write transactions (that succeed). Then we next go on to try to insert
two messages with the same message id and fail on the second one.

There's nothing anywhere in the sqlite backend that looks like locking.

Matt, thanks for the additional output.
Do you have a workaround available?
I would expect postfix to have an option for serializing external
application calls.

We stuck a time.sleep(10) in our robot.