Board index » delphi » isolation level problem

isolation level problem

hi, I'm fairly new to databases and I'm experiencing a problem. First, let
me explain what I'm trying to accomplish, then what my solution is and the
problems that arise when using this solution.

Goal:
I have a table which contains various questions that make up a questionaire.
It is important that the order of apppearance on the questionaire can be
altered at any time.

Solution:
I added an integer field 'item_index' to set the place of each record. This
index needs to be set to the maximum index + 1 when inserting. Also, when
moving a record to another index value, other records need to be updated
accordingly.

Problem:
When I retrieve the maximum value of the index and then insert a record with
an index that is 1 higher, how do I prevent other transactions from reading
this index as soon as it has been read. i.e. if two transactions read the
index 'at the same time' they will both try to insert a new record with the
same index value. Also, when deleting a record the maximum index will
eventually change after all records have been updated; I need a way to lock
access to this table in a 'wait' fashion for as long as any one transaction
has access to it. This transaction should then be 'read commited' to allow
the waiting tranaction to get the correct maximum value after the waiting is
done.

My questions:
Is my approach possible?
Is there perhaps another, better or more common solution to this kind of
problem. I've thought about using generators but since there can be
questions for many questionaires in this table it would produce gaps in the
index values and also rearranging becomes more tedious becuase you can then
only 'swap places'.

TIA

Ronald Sipkema
r.c.j.sipk...@student.utwente.nl

 

Re:isolation level problem


Use a generator to generate the next index number. This guarantees that each
user will get a unique value. You can reset the value of the generator if
necessary as part of your renumbering process. You might consider
incrementing the index number by 10 to make it easy to insert questions in
any location. To get exclusive access to the table use read/write table
stability as your transaction isolation level for your IBTransaction
component.

Another approach to this problem is to use a double precision floating point
field as the index number. This allows reordering without renumbering which
means that you never have to lock the entire table. To insert a record
between records A and B get the index number of B, subtract the index number
of A, divide the result by 2 and add that value to the index number of A.
This gives you a new index number halfway between the index numbers of the
existing records.

--
Bill

Other Threads