IntegrityError: duplicate key value violates unique constraint "session_attribute_pk"

[Christian Boos]

Happened when loading a bunch of reports as anonymous.

Information système

Trac	0.12b1
Babel	0.9.5
Docutils	0.6
Genshi	0.6
psycopg2	2.0.8
Pygments	1.2.2dev-20100224
Python	2.5.2 (r252:60911, Oct 5 2008, 19:29:17) [GCC 4.3.2]
pytz	2010g
setuptools	0.6c9
Subversion	1.5.1 (r32289)

Pile d'appel Python

Traceback (most recent call last):
  File "build/bdist.linux-x86_64/egg/trac/web/main.py", line 512, in _dispatch_request
    dispatcher.dispatch(req)
  File "build/bdist.linux-x86_64/egg/trac/web/main.py", line 257, in dispatch
    req.session.save()
  File "build/bdist.linux-x86_64/egg/trac/web/session.py", line 88, in save
    @self.env.with_transaction()
  File "build/bdist.linux-x86_64/egg/trac/db/util.py", line 60, in transaction_wrapper
    fn(ldb)
  File "build/bdist.linux-x86_64/egg/trac/web/session.py", line 117, in delete_session_cookie
    """, attrs)
  File "build/bdist.linux-x86_64/egg/trac/db/util.py", line 142, in executemany
    return self.cursor.executemany(sql_escape_percent(sql), args)
IntegrityError: duplicate key value violates unique constraint "session_attribute_pk"

[pipern]

Similar to #7663? We are investigating having this same problem, so maybe it's not as closed as that ticket suggests (or a regression.)

[pipern]

I think [9342] causes a regression on #7663 (simply by reading the code.)

The final fix would have to be taking into account keeping #9104 fixed, maybe by opening a new cursor.

[Carsten Klein <carsten.klein@…>]

I think that the problem basically lies in the session update code itself:

125	            if not skip and self._old != self:
126	                if not items and not authenticated:
127	                    # No need to keep around empty unauthenticated sessions
128	                    db("DELETE FROM session WHERE sid=%s AND authenticated=0",
129	                       (self.sid,))
130	                db("""DELETE FROM session_attribute
131	                      WHERE sid=%s AND authenticated=%s
132	                      """, (self.sid, authenticated))
133	                self._old = dict(self.items())
134	                db.executemany("""
135	                    INSERT INTO session_attribute
136	                      (sid,authenticated,name,value)
137	                    VALUES (%s,%s,%s,%s)
138	                    """, [(self.sid, authenticated, k, v) for k, v in items])
139	                session_saved = True

• Request 1 is processed, which will lock the db and delete all session attributes available and then it starts inserting the "new" attributes
• Request 2 is processed, which in turn will also delete all session attributes currently available and it will start inserting the "new" attributes

So, while request 1 processing already removed the attributes and begun inserting the new ones, request 2 again removes the newly inserted session attributes and also begins inserting the same session attributes, eventually coming to a point where Thread/Process 1 just inserted the same attribute, which will then result in the experienced duplicate key exception.

Now, in pysqlite, the executemany() will be executed on a per parameter basis, so instead of inserting the bunch of parameters in one go, it will iterate over the existing parameters, see ​http://code.google.com/p/pysqlite/source/browse/src/cursor.c#640 where it reads

    while (1) {
        parameters = PyIter_Next(parameters_iter);
    ...
    }

I presume that the database will not be locked for as long as the operation is processed but on a per insert statement basis.

One possible remedy I see here is to refactor the session handling code so that it will first create a difference set of the attributes in the session that have actually changed and then will delete and reinsert these attributes individually. The overhead is negligible due to the fact that the underlying pysqlite implementation also processes each insert statement on an individual basis.

[Carsten Klein <carsten.klein@…>]

Of course, adding a try/except around the offending lines might do the trick, but it will not solve the inherent problem. See the regression mentioned in comment:2 induced by changeset:9342.

Here, you expect the exception and also loss of more current session data, if you reintroduce the try/except statements instead of only delete/insert the data that has actually changed.

[Remy Blank]

The whole session update is wrapped in a transaction​, so it should be atomic. But somehow, that's not the case.

[Ryan J Ollos]

Moved ticket assigned to next-stable-1.0.x since maintenance of 1.0.x is coming to a close. Please move the ticket back if it's critical to fix on 1.0.x.

[Ryan J Ollos]

Milestone renamed