Need Concurrency Help

[Jonathan Wood]

So... I've implemented a feature to allow users to control the order of
items in one of my tables. This involves adding a new column to my table,
SortOrder.

And I have two stored procedures that deal with this new column: One that
creates a new item with a SortOrder value higher than any that exist. And
another that swap the SortOrder values between two rows.

But now I need help in making these two stored procedures robust in
multi-user situations.

My create routine looks like this:
DECLARE @SortOrder int
SELECT @SortOrder = (SELECT MAX(SortOrder) FROM mc_WorkoutDetails WHERE
WorkoutID = @WorkoutID)
IF @SortOrder IS NULL
SELECT @SortOrder = 1
ELSE
SELECT @SortOrder = (@SortOrder + 1)

INSERT INTO mc_WorkoutDetails (WorkoutID, ActivityID, Reps, Sets, Minutes,
Comments, SortOrder)
VALUES (@WorkoutID, @ActivityID, @Reps, @Sets, @Minutes, @Comments,
@SortOrder)
And my swap routine looks like this:
DECLARE @tmp int
SELECT @tmp = (SELECT SortOrder FROM mc_WorkoutDetails WHERE ID = @ID1)
UPDATE mc_WorkoutDetails SET SortOrder = (SELECT SortOrder FROM
mc_WorkoutDetails WHERE ID = @ID2) WHERE ID = @ID1
UPDATE mc_WorkoutDetails SET SortOrder = @tmp WHERE ID = @ID2
Any suggestions (even suggestions about my existing approach) appreciated!

Thanks.

Jonathan

 

[Ian]

So... I've implemented a feature to allow users to control the order of
items in one of my tables. This involves adding a new column to my table,
SortOrder.

And I have two stored procedures that deal with this new column: One that
creates a new item with a SortOrder value higher than any that exist. And
another that swap the SortOrder values between two rows.

But now I need help in making these two stored procedures robust in
multi-user situations.

My create routine looks like this:



DECLARE @SortOrder int
SELECT @SortOrder = (SELECT MAX(SortOrder) FROM mc_WorkoutDetails WHERE
WorkoutID = @WorkoutID)
IF @SortOrder IS NULL
SELECT @SortOrder = 1
ELSE
SELECT @SortOrder = (@SortOrder + 1)

INSERT INTO mc_WorkoutDetails (WorkoutID, ActivityID, Reps, Sets, Minutes,
Comments, SortOrder)
VALUES (@WorkoutID, @ActivityID, @Reps, @Sets, @Minutes, @Comments,
@SortOrder)

1.

SELECT @SortOrder = (SELECT MAX(SortOrder) FROM mc_WorkoutDetails
WHERE
WorkoutID = @WorkoutID)

would also work as:

SELECT @SortOrder = (SELECT MAX(SortOrder) FROM mc_WorkoutDetails)

- which may only hit the index on SortOrder, I'm not sure. In any
case, you don't need to go with the next highest from the current
list, because there's no requirement for the SortOrder field to be 1,
2, 3, ... when 1, 5, 9 will do just as well over a given WorkoutID -
i.e. the values have to increase, but they don't need to be
sequential.


2. Can you use an auto-incrementing column here? This is an honest
question, I've never seen them used for anything but primary keys, but
in theory this would save you doing any work on your inserts.


3. With enough determination you can combine this into one SQL
statement:

INSERT INTO mc_WorkoutDetails (WorkoutID, ActivityID, Reps, Sets,
Minutes,
Comments, SortOrder)
VALUES (@WorkoutID, @ActivityID, @Reps, @Sets, @Minutes, @Comments,
ISNULL(SELECT MAX(Sortorder)+1 from mc_WorkoutDetails, 1))

(ish - ISNULL syntax from memory)

And my swap routine looks like this:



DECLARE @tmp int
SELECT @tmp = (SELECT SortOrder FROM mc_WorkoutDetails WHERE ID = @ID1)
UPDATE mc_WorkoutDetails SET SortOrder = (SELECT SortOrder FROM
mc_WorkoutDetails WHERE ID = @ID2) WHERE ID = @ID1
UPDATE mc_WorkoutDetails SET SortOrder = @tmp WHERE ID = @ID2

Also do-able with a single update. And, of course, you'd better be
doing all these in a transaction if you use multiple statements...

Cheers,
Ian.

 

[Jonathan Wood]

Ian,

SELECT @SortOrder = (SELECT MAX(SortOrder) FROM mc_WorkoutDetails
WHERE
WorkoutID = @WorkoutID)

would also work as:

SELECT @SortOrder = (SELECT MAX(SortOrder) FROM mc_WorkoutDetails)

- which may only hit the index on SortOrder, I'm not sure. In any
case, you don't need to go with the next highest from the current
list, because there's no requirement for the SortOrder field to be 1,
2, 3, ... when 1, 5, 9 will do just as well over a given WorkoutID -
i.e. the values have to increase, but they don't need to be
sequential.

I'm not sure I'm comfortable with that approach. For one thing, this is a
table that could grow very big over time if we get the expected number of
users. I'm currently using int as my SortOrder type and I'm not sure that an
overflow is out of the question. Interesting to think about though.

2. Can you use an auto-incrementing column here? This is an honest
question, I've never seen them used for anything but primary keys, but
in theory this would save you doing any work on your inserts.

Hadn't considered that. Like I said, I'm not sure I'm comfortable with this
approach but will think about it.

3. With enough determination you can combine this into one SQL
statement:

In fact, I've managed to do that with my Insert procedure using code very
similar to what you've posted.

Also do-able with a single update. And, of course, you'd better be
doing all these in a transaction if you use multiple statements...

I have not yet found any way to do this one with a single statement. I'm
currently looking into the correct isolation level and/or hints for a
transaction.

Thanks for the input!

Jonathan

 

[Cor Ligthert[MVP]]

Jonathan,

You reply probably that you don't have anything on my answer.

However, you are probably the only one on earth who want to order his data
fixed in the database.

The way your data is ordered for a user is a user interface part. Not even a
datalayer part, and for sure not an action in a database table.

Cor

 

[Jonathan Wood]

Cor,

You reply probably that you don't have anything on my answer.

I'm unable to parse this. Your last reply to me seemed to indicate that you
decided you were not the one to give me an answer. I accepted what I
understood you to be telling me.

However, you are probably the only one on earth who want to order his data
fixed in the database.

The way your data is ordered for a user is a user interface part. Not even
a datalayer part, and for sure not an action in a database table.

I'm at a loss as to why you're getting hung up on this particular issue.

There is no way to reliably order a table without having a field that is
suitable for ordering by. So I needed to create a field for this purpose
along with a couple of stored procedures for special handling of this field.
That IS a database part.

As far as my interface, the only reason I even mentioned the UP/DOWN buttons
is because I was asked. To me, they are irrelevant to what I'm asking about
these database-design issues.

Jonathan

 

[Cor Ligthert[MVP]]

As you really want a location in your database, then add a placenumber as
item, however as soon as two users are doing this, then the computer will
only be busy changing the placenumbers.

Just my opinion.

Cor

 

[Jonathan Wood]

Cor,

As you really want a location in your database, then add a placenumber as
item, however as soon as two users are doing this, then the computer will
only be busy changing the placenumbers.

Just my opinion.

I'm not sure I get your point here. But you seem to have an issue with the
approach I'm taking.

Please, can you offer even a single way to control item order without a
placeholder column in the table? If so, I'd love to hear it.

Jonathan