Shulou(Shulou.com)06/01 Report--

Show definition ID

After the self-increment ID defined by the table reaches the upper limit, the value obtained remains the same when the next ID is applied.

-- (2 ^ 32-1) = 4294967295-BIGINT UNSIGNEDCREATE TABLE t (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY) AUTO_INCREMENT=4294967295;INSERT INTO t VALUES (null) is recommended;-- AUTO_INCREMENT does not change mysql > SHOW CREATE TABLE t +-+-+ | Table | Create Table | +-+- -+ | t | CREATE TABLE `t` (`id` int (10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY (`id`) ENGINE=InnoDB AUTO_INCREMENT=4294967295 DEFAULT CHARSET=utf8 | +-+ mysql > INSERT INTO t VALUES (null) ERROR 1062 (23000): Duplicate entry '4294967295' for key' PRIMARY'

InnoDB row_id

1. If you create an InnoDB table that does not specify a primary key, InnoDB creates an invisible row_id with a length of 6 Bytes

2. InnoDB maintains a global dict_sys.row_ id value. For all InnoDB tables without primary keys, each row of data is inserted

All take the current dict_sys.row_ id value as the row_id to insert the data, and then take the value of dict_sys.row_id + 1

3. In code implementation, row_id is an 8-Bytes BIGINT UNSIGNED.

However, when InnoDB is designed, only 6 Bytes of space is reserved for row_id. When writing to the data table, it will only store the last range of values of 6 Bytesrow_id: when 0 ~ 2 ^ 48-1 reaches the upper limit, the next value is 0.

4. In InnoDB, after applying for a row_id=N, write this row of data to the table

If there are already rows of row_id=N in the table, the newly written row will overwrite the original row

5. It is recommended to show the creation of a self-increasing primary key

After the ID of the table reaches the upper limit, the error of primary key conflict will be reported when inserting data, which affects availability and overwrites data, which means data loss and affects reliability. Generally speaking, reliability is better than availability.

XID

1. When redolog and binlog work together, there is a common field XID, which corresponds to a transaction.

2. Generation logic

MySQL maintains a global variable global_query_id assigns global_query_id to Query_id every time a statement is executed, and then global_query_id+1 assigns Query_id to the transaction's XID if the current statement is the first statement executed by the transaction

3. Global_query_id is a pure memory variable, which is cleared after restart.

Therefore, in the same database instance, the XID of different transactions may also be the same MySQL. After the restart of the binlog, a new binlog guarantee will be generated: when the XID is the only global_query_id in the same binlog file, it will continue to count from zero, so in theory, the same XID will still appear in the same binlog, but the probability is very low.

4. Global_query_id is 8 Bytes, and the upper limit is 2 ^ 64-1.

Execute a transaction, assuming that XID is An and then execute 2 ^ 64 query statements, let global_query_id go back to An and start another transaction, and the XID of this transaction is also A.

InnoDB trx_id

1. XID is maintained by the Server layer

2. Trx_id is used internally in InnoDB in order to correlate between InnoDB transactions and Server layers.

3. InnoDB maintains a global variable of max_trx_id internally.

Every time you need to apply for a new trx_id, you get the current value of max_trx_id, and then max_trx_id+1

4. The core idea of InnoDB data visibility.

Each row of data records the trx_id that updates it. When a transaction reads a row of data, the consistency view of the transaction that determines the visibility of the data is compared with the trx_id of the row.

5. For a transaction that is executing, you can see the trx_id of the transaction through information_schema.innodb_trx

Operation sequence

Session Asession BT1BEGIN;SELECT * FROM t LIMIT 1 * T2USE information_schema;SELECT trx_id,trx_mysql_thread_id FROM innodb_trx;T3INSERT INTO t VALUES (null); T4SELECT trx_id,trx_mysql_thread_id FROM innodb_trx;-- T2 mysql > SELECT trx_id,trx_mysql_thread_id FROM innodb_trx +-+-+ | trx_id | trx_mysql_thread_id | +-+-+ | 281479812572992 | 30 | +- -+-- T4 moment mysql > SELECT trx_id Trx_mysql_thread_id FROM innodb_trx +-+-+ | trx_id | trx_mysql_thread_id | +-+-+ | 7417540 | 30 | +- -+ mysql > SHOW PROCESSLIST +-+-+ | Id | User | Host | db | Command | Time | State | Info | +-+ -+-+ | 4 | event_scheduler | localhost | NULL | Daemon | 344051 | Waiting on empty queue | NULL | | 30 | root | localhost | test | Sleep | | NULL | 31 | root | localhost | information_schema | Query | 0 | starting | SHOW PROCESSLIST | +-+ -+

1. Trx_mysql_thread_id=30 is the thread ID, that is, the thread where session An is located.

2. At T1, the value of trx_id is actually 0, and a large value is just for display (different from ordinary read and write transactions).

3. At T2, trx_id is a large number, because at T1, session A does not involve update operations and is a read-only transaction.

For read-only transactions, InnoDB does not allocate trx_id

4. When session An executes the INSERT statement at T3, InnoDB really allocates trx_id.

Read-only transaction

1. In the T2 moment above, a large trx_id is temporarily calculated by the system.

Convert the pointer address of the trx variable of the current transaction to an integer, plus 2 ^ 48

2. during the execution of the same read-only transaction, its pointer address will not change.

Whether in innodb_trx or innodb_locks table, the trx_id checked out by the same read-only transaction is the same

3. If there are multiple parallel read-only transactions, the pointer address of the trx variable of each transaction must be different

The trx_id is different for different concurrent read-only transactions.

4. The purpose of adding 2 ^ 48: to ensure that read-only transactions display a large trx_ id value, which is used to distinguish ordinary read-write transactions.

5. The logic of trx_id is similar to that of row_id, with a defined length of 8 Bytes.

In theory, it is possible that a read-write transaction is the same as the trx_id displayed by a read-only transaction, but the probability is extremely low, and there is no real harm.

6. Read-only transactions do not allocate the benefits of trx_id

You can reduce the size of the active array in the transaction view. The currently running read-only transaction does not affect the visibility judgment of the data. Therefore, when creating a consistent view of the transaction, you only need to copy the trx_id of the read-write transaction to reduce the number of trx_id applications. In InnoDB, even if only an ordinary SELECT statement is executed, it should correspond to a read-only transaction if the ordinary query statement does not apply for trx_id. The lock conflicts of concurrent transaction request trx_id can be greatly reduced because the increase rate of read-only transaction without allocating trx_id,trx_id will be slower.

7. Max_trx_id will persist the storage, and the restart will not be reset to 0, but will not be reset to 0 until the upper limit of 2 ^ 48-1 is reached.

Thread_id

1. The first column of SHOW PROCESSLIST is thread_id

2. The system saves an environment variable thread_id_counter.

Each time a new connection is created, the thread_id_counter is assigned to the thread variable of the new connection

3. Thread_id_counter is defined as 4 Bytes, so it will be reset to 0 when 2 ^ 32-1 is reached.

But you won't see two identical thread_id in SHOW PROCESSLIST because MySQL designs the logic of a unique array, assigning thread_id to the new thread, as follows: do {new_id= thread_id_counter++;} while (! thread_ids.insert_unique (new_id) .second)

references

"45 lectures on MySQL practice"

Summary

The above is the whole content of this article. I hope the content of this article has a certain reference and learning value for everyone's study or work. Thank you for your support.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.