B-Compatible Database Lock

To ensure database data consistency, you can execute the LOCK TABLES statement to prevent other users from modifying tables.

For example, an application needs to ensure that data in a table is not modified during transaction running. For this purpose, table usage can be locked. This prevents data from being concurrently modified.

After LOCK TABLES is used, the subsequent SQL statements are in the transaction state. Therefore, you need to run UNLOCK TABLES to manually release the lock and end the transaction.

In addition, if you want to make the current session read-only, you can use FLUSH TABLES WITH READ LOCK to implement this function. Then, you need to use UNLOCK TABLES to manually disable this function.

Syntax

• Lock.

  LOCK TABLES namelist READ/WRITE

• Make the current session read-only.

  FLUSH TABLES WITH READ LOCK

• Unlock.

  UNLOCK TABLES

Parameter Description

• namelist

  Name of the table to be locked. Multiple tables are allowed.

• READ/WRITE

  Lock mode. Values:

  • READ

    Tables can be read only.

  • WRITE

    The holder is the only transaction accessing the table in any way.

Examples

Obtains a WRITE lock on a table when going to perform a delete operation.

-- Create an example table.
MogDB=# CREATE TABLE graderecord  
             (  
             number INTEGER,  
             name CHAR(20),  
             class CHAR(20),  
             grade INTEGER
             );
-- Insert data.
MogDB=# insert into graderecord values('210101','Alan','21.01',92);  

-- Provide the example table.
MogDB=# LOCK TABLES graderecord WRITE;

-- Delete the example table.
MogDB=# DELETE FROM graderecord WHERE name ='Alan';

MogDB=# UNLOCK TABLES;