serializable isolation level on cascade delete

["Sumner, Alex" <Al...@mentor.com>]

Hi, 

I have a problem with concurrent access to my Derby database (version
10.3.2.1). I have a tree like data structure, there are multiple
projects, each project contains multiple designs, each design contains
multiple components. There's a project table, a design table and a
component table, with foreign key relationships reflecting the
ownership: project -> design, design -> component (I'm simplifying it a
bit, there are actually more layers). The problem comes when I delete a
project. The foreign key relationships are set to cascaded delete, so
deleting the row for a project will also remove all its designs and all
their components. The problem I have is that deleting a project can take
a while (many minutes, but I can live with that) and whilst its
happening no other user can work on a design in another project because
there are table level locks held on the design and component tables
(which is much more of a problem). 

I am using read-committed transaction isolation and have tried setting
the derby.locks.escalationThreshold high, but this seems to make no
difference to the behaviour during a cascade deletion. I see that row
level locking is used on the root table (the project table) but the
child tables (design and component) are locked at table level and have
serializable isolation level, though I don't really want that. The Derby
log is reproduced below. Is there any way to prevent the use of table
locking on the child tables? I would be happy for the deletion to take a
little longer if it meant concurrent access to other projects was not
affected.

Any advice greatly would be appreciated. Thanks in advance,

Alex Sumner


excerpt from Derby log: 

            Source result set: 

                        Index Scan ResultSet for DESIGN using index On
Foreign Key at serializable isolation level using exclusive table
locking chosen by the optimizer 

                        Number of opens = 1 

                        Rows seen = 8 

                        Rows filtered = 0 

                        Fetch Size = 1 

                                    constructor time (milliseconds) = 0 

                                    open time (milliseconds) = 0 

                                    next time (milliseconds) = 0 

                                    close time (milliseconds) = 0 

                                    next time in milliseconds/row = 0 

  

                        scan information: 

                                    Bit set of columns fetched=All 

                                    Number of columns fetched=2 

                                    Number of deleted rows visited=0 

                                    Number of pages visited=1 

                                    Number of rows qualified=8 

                                    Number of rows visited=8 

                                    Scan type=btree 

                                    Tree height=1 

                                    start position: 

            >= on first 1 column(s). 

            Ordered null semantics on the following columns: 

  

                                    stop position: 

            > on first 1 column(s). 

            Ordered null semantics on the following columns: 

  

                                    qualifiers: 

None 

                                    optimizer estimated row count:
13.00 

                                    optimizer estimated cost:
215.93 

                        

Referential Actions On Dependent Tables: 

                                    Delete Cascade ResultSet using row
locking: 

                                    deferred: false 

                                    Rows deleted = 0 

                                    Indexes updated = 3 

                                    Execute Time = 0 

            Project-Restrict ResultSet (552): 

            Number of opens = 1 

            Rows seen = 0 

            Rows filtered = 0 

            restriction = false 

            projection = true 

                        constructor time (milliseconds) = 0 

                        open time (milliseconds) = 0 

                        next time (milliseconds) = 0 

                        close time (milliseconds) = 0 

                        restriction time (milliseconds) = 0 

                        projection time (milliseconds) = 0 

                        optimizer estimated row count:            6.00 

                        optimizer estimated cost:          788.53