CODEWITHSUNDEEP
mysqldeadlock
Bogdan Dubyk
Bogdan Dubyk

Reputation: 5540

MySql update each n-th rows without using module to avoid deadlock

I have a few queries which are running simultaneously, and each query should update own n-th row depends on the number of queries I need to run. For example, we need to run 5 queries so where clause should be where (id MOD :queries_amont) = :current_query_number

but in such case, I'm getting deadlock and I think it's because MySql is scanning all rows to find correct rows to update as there is no index to use.

Is it possible to balance row updating between queries running simultaneously? Or how to avoid deadlocks with current implementation?

Upvotes: 0

Views: 38

Answers (1)

DataVader
DataVader

Reputation: 780

In general: if you have to run some statements constantly without any event, your database design probably is very bad. More so if those statements take a long time. So you should consider changing it. To answer your question, you could use window functions to only scope for every n-th row.

Upvotes: 1

Related Questions