{question}
How do you execute row locking/transaction blocking mechanisms in SingleStore?
{question}
{answer}
We have an option to block transactions in the cluster using the FLUSH TABLES. Using this will complete all the in-progress DDL and DML transactions that are going on, and then for every table in the cluster, only READs are permitted, but not WRITEs.
UsingFLUSH TABLESwill lock the table for WRITEs. This is useful in situations like cluster failover. Once the required actions have been taken, UNLOCK TABLES, which releases all the locks on the tables, can be used.
Another possible locking mechanism that can be used in the SELECT ... FOR UPDATE statement. This command takes write locks on the rows returned from the SELECT query, and the locks are held until the end of the transaction. Other transactions are blocked and cannot write to the locked rows until the locks are released. To remove the locks on the rows, commit or roll back the open transaction. The example mentioned here can be used as a reference for implementing the SELECT ... FOR UPDATE statement.
Reference
{answer}