Advanced Features—Database Lock Operations

The database is a shared resource used by multiple users. When multiple users access data concurrently, multiple transactions will access the same data at the same time in the database. If concurrent operations are not controlled, Control may cause incorrect reading and storage of data, destroying the data consistency of the database. Therefore, locking is a very important technology to achieve database concurrency control;

Database locking The process is: before a transaction operates on a data object, it first sends a request to the system to lock it. The locked transaction has certain control over the data object. Before the transaction releases the lock, other Transactions cannot update this data object;

Therefore, the JDBC module integrates the control capability for database record locks in database query operations, called IDBLocker, which can be used in the same way as parameters So simple!

First understand the types of locks provided by IDBLocker:

• MySQL:

  IDBLocker.MYSQL: Row-level locks, only those that meet the conditions The data is locked, and other processes wait for the resource to be unlocked before operating;

• Oracle:

  IDBLocker.ORACLE: row-level lock, Only data that meets the conditions is locked, and other processes wait for the resource to be unlocked before operating;

  IDBLocker.ORACLE_NOWAIT: row-level lock, no resource waiting, as long as some data in the result set is found to be locked, return immediately "ORA-00054 error";

• SQL Server:

  IDBLocker.SQLSERVER_NOLOCK: No locking, no locking when reading or modifying data Add any lock;

  IDBLocker.SQLSERVER_HOLDLOCK: Hold the lock, keep this shared lock until the end of the entire transaction, and will not release it on the way;

  IDBLocker.SQLSERVER_UPDLOCK: Modify the lock, which can ensure multiple Two processes can read data at the same time but only this process can modify the data;

  IDBLocker.SQLSERVER_TABLOCK: table lock, the entire table sets a shared lock until the end of the command, ensuring that other processes can only read but not modify the data;

  IDBLocker.SQLSERVER_PAGLOCK: page lock;

  IDBLocker.SQLSERVER_TABLOCKX: exclusive table lock, which will set an exclusive lock on the entire table to prevent other processes from reading or modifying the data in the table;

• Other databases:

  can be implemented through the IDBLocker interface;

below Demonstrate how to use locks through sample code:

Sample code one: Pass lock parameters through the EntitySQL object;

session.find(EntitySQL.create(User.class)
        .field(Fields.create(User.FIELDS.ID, User.FIELDS.USER_NAME).excluded(true))
        .forUpdate(IDBLocker.MYSQL));

Sample code two: Through Select Query object passes lock parameters;

Select _select = Select.create(User.class, "u")
        .field("u", "username").field("ue", "money")
        .where(Where.create(
                Cond.create().eq(User.FIELDS.ID).param("bc19f5645aa9438089c5e9954e5f1ac5")))
        .forUpdate(IDBLocker.MYSQL);

session.find(SQL.create(_select), IResultSetHandler.ARRAY);

Sample code three: Pass lock parameters based on data entity object

//
User _user = new User();
_user.setId("bc19f5645aa9438089c5e9954e5f1ac5");
//
_user.load(IDBLocker.MYSQL);

//
User _user = new User();
_user.setUsername("suninformation");
_user.setPwd(DigestUtils.md5Hex("123456"));
//
IResultSet<User> _users = _user.find(IDBLocker.MYSQL);

Note:

Please use the database lock mechanism with caution and try to avoid locking the table to avoid deadlock!