SEO

I am very interested in the question that what would happen if two
users want to update one record in the database at the same time. How
MySQL would performance? It would drop both of them, update both of
them or have some mechanism to decide which update is the first.

It is in the same situation that when 2 users want to insert a new
entry with the same id at the same time. What I want is a unique id
for each entry. When the users query the database, they both can find
it available. But when they submit the 'insert', I think 2 entries
will both be added to the database.


Any idea for this?

If there exists the write conflict, what is the solution? Lock table?
How?

I know I have many questions. I'd be appreciated if you can provide
some ideas.

Thanks a lot.

>I am very interested in the question that what would happen if two
>users want to update one record in the database at the same time. How
>MySQL would performance? It would drop both of them, update both of
>them or have some mechanism to decide which update is the first.

If transactions are not being used, a whole SQL statement is executed
all at once, without pieces of others mixed in. You can't count
on the relative order of queries coming in on different connections.
(Roughly, it will be first-come-first-served, but you can't count
on that for close ties.) If transactions are being used, the whole
transaction is executed all at once, without pieces of others mixed
in.

>It is in the same situation that when 2 users want to insert a new
>entry with the same id at the same time. What I want is a unique id
>for each entry. When the users query the database, they both can find
>it available. But when they submit the 'insert', I think 2 entries
>will both be added to the database.

One insert succeeds, one fails, assuming you have a unique index or
primary key on that column.

For this particular example, I'd think you can just do the insert.
If it fails with a duplicate key error, the id was already taken.
If it succeeds, that id has been claimed and nobody else can get
it.

In more complex situations, use transactions. One example is the
traditional banking problem: Add $x to the balance for account A,
and subtract $x from the balance for account B. This should fail
(roll back with no changes) if: (a) account A or B (or both) do not
exist, (b) x is negative, or (c) B's new balance is negative. Of
course real banks have a complex web of overdraft fees for this
situation.

If you are inserting into a table with an auto_increment primary
key, you can get the key value for this record with last_insert_id()
in a subsequent query. This returns the last id inserted on *THIS
CONNECTION*, so you don't have to worry about someone else inserting
a record and messing up the number.



>If there exists the write conflict, what is the solution? Lock table?
>How?

Pick one:

1. Put it all in one statement, if possible.
2. Transactions. (Requires InnoDB tables)
3. LOCK TABLE

Depending on what you are doing, #1 or #2 are preferable. #3 tends
to cause more of a bottleneck than necessary (transactions may lock
only part of a table, rather than the whole thing). If part of a
transaction fails, the whole thing gets rolled back (all the changes
are undone).

On Tue, 04 Dec 2007 01:03:28 -0000, Gordon Burditt wrote:
>>If there exists the write conflict, what is the solution? Lock table?
>>How?
>
> Pick one:
>
> 1. Put it all in one statement, if possible.
> 2. Transactions. (Requires InnoDB tables)
> 3. LOCK TABLE
>
> Depending on what you are doing, #1 or #2 are preferable. #3 tends
> to cause more of a bottleneck than necessary (transactions may lock
> only part of a table, rather than the whole thing). If part of a
> transaction fails, the whole thing gets rolled back (all the changes
> are undone).

Or, quit predetermining your pkey, let MySQL assign it out of an
autoincrement column, and use the supplied function to ask for the pkey
when MySQL has finished the insert. There's very seldom a good reason to
make a primary key contain any information aside from the row's own
identity.

--
Crowds want to beat, journalists deserve to be beaten. Where lies
the problem?
-- Lars Syrstad

>>>If there exists the write conflict, what is the solution? Lock table?
>>>How?
>>
>> Pick one:
>>
>> 1. Put it all in one statement, if possible.
>> 2. Transactions. (Requires InnoDB tables)
>> 3. LOCK TABLE
>>
>> Depending on what you are doing, #1 or #2 are preferable. #3 tends
>> to cause more of a bottleneck than necessary (transactions may lock
>> only part of a table, rather than the whole thing). If part of a
>> transaction fails, the whole thing gets rolled back (all the changes
>> are undone).
>
>Or, quit predetermining your pkey, let MySQL assign it out of an
>autoincrement column, and use the supplied function to ask for the pkey
>when MySQL has finished the insert. There's very seldom a good reason to
>make a primary key contain any information aside from the row's own
>identity.

There *IS* a good reason to predetermine (from user input) a unique
index being used as a user name, "handle", email address (assuming
this site provides one using a single fixed domain name) or whatever.
It makes the site more user-friendly and personal. This probably
isn't a primary key. As you said, an autoincrement primary key is
a good idea. But the user name, "handle" or whatever still needs
to be unique and there's good reason to allow the user to choose
one. It also probably deserves an index, since logging in will
probably use this field rather than the primary key, and if users
can refer to each other via the user interface, they will probably
use this field. So I expect a lot of lookups on it.

Gordon Burditt wrote:
>>>> If there exists the write conflict, what is the solution? Lock table?
>>>> How?
>>> Pick one:
>>>
>>> 1. Put it all in one statement, if possible.
>>> 2. Transactions. (Requires InnoDB tables)
>>> 3. LOCK TABLE
>>>
>>> Depending on what you are doing, #1 or #2 are preferable. #3 tends
>>> to cause more of a bottleneck than necessary (transactions may lock
>>> only part of a table, rather than the whole thing). If part of a
>>> transaction fails, the whole thing gets rolled back (all the changes
>>> are undone).
>> Or, quit predetermining your pkey, let MySQL assign it out of an
>> autoincrement column, and use the supplied function to ask for the pkey
>> when MySQL has finished the insert. There's very seldom a good reason to
>> make a primary key contain any information aside from the row's own
>> identity.
>
> There *IS* a good reason to predetermine (from user input) a unique
> index being used as a user name, "handle", email address (assuming
> this site provides one using a single fixed domain name) or whatever.
> It makes the site more user-friendly and personal. This probably
> isn't a primary key. As you said, an autoincrement primary key is
> a good idea. But the user name, "handle" or whatever still needs
> to be unique and there's good reason to allow the user to choose
> one. It also probably deserves an index, since logging in will
> probably use this field rather than the primary key, and if users
> can refer to each other via the user interface, they will probably
> use this field. So I expect a lot of lookups on it.
>
>

If it's selected by the user, no problem. Create a unique index on the
column and attempt to insert the row. If you get an error, tell the
user their requested id is already in use.

But this wouldn't necessarily be the primary key. In fact, in most
cases it would be better for performance reasons that it weren't (i.e.
used as a reference in a foreign key).

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Quite useful information!

Great appreciation to you all!