> as I understand it, when you insert a row into a table with an > identity column, it has to lock syscolumns to update the identity value - > from the docs: > > "Derby keeps track of the last increment value for a column in a cache. It > also stores the value of what the next increment value will be for the > column on disk in the AUTOINCREMENTVALUE column of the SYS.SYSCOLUMNS > system table. Rolling back a transaction does not undo this value, and > thus rolled-back transactions can leave "gaps" in the values automatically > inserted into an identity column. Derby behaves this way to avoid locking > a row in SYS.SYSCOLUMNS for the duration of a transaction and keeping > concurrency high."
Ah-ha! This answers something for me. In our application, I just recently started using dynamically created tables (created on demand as needed). However, when table creation occurred, I would see a large number of deadlocks for other, concurrent transactions which were inserting into *unrelated* tables. This essentially ground everything to a halt, similar to Andrew's experience. My reaction was/is, "This is ridiculous", but I didn't have time to pursue it further at the time. My workaround was to use a global shared/exclusive (read/write) lock for all database access, locking exclusively when creating tables. This was acceptable in our case because as it turns out, table creation happens very seldom. Nevertheless, this appears to be a weak point for Derby... it shouldn't be necessary to do this type of global/external locking for table creation. Comments? Advice? Is there a better way for Derby to manage this, so that it doesn't produce lock contention across unrelated tables? Regards, Jim
