Increase same counter while concurrency

Question

I have about 500 threads with loop that concurrently works. I want to increases a counter on every loop.

Last line in loop:

update counter_tbl set counter=counter+1 where id = 5;

Is it possible that at least two thread pickup one value of counter and increases it to same value?

For example, counter is 50, two thread exactly pickup 50 at same time and increases it, so thread1 updates counter to 51 and thread2 that have 51, updates counter to 51 again. so we will miss one increment. Is this possible? If yes how to avoid it?

I can use LOCK TABLE but because of its cost I want avoid it.

Answer 1

Actually it is not possible, since these are update statements and would run in a transaction.

Locking tables is only necessary, if you have a transaction consisting of two ore more statements.

Answer 2

You do not have to use LOCK TABLE. Database servers are consistent per transaction. In this case, your query is one transaction and when two threads try to update the same record, the database server will handle the concurency. If it is possible, the transactions will be executed in arriving order (or any order determined by the server), if not, a deadlock will be occured (in this case, you can safely resend the transaction in your specific scenario!).