Node.js+Sequelize连接MySQL：公司库连接睡眠锁表求助

背景 #

本人有多年编程经验，但Node.js及数据库连接领域是新手。当前开发基于Node.js+Sequelize+MySQL的管理平台，项目已有部分功能正常运行。平台为每个客户提供独立数据库（结构一致），默认连接当前登录客户的数据库；部分业务流程需将数据写入公司自有数据库，该库有独立访问凭证。

所有数据库凭证加密存储在.env文件中，后端读取解密后，在Node.js启动阶段初始化两个数据库连接。前端请求遵循Controller→Service→Repository流程，初始化数据库连接实例并执行查询。客户数据库的所有连接均正常，查询完成后连接会自动释放；但公司数据库执行仅更新日期字段的UPDATE操作后，连接会停留在Sleep状态，导致对应表被锁定，这是不允许的。

已查阅Sequelize文档，未找到有效解决方法，根据文档判断连接、实例等配置均正确。

相关代码实现 #

客户数据库加载器（公司库加载器结构类似，仅凭证不同） #

export class DbClientLoader {
  private static _instance: DbClientLoader;

  public database;

  private constructor() {
    this.initDb();
  }

  /**
   * Initialize the connection pool to the db
   */
  private initDb() {
    const { Sequelize } = require('sequelize');

    this.database = new Sequelize(
      process.env.BD_CLIENT_SCHEMA,
      process.env.BD_CLIENT_USER,
      process.env.BD_CLIENT_PASSWORD,
      {
        host: process.env.BD_CLIENT_HOST,
        dialect: 'mysql',
        pool: {
          max: +process.env.BD_MAX_CONNECTIONS, // connection pool size
          min: 0,
          acquire: 30000,
          idle: 10000,
        },
        timezone: 'SYSTEM',
        logging: false,
        dialectOptions: {
          timezone: 'local',
          decimalNumbers: true,
          dateStrings: true,
          typeCast: function (field, next) {
            // for reading from database
            if (field.type === 'DATETIME') {
              return field.string();
            }
            return next();
          },
        },
      }
    );
  }

  /**
   * Singleton loader
   */
  public static get Instance() {
    return this._instance || (this._instance = new this());
  }
}

export const DbClientLoaderInst = DbClientLoader.Instance;

数据库加载器（Node.js启动时调用，所有连接验证均成功） #

export class DbLoader {
  public static async loadAll() {

    // Sets connection with the client database
    const DbClientLoader = require('./DbClientLoader');
    const dbClient = DbClientLoader.DbClientLoaderInst.database;

    try {
      await dbClient.authenticate();
      console.log('Connection established with the client database.');
    } catch (error) {
      console.error('(!) ERROR: Connection with the client database failed:');
      console.error('> ', error);
    }

    // Sets connection with the company database
    const DbCompanyLoader = require('./DbCompanyLoader');
    const dbCompany = DbCompanyLoader.DbCompanyLoaderInst.database;

    try {
      await dbCompany.authenticate();
      console.log('Connection established with the company database.');
    } catch (error) {
      console.error('(!) ERROR: Connection with the company database failed:');
      console.error('> ', error);
    }

  }
}

数据库ID枚举文件 #

export enum DB_ID {
  CLIENT = 'client',
  COMPANY = 'company',
}

数据库工具类文件 #

import { Sequelize } from 'sequelize';
import { DbClientLoader } from '../loaders/DbClientLoader';
import { DbCompanyLoader } from '../loaders/DbCompanyLoader';

export class dbUtils {
  /**
   * Gets the connection pool for the client database
   */
  static getClientDB(): Sequelize {
    return DbClientLoader.Instance.database;
  }

  /**
   * Gets the connection pool for the company database
   */
  static getCompanyDB(): Sequelize {
    return DbCompanyLoader.Instance.database;
  }
}

通用仓库基类文件 #

import cls from 'continuation-local-storage';
import { Transaction } from 'sequelize';

import { dbUtils } from '../dbUtils';
import { DB_ID } from './DB_ID.enum';

import { Base_Repository_Interface } from './Base_Repository.interface';

/**
 * Generic implementation of a repository
 */
export class Base_Repository implements Base_Repository_Interface {
  protected DBID: DB_ID;

  constructor(dbid: DB_ID) {
    this.DBID = dbid;
  }

  async getTransaction(): Promise<Transaction> {
    const session = cls.getNamespace('managersoft');
    let transation;

    switch (this.DBID) {

      case DB_ID.CLIENT:
        transation = session.get('transaction_client');

        if (!transation) {
          transation = await this.getConnection().transaction();
          session.set('transaction_client', transation);
        }
      break;

      case DB_ID.COMPANY:
        transation = session.get('transaction_company');

        if (!transation) {
          transation = await this.getConnection().transaction();
          session.set('transaction_company', transation);
        }
      break;
    }

    return transation as Transaction;
  }

  public getConnection() {
    switch (this.DBID) {

      case DB_ID.CLIENT:
        return dbUtils.getClientDB();
      break;

      case DB_ID.COMPANY:
        return dbUtils.getCompanyDB();
      break;
    }
  }
}

业务代码示例 #

User_Service（调用User_Repository和Company_Service） #

import { provide, inject } from 'inversify';
// custom services
import { DIUser_Service, User_Service_Interface } from './User_Service.interface';
import { DICompany_Service, Company_Service_Interface } from './Company_Service.interface';

