Updating a row at the same time

Question

Imagine I have a table with two columns that have a point register from two different teams, like the one below:

TABLE:

• first_team
• second_team
• first_team_points
• second_team_points

The table has the relation of the two teams and the points they get when they finish a level during a period of time.

The two teams can play a level when they want and update their points when they finish. So it is possible that they update their own column of points at the same time. Like, A team updates first_team_points and B team updates second_team_points.

I know that InnoDB has a row-level locking, so I suppose that in that case the two updates will be realized in a sequencial order.

Im I wrong? Do I need to configurate something? Will the second update request cause a deadlock?

Thanks in advance!

Answer 1

Please provide the code for critique. Meanwhile, in general...

BEGIN;   -- start the transaction
SELECT ... FOR UPDATE;  -- if you need to look at something before updating
...
INSERT/UPDATE/etc   -- make whatever changes
COMMIT;

There are several issues:

• You want data integrity; transactions help a lot.
• You want to avoid deadlocks -- without further details, I cannot assure that all deadlocks will be prevented. Be ready to re-do the transaction if you do get a deadlock.
• One connection could get a "lock_wait_timeout". Think of this is as a deadlock that can be resolved by having one of the contenders wait. But, if the other connection takes too long, you could timeout. This is usually avoidable by making things run faster. (50 seconds is the default wait time; it is rare to hit it.)