First, install related packages

npm install access-db

npm install dotenv

Then introduce the require(‘dotenv’).config() new.env file at the front of the project entry file (e.g. App.js) and add mysql configuration.

  MYSQL_HOST=localhost   / / required
  MYSQL_USER=root
  MYSQL_PASSWORD=123456
  MYSQL_PORT=3306
  MYSQL_DATABASE=    // Specifies the database name
Copy the code

Transaction and lock ()

Asynchronous functions for transaction processing

let { run, begin, rollback, commit, locks } = await mysql.transaction()

parameter type mandatory instructions
begin Function is Transaction start function
commit Function is Transaction commit function
rollback Function no Rollback transaction functions
run Function is Execute SQL statement functions
locks Object no Locking type

Details of the locks

field value instructions
shared_locks ‘ lock in share mode’ Shared Locks (S Locks)
exclusive_locks ‘ for update’ Exclusive Locks (X Locks)

Begin () starts the transaction function, performs database operations within begin(), executes individual SQL statements by running (), and returns results. The rollback() function can be used to rollback SQL when an error is reported or SQL results are not met. Remember to commit the transaction when the execution is ok, that is, execute the commit() function.

If you need something like a seckill. You also need to lock the data by simply adding the type of locks to the return SQL statement.

Sample code:

import {mysql} from 'access-db'

/** mysql transactions and locks */
let {run, begin, rollback, commit, locks} = await mysql.transaction()

await begin(async() = > {try{
    // When we need to lock, we can add the lock after the SQL statement returned
    let sql1 = (await mysql.get('user'.10.'sentence')) + locks.exclusive_locks
    let sql2 = await mysql.update('user'.10, {money: ['incr', -3]}, 'sentence')
    let sql3 = await mysql.update('user'.12, {money: ['incr'.3]}, 'sentence')

    let res1 = await run(sql1)
    if(res1.data.money < 3) {return await rollback()  // Rollback the transaction
    }
    await run(sql2)
    await run(sql3)
    await commit()  // Commit the transaction
  }catch(err){
    await rollback()  // Rollback the transaction
    throw new Error(err)
  }
})
Copy the code