// custom repositories
import { DIUser_Repository, User_Repository_Interface } from '../repositories/User_Repository.interface';

@provide(DIUser_Service)
export class User_Service implements User_Service_Interface {
  constructor(
    // custom services
    @inject(DICompany_Service) private companyService: Company_Service_Interface,
    // custom repositories
    @inject(DIUser_Repository) private userRepository: User_Repository_Interface,
  ) {}

  public async getUserData(params) {
    ...
  }

  public async setUserData(params) {
    const upsert = await this.userRepository.set({
      id: params.userid,
      name: params.name,
      nickname: params.nickname,
      birthdate: params.birthdate,
    });

    if (upsert.inserted > 0 || upsert.updated > 0) {
      this.companyService.update({
        what: 'userstats',
        id: params.userid,
      });
    }

    return upsert;
  }
}

User_Repository（被User_Service调用） #

import { provide } from 'inversify';
import { Sequelize } from 'sequelize';

import { DB_ID } from './DB_ID.enum';
import { Base_Repository } from './Base_Repository';
import { User_Repository_Interface, DIUser_Repository } from './User_Repository.interface';

@provide(DIUser_Repository)
export class User_Repository extends Base_Repository implements User_Repository_Interface {
  constructor() {
    super(DB_ID.CLIENT);
  }

  async get(params) {
    ...
  }

  async set(params) {
    /*
      QUERIES
    */
    const sql = {
      users: {
        INSERT: `INSERT INTO users (__FIELDS__)
                 VALUES (__PLACEHOLDERS__)`,
        UPDATE: `UPDATE users
                 SET __PLACEHOLDERS__
                 WHERE id = :id`,
      },
      ...
      params: {
        type: params.id > 0 ? 'UPDATE' : 'INSERT',
        transaction: await this.getTransaction(),
        replacements: {},
      },
      ready:  '',
    }



    /*
      SETUP READY QUERY AND PARAMS
    */
    switch (sql.params.type) {

      case 'INSERT':
        ...
      break;

      case 'UPDATE':
        // prepare placeholders and params
        let __PLACEHOLDERS__ = '';
        ...

        sql.ready = sql.users.UPDATE.replace('__PLACEHOLDERS__', __PLACEHOLDERS__);
      break;
    }

    /*
      EXECUTE QUERY
    */
    return await super.getConnection()
                      .query(sql.ready, sql.params)
                      .then((data) => {
                        // data<array>
                        //  [0] => last id (on INSERT)
                        //  [1] => affected rows
                        return { id: data[0], upserted: data[1] }
                      })
                      .catch((err) => {
                        console.error(err);
                      });
  }
}

Company_Service（调用Company_Repository，被User_Service调用） #

import { provide, inject } from 'inversify';
// custom services
import { DICompany_Service, Company_Service_Interface } from './Company_Service.interface';

// custom repositories
import { Company_Repository_Interface, DICompany_Repository } from '../repositories/Company_Repository.interface';

@provide(DICompany_Service)
export class Company_Service implements Company_Service_Interface {

  constructor(
    @inject(DICompany_Repository) private companyRepository: Company_Repository_Interface,
  ) {}

  public async update(params) {
    const upsert = await this.companyRepository.set(params);

    return upsert;
  }
}

Company_Repository（被Company_Service调用） #

import { provide } from 'inversify';
import { Sequelize } from 'sequelize';

import { DB_ID } from './DB_ID.enum';
import { Base_Repository } from './Base_Repository';
import { Company_Repository_Interface, DICompany_Repository } from './Company_Repository.interface';

@provide(DICompany_Repository)
export class Company_Repository extends Base_Repository implements Company_Repository_Interface {
  constructor() {
    super(DB_ID.COMPANY);
  }

  async get(params) {
    ...
  }

  async set(params) {
    /*
      QUERIES
    */
    const sql = {
      userstats: {
        INSERT: `INSERT INTO userstats (id, created, lastupdate)
                 VALUES (id, NOW(), NULL)`,
        UPDATE: `UPDATE userstats
                 SET lastupdate = NOW()
                 WHERE id = :id`,
      },
      ...
      params: {
        type: params.id > 0 ? 'UPDATE' : 'INSERT',
        transaction: await this.getTransaction(),
        replacements: {},
      },
      ready:  '',
    }

    /*
      SETUP READY QUERY AND PARAMS
    */
    sql.ready = sql.userstats[sql.params.type];
    sql.params.replacements.id = params.id;

    /*
      EXECUTE QUERY
    */
    return await super.getConnection()
                      .query(sql.ready, sql.params)
                      .then((data) => {
                        // data<array>
                        //  [0] => last id (on INSERT)
                        //  [1] => affected rows
                        return { id: data[0], upserted: data[1] }
                      })
                      .catch((err) => {
                        console.error(err);
                      });
  }
}

已尝试的解决方案 #

将Company_Service的调用从User_Service中移出，改为前端在用户更新成功后独立调用，但问题依旧：公司数据库表仍会被处于Sleep状态的连接锁定。

期望效果 #

公司数据库的UPDATE操作能正常完成事务，不会导致表被锁定，也不会让连接停留在Sleep状态。

内容的提问来源于stack exchange，提问作者Miguel Formigo