I have a table with two fields, VARCHAR playername and INT score. When
the player wins, I want to increase her score. The player might play
multiple games at the same time, so if I read the old score, increase it
and write it back, I run the risk of overwriting a concurrent update that
does the same thing.

This is a simplified example. In the real application I want to do
something more complex to the score, so a solution that only works for
doing simple arithmetic in a field won't work for me.

I can think of a few solutions to this problem, but they are all
cumbersome and prone to error. I can't help but feel that this must be a
very common problem that has a very common solution.

Thanks,
Dirk

> I have a table with two fields, VARCHAR playername and INT score. When
> the player wins, I want to increase her score. The player might play
> multiple games at the same time, so if I read the old score, increase it
> and write it back, I run the risk of overwriting a concurrent update that
> does the same thing.

Not if you do it in the same statement:

UPDATE yourtable SET score=score+1 WHERE playername='Some name';

Every single SQL statement is atomic, meaning that concurrency problems
do not occur within one statement.

Best regards,
--
Willem Bogaerts

Application smith
Kratz B.V.
http://www.kratz.nl/

On Fri, 28 Mar 2008 08:35:07 +0100, Willem Bogaerts wrote:

>> I have a table with two fields, VARCHAR playername and INT score. When
>> the player wins, I want to increase her score. The player might play
>> multiple games at the same time, so if I read the old score, increase
>> it and write it back, I run the risk of overwriting a concurrent update
>> that does the same thing.
>
> Not if you do it in the same statement:
>
> UPDATE yourtable SET score=score+1 WHERE playername='Some name';
>
> Every single SQL statement is atomic, meaning that concurrency problems
> do not occur within one statement.

What if my operation is more complicated than increasing a number? What
if instead of an INT score I have a BLOB image, and I want to do
operations to the image like rotate it, blur it or something else? In
short, what if my operation is something I can't do in SQL?

Dirk

Dirk Groeneveld wrote:
> On Fri, 28 Mar 2008 08:35:07 +0100, Willem Bogaerts wrote:
>
>>> I have a table with two fields, VARCHAR playername and INT score.
>>> When the player wins, I want to increase her score. The player
>>> might play multiple games at the same time, so if I read the old
>>> score, increase it and write it back, I run the risk of overwriting
>>> a concurrent update that does the same thing.
>>
>> Not if you do it in the same statement:
>>
>> UPDATE yourtable SET score=score+1 WHERE playername='Some name';
>>
>> Every single SQL statement is atomic, meaning that concurrency
>> problems do not occur within one statement.
>
> What if my operation is more complicated than increasing a number?
> What if instead of an INT score I have a BLOB image, and I want to do
> operations to the image like rotate it, blur it or something else? In
> short, what if my operation is something I can't do in SQL?
>
> Dirk

Then you ask a different question stating what the new requirements are.
Different problems have different solutions.

On Mar 28, 11:44 am, Dirk Groeneveld <groenev...@gmail.com> wrote:
> On Fri, 28 Mar 2008 08:35:07 +0100, Willem Bogaerts wrote:
> >> I have a table with two fields, VARCHAR playername and INT score. When
> >> the player wins, I want to increase her score. The player might play
> >> multiple games at the same time, so if I read the old score, increase
> >> it and write it back, I run the risk of overwriting a concurrent update
> >> that does the same thing.
>
> > Not if you do it in the same statement:
>
> > UPDATE yourtable SET score=score+1 WHERE playername='Some name';
>
> > Every single SQL statement is atomic, meaning that concurrency problems
> > do not occur within one statement.
>
> What if my operation is more complicated than increasing a number? What
> if instead of an INT score I have a BLOB image, and I want to do
> operations to the image like rotate it, blur it or something else? In
> short, what if my operation is something I can't do in SQL?

You may be able to take advantage of InnoDB lock modes, for example
SELECT ... FOR UPDATE. See:
http://dev.mysql.com/doc/refman/5.0/...ing-reads.html

>
> Dirk

On Fri, 28 Mar 2008 16:38:34 +0000, Paul Lautman wrote:
> Dirk Groeneveld wrote:
>> On Fri, 28 Mar 2008 08:35:07 +0100, Willem Bogaerts wrote:
>>>> I have a table with two fields, VARCHAR playername and INT score.
>>>> When the player wins, I want to increase her score. The player might
>>>> play multiple games at the same time, so if I read the old score,
>>>> increase it and write it back, I run the risk of overwriting a
>>>> concurrent update that does the same thing.
>>>
>>> Not if you do it in the same statement:
>>>
>>> UPDATE yourtable SET score=score+1 WHERE playername='Some name';
>>>
>>> Every single SQL statement is atomic, meaning that concurrency
>>> problems do not occur within one statement.
>>
>> What if my operation is more complicated than increasing a number? What
>> if instead of an INT score I have a BLOB image, and I want to do
>> operations to the image like rotate it, blur it or something else? In
>> short, what if my operation is something I can't do in SQL?
>
> Then you ask a different question stating what the new requirements are.
> Different problems have different solutions.

Remember the second paragraph of my original question? Here it is again,
for your benefit:

> This is a simplified example. In the real application I want to do
> something more complex to the score, so a solution that only works for
> doing simple arithmetic in a field won't work for me.