 | |
Foreign Key induced locking
sybase.public.sqlanywhere.general sybase.public.sqlanywhere.general
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Foreign Key induced locking -
11-05-2004
, 05:36 AM
#2
| |||
| |||
|
|
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 right thing - 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 task_data. 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, John Davey |
#3
| |||
| |||
|
|
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 right thing - 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 task_data. 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, John Davey |
#4
| |||
| |||
|
|
What exactly are you asking? You're describing how the ASA engine does locking....and your question? John Davey suggested that: 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 right thing - 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 task_data. 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, John Davey |
#5
| |||
| |||
|
|
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) 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 right thing - 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 task_data. 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, John Davey |
#6
| |||
| |||
|
|
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. Breck> wrote 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) 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 right thing - 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 task_data. 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, John Davey |
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
www.risingroad.com
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.