I have this scenario:
My php web application is deployed in a clustered environment, it uses a replicated mariadb database with galera.
It has to acquire an exclusive lock on database row resource and proceed with a process that has to be done abolutely once and from just one server.
I must be sure that in case of concurrent multiple requests coming to the same, or different, servers just one request will pass.
When the application was warking in a single server environmente this was the logic:
- There was a column in resource table with the process status, let say
processedwith default 0. 0=not processed, 1=already processed. - The isolation transaction level was set to
READ COMMITTED. - When the controller acquired the request, it entered in transaction with this query:
SELECT processed FROM mytable WHERE id=? FOR UPDATE. - If the query returned
processed=0, It safetly proceded with the process, updated the processes status to 1 and commited the transaction. Otherwise, if processed value is >0 it would have skipped the process.
I have tested many times this implementation and it worked. The concurrent requests will be stopped waiting for the READ LOCK on the id index and just the first one proceeded to the next step. The isolation level to READ COMMITTED will ensure to get the last updated value of the semaphore column.
The problems started with the replicated database environment.
It looks like the READ LOCK in replicated MariaDB is optimistic, it starts the transation without waiting the lock acquisition and it will check for collision only at commit time.
So if I have 2 concurrent transactions, both are not stopped at FOR UPDATE query and both will get 0 value for the processed semaphore field. At commit one will win and other will fail. But it is too late. Of course this is not acceptable in my case because I have already let run my unique process two times.
How can I safely change my logic to suit the replicated environment?
How can be implemented a patter of exclusive resource lock with a replicated db?