見出し画像
Photo by satoshi_st

MySQLの行ロックの動きを確認してみた

SHIFT Group 技術ブログ

はじめに

こんにちは、SHIFT の開発部門に所属しているKatayamaです。今期から転属になり、開発を担当していくことになりました。

現在、基本的な事から学ぶ研修中です。開発部門では新しく学ぶことがたくさんあり、それらを自身の振り返りアウトプットとして発信していけたらと思います。記事が溜まったら、noteのマガジンにもまとめる予定です。

今回はMySQLの動き（多重アクセスと行ロック）について学んだことをまとめてみたいと思います。

MySQLの特徴をつかむために色々試してみる

準備

docker-composeで作成する。

# docker-compose.yaml
version: "3.9"
services:
  mysql:
    image: mysql:5.7.35
    container_name: mysql
    environment:
      MYSQL_ROOT_PASSWORD: ""
      MYSQL_ALLOW_EMPTY_PASSWORD: "yes"
    volumes:
      - ./data/mysql:/var/lib/mysql
      - ./sql:/docker-entrypoint-initdb.d
    ports:
      - 3306:3306

-- .sql/schema.sql
DROP DATABASE IF EXISTS `test`;
CREATE DATABASE IF NOT EXISTS `test`;
USE `test`;

DROP TABLE IF EXISTS `hoge`;
CREATE TABLE `hoge` (
  `id` int unsigned NOT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `cnt` int,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

※今回はパスワードなしにしているが本来は非推奨なので注意。

※dataフォルダが作成された後は、sqlフォルダ内のSQLは実行されないので注意（私はテストでdocker-compose.yamlのenvironmentでMYSQL_DATABASEを指定してデータベースを作成後、dataフォルダを残しておたらsqlフォルダの内容が反映されず？？になった・・・）。

・参考：Environment Variables

雑なアクセスカウンタを作成してインクリメントする

まずは多重アクセスの動きを見る準備として、何も考えずにSELECTしてインクリメントしそれをUPDATEで更新するコードを作成する。また、そのテストも作成すると以下のようになる。

const incrementSql = async (connection, id) => {
	try {
		await connection.beginTransaction();

		const [rowsHoge] = await connection.execute('SELECT `cnt` FROM `hoge` WHERE `id` = ?', [id]);
		const rowHoge = rowsHoge.shift();

		await connection.execute('UPDATE `hoge` SET `cnt` = ? WHERE `id` = ?', [rowHoge.cnt + 1, id]);

		await connection.commit();
		return 'OK';
	} catch (error) {
		await connection.rollback();
		return error.message;
	}
};

const getIdMsl = async (connection, id) => {
	try {
		const [rowsHoge] = await connection.execute('SELECT * FROM `hoge` WHERE `id` = ?', [id]);

		return size(rowsHoge) ? rowsHoge.shift() : {};
	} catch (error) {
		return { msg: error.message };
	}
};

// step6.test.js
import mysql from 'mysql2/promise';
import { incrementSql, getIdMsl } from '../src/index';

describe('雑なアクセスカウンタを作成してインクリメントする', () => {
	const id = 1;
	let expCnt;
	let connection;

	beforeAll(async () => {
		connection = await mysql.createConnection({
			host: '192.168.56.2',
			user: 'root',
			database: 'test'
		});
	});

	afterAll(async () => {
		await connection.destroy();
	});

	describe('Set Up', () => {
		test('generate expect cnt', async () => {
			const res = await getIdMsl(connection, id);
			expCnt = res.cnt + 1;
		});
	});

	describe('Test Block', () => {
		test('insert data with transaction', async () => {
			const res = await incrementSql(connection, id);
			expect(res).toEqual('OK');
		});

		test('confirm result', async () => {
			const res = await getIdMsl(connection, id);
			expect(res.cnt).toBe(expCnt);
		});
	});
});

これはまあ普通にテストも成功する。

※execute()で上記のようにしているのはsupport key value object place holderを参考にしたため。

雑なアクセスカウンタを1000回実行して多重アクセスが期待通りにならない事を確認

インクリメントをする部分は雑なアクセスカウンタを作成してインクリメントすると同じで、多重アクセスするためのテストコードを以下のように実装した。

// step7.test.js
// 省略

describe('雑なアクセスカウンタを1000回実行して多重アクセスが期待通りにならない事を確認', () => {
	const id = 1;
	// 省略

	describe('Test Block', () => {
		test('insert data with transaction', async () => {
			for (let index = 0; index < 1000; index += 1) {
				// eslint-disable-next-line no-await-in-loop
				const res = await incrementSql(connection, id);
				expect(res).toEqual('OK');
			}
		});
	})
});

実際に（多重アクセスなので）2つのターミナル上で上記のテストを実行してその後にSELECTしてみると、、、
（実行前のcntは0に設定しているので、期待値としては2000）。

## ターミナルA
# yarn test
yarn run v1.22.15
$ npx jest --maxWorkers=1 step7.test.js
 PASS  tests/step7.test.js (23.501 s)
  雑なアクセスカウンタを1000回実行して多重アクセスが期待通りにならない事を確認
    省略

## ターミナルBの実行が終わったのを確認してから・・・
# docker container exec -it mysql mysql -u root
mysql> USE `test`;
mysql> SELECT * FROM `hoge` WHERE `id` = 1;
+----+---------------------+------+
| id | updated_at          | cnt  |
+----+---------------------+------+
|  1 | 2021-10-15 05:17:51 | 1009 |
+----+---------------------+------+
1 row in set (0.00 sec)

## ターミナルB
# yarn test
yarn run v1.22.15
$ npx jest --maxWorkers=1 step7.test.js
 PASS  tests/step7.test.js (24.224 s)
  雑なアクセスカウンタを1000回実行して多重アクセスが期待通りにならない事を確認
    省略

という結果の通り、トランザクションを張っていてもターミナルAがSELECTしてインクリメント（UPDATE）する前にターミナルBの方がSELECTしてしまう事ができるので、期待値の2000にならない。
期待している動きは、ターミナルAがインクリメント（UPDATE）するまではターミナルBはSELECTを実行しない、という動き。そうならないとターミナルAでインクリメントしたのに、ターミナルBがターミナルAのインクリメント前の値をインクリメントするので+2になる所が+1になりずれが生じる。

トランザクションとSELECT ... FOR UPDATEを使った行ロックを1000回実行して多重アクセスが期待通りになる事を確認

テストコードについては雑なアクセスカウンタを1000回実行して多重アクセスが期待通りにならない事を確認と同じものを使って今度は以下のコードを実行してみる。
以下のコードで雑なアクセスカウンタのコードから変更している点は、SELECT→SELECT ... FOR UPDATEになっている部分のみ。

const incrementSqlByForUp = async (connection, id) => {
	try {
		await connection.beginTransaction();

		const [rowsHoge] = await connection.execute(
			'SELECT `cnt` FROM `hoge` WHERE `id` = ? FOR UPDATE',
			[id]
		);
		const rowHoge = rowsHoge.shift();

		await connection.execute('UPDATE `hoge` SET `cnt` = ? WHERE `id` = ?', [rowHoge.cnt + 1, id]);

		await connection.commit();
		return 'OK';
	} catch (error) {
		await connection.rollback();
		return error.message;
	}
};

上記の実装のように、SELECT ... FOR UPDATEを使って実装する事で、最新データを読み取った後はその読み取った行上に排他ロックを設定し、他のプロセスから読み取りされないようにできる。

そのため、実際に2つのターミナル上で上記のテストを実行してその後にSELECTしてみると、、、
（今回も初期値は0でやったので期待値は2000）。

## ターミナルA
# yarn test
yarn run v1.22.15
$ npx jest --maxWorkers=1 step8.test.js
 PASS  tests/step8.test.js (33.483 s)
  アクセスカウンタを1000回実行して多重アクセスが期待通りになる事を確認

## ターミナルBの実行が終わったのを確認してから・・・
# docker container exec -it mysql mysql -u root
mysql> USE `test`;
mysql> SELECT * FROM `hoge` WHERE `id` = 1;
+----+---------------------+------+
| id | updated_at          | cnt  |
+----+---------------------+------+
|  1 | 2021-10-15 05:50:50 | 2000 |
+----+---------------------+------+
1 row in set (0.00 sec)

## ターミナルB
# yarn test
yarn run v1.22.15
$ npx jest --maxWorkers=1 step8.test.js
 PASS  tests/step8.test.js (34.864 s)
  アクセスカウンタを1000回実行して多重アクセスが期待通りになる事を確認

という結果の通り、トランザクションと行ロックの仕組みにより期待値の2000になった。

※何でロックするのか？で挙動が変わるのでそこは注意が必要（【MySQL】InnoDBの select for update のロックの動作を確認する）。

まとめとして

多重アクセスの制御（排他制御）は基本的な部分だがここを適切に理解して設計・実装しないと思わぬ動きになってしまう事が体感できた。テーブルが増えた時や処理が複雑化してきたときに適切な設計・実装ができるように今後も学習していきたい。

おまけ

MySQLの処理速度

MySQLの処理速度を見てみると、、、

describe('Test Block', () => {
		test('insert data with transaction', async () => {
			console.time('loop time');
			for (let index = 0; index < 1000; index += 1) {
				// eslint-disable-next-line no-await-in-loop
				const res = await incrementSqlByForUp(connection, id);
				expect(res).toEqual('OK');
			}
			console.timeEnd('loop time');
		});
	});

# yarn test
yarn run v1.22.15
$ npx jest --maxWorkers=1 step8.test.js
  console.time
    loop time: 11146 ms

      at Object.<anonymous> (tests/step8.test.js:30:12)
          at runMicrotasks (<anonymous>)

 PASS  tests/step8.test.js (12.35 s)
  省略


★Katayama Yutaの記事

＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿＿

執筆者プロフィール：Katayama Yuta
SaaS ERPパッケージベンダーにて開発を2年経験。 SHIFTでは、GUIテストの自動化やUnitテストの実装などテスト関係の案件に従事したり、DevOpsの一環でCICD導入支援をする案件にも従事。 最近開発部門へ移動し、再び開発エンジニアに。座学で読み物を読むより、色々手を動かして試したり学んだりするのが好きなタイプ。

お問合せはお気軽に
https://service.shiftinc.jp/contact/

SHIFTについて（コーポレートサイト）
https://www.shiftinc.jp/

SHIFTのサービスについて（サービスサイト）
https://service.shiftinc.jp/

SHIFTの導入事例
https://service.shiftinc.jp/case/

お役立ち資料はこちら
https://service.shiftinc.jp/resources/

SHIFTの採用情報はこちら
https://recruit.shiftinc.jp/career/


最後まで読んでくださり、ありがとうございます！

御社サービスのご相談はお気軽に。
ソフトウェア品質のプロがお伺いします。