We have developed a web page which can be used to create or delete courses. (It can be used simultaneously by multiple users).

The web page uses a single database transaction each time it runs. The transaction can consist of an insert of a single row to the course table, a deletion of a single row from the course table, or an insert and a delete within the same transaction. Note that a delete and insert of a course always applies to the same course within the same category - it does not support category changes at the moment.

This web page periodically fails with duplicate key errors on the course table, field "sortorder". In order to try to prevent this, we call "fix_course_sortorder" in the following places, (but it does not seem to prevent the problem):

1. If the transaction fails;

fix_course_sortorder($category);
fix_course_sortorder();

where $category is the ID of the category the course was being inserted into or deleted from.

2. After a course deletion:

fix_course_sortorder(); (without a category ID passed).

3. Before a course insertion

fix_course_sortorder($category);

(where $category is the ID of the category the course is going to be inserted into)

We also default the sortorder of the new course to the min(sortorder) for courses in that category, minus 1.
If there are no pre-existing courses in that category we default it to 100.

4. After a successful course insertion

fix_course_sortorder(); (without a category ID passed).

Could someone please advise on what the correct approach is to calling fix_course_sortorder around course insertions and deletions, as we appear to be doing something wrong with our solution which is causing the duplicate key conflicts.