[Entrypoint] Initializing database 2025-04-21T04:21:50.528592Z 0 [System] [MY-015017] [Server] MySQL Server I nitialization - start. 2025-04-21T04:21:50.530156Z 0 [System] [MY-013169] [Server] /usr/sbin/mys qld (mysqld 8.4.5) initializing of server in progress as process 17 .. [Entrypoint] MySQL init process done. Ready for start up. .. 2025-04-21T04:21:59.400336Z 0 [System] [MY-010931] [Server] /usr/sbin/mys qld: ready for connections. Version: '8.4.5' socket: '/var/lib/mysql/mys ql.sock' port: 3306 MySQL Community Server - GPL. 6/77
INT PRIMARY KEY, val VARCHAR(32) NOT NULL, UNIQUE KE Y uidx_val(val)); INSERT INTO t1 (num, val) VALUES (1, 'one'); INSERT INTO t1 (num, val) VALUES (2, 'two'); INSERT INTO t1 (num, val) VALUES (3, 'three'); 8/77
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_option] ... create_option: [DEFAULT] { CHARACTER SET [=] charset_name | COLLATE [=] collation_name | ENCRYPTION [=] {'Y' | 'N'} } CREATE DATABASE creates a database with the given name. To use this statement, you need the CREATE privilege for the database. CREATE SCHEMA is a synonym for CREATE DATABASE. 12/77
normal_t1 JOIN t1 AS another_t1 ON normal_t1.num = another_t 1.num SET normal_t1.num = normal_t1.num + another_t1.num WHERE another_t1. num = 3; SELECT * FROM t1 ORDER BY num; +-----+-------+ | num | val | +-----+-------+ | 1 | one | | 2 | two | | 6 | three | +-----+-------+ 16/77
6; ### DELETE FROMの あとはテーブル名が来ないといけないのでシンタックスエラー ERROR 1064 (42000): You have an error in your SQL syntax; check the manual th at corresponds to your MySQL server version for the right syntax to use near ' IGNORE INDEX(PRIMARY) WHERE num = 6' at line 1 EXPLAIN DELETE t1 FROM t1 IGNORE INDEX(PRIMARY) WHERE num = 6; ### インデック スヒントを含んだテーブル指定は「テーブルリファレンス」 +----+-------------+-------+------------+------+---------------+------+------- --+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_l en | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+------- --+------+------+----------+-------------+ | 1 | DELETE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 50.00 | Using where | +----+-------------+-------+------------+------+---------------+------+------- --+------+------+----------+-------------+ 20/77
WHERE num = (SELECT * FROM (SELECT MAX(num) FROM t1) AS du mmy); SELECT * FROM t1 ORDER BY num; +-----+-----+ | num | val | +-----+-----+ | 1 | one | | 2 | two | +-----+-----+ 22/77
FOR UPDATE a. transaction_isolation = SERIALIZABLE b. INSERT は対応するユニークキーとPrimary Keyの値をロック UPDATE / DELETE は「WHERE句の解決に使ったインデックス」と対応するPrimary Keyの 値をロック ### ターミナル2 BEGIN; SELECT * FROM t1 ORDER BY num; ### 何もつけないSELECTはロックフリー +-----+-----+ | num | val | +-----+-----+ 24/77
FOR UPDATE a. transaction_isolation = SERIALIZABLE b. INSERT は対応するユニークキーとPrimary Keyの値をロック UPDATE / DELETE は「WHERE句の解決に使ったインデックス」と対応するPrimary Keyの 値をロック ### ターミナル2 SET SESSION innodb_lock_wait_timeout = 1; SELECT * FROM t1 WHERE num = 3 FOR UPDATE; ### num = 3はINSERTにロックされて いる INSERT INTO t1 VALUES (3, 'drei'); ### ↑同様 INSERT INTO t1 VALUES (6, 'six'); ### num = 6はロックされてないので 25/77
FOR UPDATE a. transaction_isolation = SERIALIZABLE b. INSERT は対応するユニークキーとPrimary Keyの値をロック UPDATE / DELETE は「WHERE句の解決に使ったインデックス」と対応するPrimary Keyの 値をロック ### ターミナル2 DELETE FROM t1 WHERE val = 'five'; ### val = 'five' に紐づく num = 5 がINSERTにロックされている UPDATE t1 SET val = 'five' WHERE num = 1; ### num = 1はロックされていないが v al = 'five' がロックされている UPDATE t1 SET val = 'eins' WHERE num = 1; ### num = 1もval = 'eins' もロッ クされていないので成功 ### ちなみにvalがユニークキーでない場合は2つ目のUPDATEが成功する 26/77