Hi there guys,

I have a table like the one below:

CREATE TABLE `news` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`name` VARCHAR( 100 ) NOT NULL ,
`position` INT( 11 ) NOT NULL
) ENGINE = MYISAM ;

And the field position specifies the position of each news while they
are being displayed.

What I have at the moment is the following processes:

* When adding a new item, I need to know what is the max position, so
that the new item position will be: max_position + 1 (adding at the
bottom);

That I can achieve with:

SELECT position
FROM news
ORDER BY position DESC
LIMIT 1

And I think this isn't the best way of doing this, since this system
is multi-user and maybe there will be problems if two users add a new
item at the same time, then, there will be 2 equal positions.

Is this correct?
How can I avoid this?

I also have the same kind of problem while deleting items, imagine
that a user deletes a couple of items and another user at the same
time updates some items positions, won't that be a problem? I'm using
transactions on this one, but I'm not sure it will avoid this
situation.

Same doubt when moving an item up or down (using transactions on this
one too).


Thanks in advance.