Does a field in a MySQL atom increase?

Question

I am creating a website where I would like to increase the counter in the standard MyISAM table.

A simplified example:

UPDATE votes SET num = num + 1; 

Will this cause problems if multiple connections make the same request or does MySQL take care of this and lock the table or something to make sure there are no conflicts?

Answer 1

MyISAM tables use table-level locking. This means that the entire table will be locked during the execution of your update request. So the answer for your simplified use case is: yes, it is thread safe. But this may not be the case if you use a different storage engine or your update includes several tables.

Here is a quote from the MySQL manual for clarity:

Locking tables allows many sessions to read from a table at the same time, but if a session wants to record a table, it must first get exclusive access. During the upgrade, all other sessions that want to access this particular table must wait for the upgrade to complete.

You might also consider using auto-increment columns, transactions, or external synchronization if it suits your design.

Hurrah!

Answer 2

The record is atomic, but the increment also requires reading. Therefore, the question arises: are you sure that reading is safe, in other words, are you sure that the other thread performing the increment will not have the same value that needs to be increased? I have doubts. A 100% correct way to do 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... 

Answer 3

Yes, a table (or rows in InnoDB databases) is automatically locked when an update request is executed.

Answer 4

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

Answer 5

Had the same problem, although the request was more complex:

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

Using MyISAM as the default mechanism did not help, so I am returning to SELECT FOR UPDATE .

WITH CHOICE FOR UPDATE, performance improved ~ 10 times since MySQL did not lock the entire table to update the row.

Answer 6

Another approach when using InnoDB uses a unique index for multiple columns as follows:

Sessions table {unique_key (browser_session_id, profile_id) // ensures that inserting 1 record into the session happens once}

select count (browser_session_id) from sessions

Ensures the result of unique sessions, since multiple sessions per user are not allowed.

findings

• Advantage

  Each insert requires prior selection.

• Inconvenience

  It is not suitable for all occasions.

  May slow down recording performance and requires additional control