ちょっと書いたんだけど、課題と全然違うことしてたことに気づいた。もったいないのでここに残しておく。推敲なしのメモです。英語も書いただけで直してない
How MySQL Shows Transaction Log
MySQL does not allow us to see the transaction log directly. However, we can see the summary of transaction status. First, I start a transaction and execute some queries.
mysql> START TRANSACTION;
mysql> SELECT * FROM Patients WHERE PatientID = 1;
mysql> UPDATE Patients SET Name = 'Updated User' WHERE PatientID = 1;
Before COMMIT, I can see the status of the transaction by the following command:
mysql> SHOW ENGINE INNODB STATUS \G;
The output includes the following lines:
------------
TRANSACTIONS
------------
Trx id counter 2015
Purge done for trx's n:o < 2009 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281480098585592, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
...
...
...
---TRANSACTION 2014, ACTIVE 33 sec
2 lock struct(s), heap size 1128, 1 row lock(s), undo log entries 1
MySQL thread id 17, OS thread handle 6330691584, query id 334 localhost root
Trx read view will not see trx with id >= 2014, sees < 2014
---
LOG
---
Log sequence number 20810214
Log buffer assigned up to 20810214
Log buffer completed up to 20810214
Log written up to 20810214
Log flushed up to 20810214
Added dirty pages up to 20810214
Pages flushed up to 20810214
Last checkpoint at 20810214
Log minimum file id is 6
Log maximum file id is 6
438 log i/o's done, 0.07 log i/o's/second
The TRANSACTIONS section indicates a transaction is active and it's holding 2 locks. Since COMMIT has not been executed, the undo log has one entry. The current checkpoint is 20810214 in the LOG section.
Let's close the transaction.
mysql> COMMIT;
After COMMIT, the transaction is closed and the transaction log is written to the disk. Here is the result after COMMIT. The checkpoint is updated to 20810529, and the transaction log is cleared.
------------
TRANSACTIONS
------------
Trx id counter 2016
Purge done for trx's n:o < 2016 undo n:o < 0 state: running but idle
History list length 1
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281480098585592, not started
...
...
...
---TRANSACTION 281480098580048, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---
LOG
---
Log sequence number 20810529
Log buffer assigned up to 20810529
Log buffer completed up to 20810529
Log written up to 20810529
Log flushed up to 20810529
Added dirty pages up to 20810529
Pages flushed up to 20810529
Last checkpoint at 20810529
Log minimum file id is 6
Log maximum file id is 6
442 log i/o's done, 0.17 log i/o's/second
The Trx id counter is increased when a transaction is started and increases again when a transaction is closed. This is the Transaction ID that Vidhya et al. mentioned in the text.
ここで気づいた。課題で求められてるのは全然違うことだった