Menu
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?
765
Syntax for MySQLMySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature. By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record. VALUES ('Lars','Monsen'); The SQL statement above would insert a new record into the "Persons" table.
MySQL 8.0 supports atomic Data Definition Language (DDL) statements. This feature is referred to as atomic DDL. An atomic DDL statement combines the data dictionary updates, storage engine operations, and binary log writes associated with a DDL operation into a single, atomic operation.
A SELECT ... FOR UPDATE reads the latest available data, setting exclusive locks on each row it reads. Thus, it sets the same locks a searched SQL UPDATE would set on the rows.
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... If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
