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.

On 28 Sep, 10:36, "João Morais" <jcsmor...@gmail.com> wrote:
> 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.

Why not just use your autoincrement field?

> Why not just use your autoincrement field?

Because positions can be changed later, moved up or down.

PS: At the example above store engine is myisam but I'm using innodb.

On Fri, 28 Sep 2007 09:50:42 -0000, João Morais wrote:
>> Why not just use your autoincrement field?
>
> Because positions can be changed later, moved up or down.

Since your application is driving the data change, then your application
has to resolve the problems.

You may wish to look at locking the tables in question for this kind of
thing to be done. It could be handled as a transation as well, but it
would require more analysis of other parts of the system than just this
presentation ordering to determine what things would need to happen to
ensure that only the right changes get made.

See http://dev.mysql.com/doc/refman/5.0/...-commands.html
for information about both locking and transactions.

> PS: At the example above store engine is myisam but I'm using innodb.

That won't solve your problem all by itself.

--
The plural of datum is not "facts".
A collection of facts is not "knowledge".

> You may wish to look at locking the tables in question for this kind of
> thing to be done. It could be handled as a transation as well, but it
> would require more analysis of other parts of the system than just this
> presentation ordering to determine what things would need to happen to
> ensure that only the right changes get made.
>
> See http://dev.mysql.com/doc/refman/5.0/...-commands.html
> for information about both locking and transactions.

" ...
This is the default isolation level of InnoDB. SELECT ... FOR UPDATE,
SELECT ... LOCK IN SHARE MODE, UPDATE, and DELETE statements that use
a unique index with a unique search condition lock only the index
record found, not the gap before it. With other search conditions,
these operations employ next-key locking, locking the index range
scanned with next-key or gap locks, and block new insertions by other
users.

In consistent reads, there is an important difference from the READ
COMMITTED isolation level: All consistent reads within the same
transaction read the same snapshot established by the first read. This
convention means that if you issue several plain SELECT statements
within the same transaction, these SELECT statements are consistent
also with respect to each other.
...."

I'm already using transactions in most of the features supported by
this system, from what I've read in the documentation, only
transactions will solve my problems since by default, isolation level
is REPEATABLE READ, and if I understood it right all the SELECT's done
in one transaction will be done in the same snapshot.

Although I think I didn't quite understand the first paragraph does it
mean, that if all the statements are performed with transactions,
there will be no problems (caused by the multiuser-system) since the
index range will be locked and new insertions will be blocked.

Is this right? Can any one explain this to me?

Any will be appreciated.