47

I'm making a web site where I would like to increment a counter in a standard MyISAM table.

Simplified example:

UPDATE votes SET num = num + 1;

Will this cause problems if multiple connections are doing the same query, or will MySQL take care of it and lock the table or something to make sure that there are no conflicts?

Newb
  • 513
  • 1
  • 4
  • 7
  • You might also be interested in my answer to another locking question: http://stackoverflow.com/questions/3312361/does-this-lock-the-database/3312790#3312790 – Mike Dec 05 '10 at 13:52
  • What about innodb? – Aidas Jan 05 '21 at 11:34

6 Answers6

30

The write is atomic but an increment also requires a read. So the question is: Are you sure the read is safe, in other words, are you sure another thread doing the increment will not end up with the same value to be incremented? I have doubts. The 100% correct way of doing this would be.

-- begin transaction here

select counter from myCounters where counter_id = 1 FOR UPDATE;

-- now the row is locked and nobody can read or modify its values

update myCounters set counter = ? where id = 1;

-- set ? to counter + 1 programmatically

commit; -- and unlock...
Rangi Lin
  • 9,303
  • 6
  • 45
  • 71
TraderJoeChicago
  • 6,205
  • 8
  • 50
  • 54
  • 3
    I'm fairly certain the OPs example of UPDATE SET field = field + 1 won't cause any concurrency problems, but this is the best way to assure you are locking the row/table while modifying a field. Typically makes more sense when you are doing a more complex operation though. – Nicholi May 09 '13 at 19:46
  • You are right, thanks. This very same example is mentioned in [MySQL Manual and the manual did exactly as you did.](https://dev.mysql.com/doc/refman/5.5/en/innodb-locking-reads.html) see the `UPDATE child_codes SET counter_field = counter_field + 1;` example – Accountant م Oct 19 '18 at 18:17
  • 7
    @Nicholi No you are wrong. This example of `UPDATE SET field = field + 1 ` is mentioned in [MySQL Manual](https://dev.mysql.com/doc/refman/5.5/en/innodb-locking-reads.html) and the manual says: **"Do not use either consistent read or a shared mode read to read the present value of the counter, because two users of the database could see the same value for the counter"** .You must lock the row before you update, just as this answer if you don't want very nasty bugs to appear on heavy load. – Accountant م Oct 19 '18 at 18:20
  • @Accountantم he is not wrong while it is exactly single UPDATE without any subqueries or other complexities. https://forums.mysql.com/read.php?25,401854,401992#msg-401992 – Alexo Po. Nov 01 '21 at 15:15
18

MyISAM tables use table level locking. This means that the whole table will be locked during the execution of your update query. So the answer for your simplified use case is: yes, this is thread safe. But this may not be the case if you use another storage engine or your update includes multiple tables.

Here is a quote from the MySQL manual for more clarity:

Table locking enables many sessions to read from a table at the same time, but if a session wants to write to a table, it must first get exclusive access. During the update, all other sessions that want to access this particular table must wait until the update is done.

You can also consider using auto increment columns, transactions or external synchronization if that fits to your design.

Cheers!

Lachezar Balev
  • 11,498
  • 9
  • 49
  • 72
8

Yes, the table (or rows in InnoDB format databases) is automatically locked when you execute an update query.

grahamparks
  • 16,130
  • 5
  • 49
  • 43
5

This form of UPDATE is atomic. Other forms of UPDATE can be made atomic by using transactions with SELECT ... FOR UPDATE.

Ignacio Vazquez-Abrams
  • 776,304
  • 153
  • 1,341
  • 1,358
2

Had the same issue, although query was more complicated:

UPDATE CLB_SYNC_T SET PENDING_MESSAGES = PENDING_MESSAGES + ? WHERE USER_ID = ?

Using MyISAM as a default engine did not help, so I fallback to SELECT FOR UPDATE use.

With SELECT FOR UPDATE performance improved ~ 10 times, since MySQL did not lock whole table, to make a row update.

mavarazy
  • 7,562
  • 1
  • 34
  • 60
0

Another approach when using InnoDB is using unique index on multiple column as follow:

Table 'Sessions' { unique_key(browser_session_id,profile_id) // ensures that inserting 1 entry per session will occur once }

select count(browser_session_id) from Sessions

Will guarantee result of unique sessions, as multiple sessions per user is not allowed.

Conclusions

  • Advantage

    Each insert does require a pre-select.

  • Disadvantage

    It is not suitable for all cases.

    May slow down write performance, and requires extra management

guykaplan
  • 145
  • 1
  • 3