ConcurrentModificationError with "delete-orphan" on firebird

[Former user]

When I use a relation between two classes with cascade="all, delete-orphan", I run into a ConcurrentModificationError with Firebird. It doesn't seem to happen with PostgreSQL, so I have some hope that my code may actually be valid. In that case something seems to be wrong with the rowcount in the firebird module.

Example:

from sqlalchemy import *

md = BoundMetaData(r"firebird://sysdba:masterkey@localhost/c:\db\mydb.gdb")

table1 = Table("table1", md,
                  Column("id", Integer, Sequence("s_t1_id", optional=True), primary_key=True),
                  Column("name", String(100), unique=True, nullable=False))

table1.create(checkfirst=True)

table2 = Table("table2", md,
                Column("id", Integer, Sequence("s_t2_id", optional=True), primary_key=True),
                Column("t1_id", Integer, nullable=False),
                Column("name", String(50), nullable=False),
                ForeignKeyConstraint(["t1_id"]("t1_id"), ["table1.id"]("table1.id"), ondelete="CASCADE", onupdate="CASCADE"))

table2.create(checkfirst=True)

session = create_session()

class T1(object):
    pass

class T2(object):
    pass

t1mapper = mapper(T1, table1)
t2mapper = mapper(T2, table2)
t1mapper.add_property("t2list", relation(T2, backref="t1", cascade="all, delete-orphan"))

t1obj = T1()
t1obj.name = "test"
session.save(t1obj)

session.flush()

t2obj = T2()
t2obj.name = "test2"
t2obj.t1_id = t1obj.id
t1obj.t2list.append(t2obj)
session.save(t2obj)

t2obj = T2()
t2obj.name = "test3"
t2obj.t1_id = t1obj.id
t1obj.t2list.append(t2obj)
session.save(t2obj)

session.flush()

session.delete(t1obj)
session.flush()

generates this traceback:

Traceback (most recent call last):
  File "C:\Programme\Python25\Lib\site-packages\pythonwin\pywin\framework\scriptutils.py", line 310, in RunScript
    exec codeObject in __main__.__dict__
  File "C:\Programme\Python25\Lib\site-packages\play\firebird.py", line 52, in <module>
    session.flush()
  File "C:\Programme\Python25\Lib\site-packages\sqlalchemy\orm\session.py", line 219, in flush
    self.uow.flush(self, objects)
  File "C:\Programme\Python25\Lib\site-packages\sqlalchemy\orm\unitofwork.py", line 188, in flush
    flush_context.execute()
  File "C:\Programme\Python25\Lib\site-packages\sqlalchemy\orm\unitofwork.py", line 327, in execute
    head.execute(self)
  File "C:\Programme\Python25\Lib\site-packages\sqlalchemy\orm\unitofwork.py", line 464, in execute
    UOWExecutor().execute(trans, self)
  File "C:\Programme\Python25\Lib\site-packages\sqlalchemy\orm\unitofwork.py", line 789, in execute
    self.execute_delete_steps(trans, task)
  File "C:\Programme\Python25\Lib\site-packages\sqlalchemy\orm\unitofwork.py", line 816, in execute_delete_steps
    self.execute_childtasks(trans, task, True)
  File "C:\Programme\Python25\Lib\site-packages\sqlalchemy\orm\unitofwork.py", line 835, in execute_childtasks
    self.execute(trans, child, isdelete)
  File "C:\Programme\Python25\Lib\site-packages\sqlalchemy\orm\unitofwork.py", line 789, in execute
    self.execute_delete_steps(trans, task)
  File "C:\Programme\Python25\Lib\site-packages\sqlalchemy\orm\unitofwork.py", line 818, in execute_delete_steps
    self.delete_objects(trans, task)
  File "C:\Programme\Python25\Lib\site-packages\sqlalchemy\orm\unitofwork.py", line 795, in delete_objects
    task._delete_objects(trans)
  File "C:\Programme\Python25\Lib\site-packages\sqlalchemy\orm\unitofwork.py", line 458, in _delete_objects
    task.mapper.delete_obj(task.todelete_objects, trans)
  File "C:\Programme\Python25\Lib\site-packages\sqlalchemy\orm\mapper.py", line 1064, in delete_obj
    raise exceptions.ConcurrentModificationError("Updated rowcount %d does not match number of objects updated %d" % (c.cursor.rowcount, len(delete)))
ConcurrentModificationError: Updated rowcount 1 does not match number of objects updated 2

I'm using sqlalchemy 0.3.1, kinterbasdb 3.2 and Firebird 2.0.0.

[Mike Bayer]

if firebirds rowcount works like MySQLs, where it returns the number of rows that were actually updated vs. the number of rows that matched the criterion, then its "broken". we have a method on dialect called supports_sane_rowcount() - if you modify the FBDialect to have a supports_sane_rowcount() method that returns False, then this check wont occur. Since i dont have firebird to verify can you please try this out ?

also some confirmation on FB's rowcount behavior would be helpful here.

[Former user]

I can confirm that

1. the script above fails as stated and
2. setting FBExecutionContext.supports_sane_rowcount() to False makes the difference, and the script runs fine

I'll try to understand the rowcount mechanism.

[Former user]

Patch that disables sane_rowcount for FB and new test with case above

[Mike Bayer]

so, i see you added a test for rowcount in combination with ON DELETE/UPDATE CASCADE. is that where firebird acts funky ? im hoping "rowcount" usually means "number of rows affected" not including cascades (else the entire SA mechanism for checking concurrent mods doesnt work). this since I actually dont have much experience using CASCADE.

[Former user]

I just morphed the script above in a test case, there surely is an easier path to trigger the rowcount problem, though.

I'll try to understand better what http://kinterbasdb.sourceforge.net/dist_docs/usage.html really means when it tags the engine support for rowcount as "quirky", as well as SA expectation. I never used this functionality before.

[Mike Bayer]

Firebird supports_sane_rowcount disabled in changeset:2642

[Former user]

Some new patches for both 0.3.x and 0.4 branches (and a separate test script). Please see http://tinyurl.com/2gsr8t for further information.

Roger Demetrescu

[Former user]

Couldn't upload the patches, so you can find it at:

http://groups.google.com/group/sqlalchemy/browse_thread/thread/49aaa4945721b15a

Roger Demetrescu

[Former user]

Firebird has now supports_sane_rowcount disabled (again ;) ) on both 0.3 and 0.4 branches.

Changesets 3535 and 3536.

[Mike Bayer]

status on this ticket? going to put it in the 0.5xx milestone for now.

[Mike Bayer]

tested against 0.6 and it works, will assume same for 0.5

[Mike Bayer]

Removing milestone: 0.5.xx (automated comment)