Foreign Key induced locking - 11-05-2004 , 05:36 AM
using ASA v9.0.1 build 1899 running on Win XP
Application written in C# using the iAnywhere.Data.AsaClient - isolation
level 0 (READ_UNCOMITTED)
My question is about strategies for minimising blocking caused by foreign
key relationships when inserting rows.
Lets say we have a table 'Task'. We also have a table 'Task_Predecessor'
which records the predecessor task(s) for each task - ie it contains
references to two task rows. Now, the task table has other columns which are
dynamic - typically being updated when the task is processed - this clearly
involves locking the row when making the update. My problem is that in
parallel (on a separate thread) we may be creating new task rows (later
tasks - may be intened to be processed far in the future). When a new task
is created new rows are inserted into task_predecessor - for every task in
the 'chain' of history. To do this insert the row in task gets locked also
(read lock to ensure foreign key integrity at commit). So now I have a
situation where the act of creating new tasks (possibly far in the future)
can cause blocking against the tasks which are 'active' now. What can I do
to avoid or minimise this?
Ideas I have come up with so far;
- short transactions all round; will do this as much as possible
- not make the 'predecessor task' column a real foreign key; sacrifice
referential integrity checking and leave it up to application to do the
- separate the task table into a task_id table containing just the primary
key, and a task_data table which FKs to it and contains all the actual task
data. Now task_predecessor FKs (twice) to task_id. This would mean that new
inserted tasks would not directly conflict with updates to the real
Are there any other good strategies? For my application it is generally true
that tasks get created, modified over time, but not deleted (and certainly
they never have their PKs updated), so the existing locking is not really
beneficial to me, although I do understand the general argument. The last
suggestion presented above seems to be pointing towards generally defining a
table exclusively to hold the PK for an entity, and then put all the entity
data into other related tables - could possibly benefit from separating the
data into multiple tables to reflect distinct use by separate updaters.
any advice gratefully received,
Re: Foreign Key induced locking - 11-05-2004 , 08:06 AM
What exactly are you asking? You're describing how the ASA engine does
locking....and your question?
John Davey suggested that:
Re: Foreign Key induced locking - 11-05-2004 , 12:14 PM
Schedule the future task creation process for an off-peak
time, perhaps using an event?
Post the future tasks to a different "shadow" table (with no
FK), to be copied to the real table in off-peak hours?
Use triggers to enforce the referential integrity and remove
the foreign key relationship? (you may have to add an index
to the original child column for performance of joins)
Re: Foreign Key induced locking - 11-08-2004 , 03:30 AM
the questions are the sentences ending with question marks. Thanks for your
"Someone" <abc (AT) def (DOT) com> wrote
Re: Foreign Key induced locking - 11-08-2004 , 03:34 AM
Thanks for this - all possible part solutions. I am wondering whether the
'pattern' of having a table holding only the PK is one that you have used /
considered / rejected? For some types of application it seems to me that
this may be a fairly general problem - the locking in place causes
significant blocking where the use of the data means that it really is not
adding much value.
Re: Foreign Key induced locking - 11-08-2004 , 04:28 AM
It is an occasional problem, significant only in the fact it is can
difficult to diagnose (the lock is on a table not directly named in
the SQL statement getting the lock). If blocks persist then something
is running too long without committing... the reply from Someone might
have been sarcastic and unhelpful but it was generally correct
You have a special case. You "know" that there will be no problem with
referential integrity. Since you "know" that, but ASA does not, you
must do something different. Your solution (splitting the task table)
will probably work just fine... sorry I didn't look at it closer
earlier, I just babbled off a bunch of alternatives
This is also an opportunity to look for long running transactions.
They cause hate and discontent among the user community, made even
worse when the users do *not* complain but simply accept the current
state of affairs. I'm up to my neck in that situation right now,
sackloads of blocks and a user community that's been unhappily but
silently living with it for years.
FWIW request level logging is a good place to start if you want to
explore the issue further.
On 8 Nov 2004 01:34:25 -0800, "John Davey"
<john.davey (AT) automationpartnership (DOT) com> wrote:
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/AS.../risingroad-20
bcarter (AT) risingroad (DOT) com
RisingRoad SQL Anywhere and MobiLink Professional Services