Strange thing happened to today, the database update or delete the execution often overtime, there is a problem of table involves different libraries of different tables, these libraries, no business connections between tables,
Found in common as follows:
1. The table id is on the field, 2. Basic discrete data somehow id field, such as 1,2,12,33,124,137... And ask for a moment, no one ever delete data
2. Timeout statement is very simple, is to update the table set col="XXX" where id=1, get the delete from the table where id=1, executed independently select * from table where id=1, can also be used to query data;
3. Call to show the processlist showing these statements are always updating, then the problem the whole TRUNCATE table, to generate data, update or delete again, can be normal operation, no longer stuck,
4. The database is 5.7, the innodb engine
This is when the show the processlist screenshot

Execution of the statement is: the UPDATE logintoken SET token='eyJ0eXAiOiJKV1QiLCJhbGciOiJERVMifQ==. EyJpZCI6MjcsImV4cCI6IjE1MDg3NDg4NjY0MDIiLCJsb2dpbk5hbWUiOiIxMzU3MTgzMDYyNSJ9. 7 b01517332f630d897893322f51e6a2d47d123d1ec73de38a2b4d7f3de732269cd3c6c3f23ae54064aec6d84f00adccf968da94ae4a07f0130ec872b4ebacd0b2687bd1d098989f7dcea03d59aa050d004e8ea3165075ff5162c247e4cd80a93d3e25039dd177c0dbf11599472fbadbeddc02afd1f0ef8fd, tokenExpiredTime=' 2017-11-22 16:54:26. 402 'WHERE Id=118

Table structure is:

 CREATE TABLE ` logintoken ` (
` id ` INT (11), NOT NULL AUTO_INCREMENT, 
` type ` CHAR (1) NOT NULL DEFAULT '0', 
` loginName ` VARCHAR (20) NULL DEFAULT NULL, 
` token ` VARCHAR (2000) NULL DEFAULT NULL, 
` tokenExpiredTime ` TIMESTAMP NULL DEFAULT NULL, 
PRIMARY KEY (` id `), 
The UNIQUE INDEX ` loginName ` (` loginName `, ` type `) USING BTREE 
) 
COMMENT='login Token' 
COLLATE='utf8mb4_general_ci' 
ENGINE=InnoDB 
AUTO_INCREMENT=15 
; 

Table is TRUNCATE, so there is a problem of have no way to capture data

CodePudding user response:

Discontinuous id because the only index, id after the conflict on the 1

Long updateing should be transaction caused by waiting for lock to lock up the corresponding id


You can look at the situation of the transaction the select * from information_schem. INNODB_TRX

CodePudding user response:

1, since the field ids discontinuity is normal, in the middle of the empty lot number, including the insert statements of the proportion of the transaction is not successful,
2, appear a lot of irrelevant statements update waiting, may be system malfunction, such as disk errors, or other errors, it is better to check the mysql error record

CodePudding user response:

Discontinuity is not, a lot of result, the design of the situation is not in continuous as the goal, don't tangle
Delete the questions, you can try the select, if you select the same slow, system problems should be considered, if only the update/delete slowly, then check the question of whether or not to lock, disconnect all processes, for example, in the case of no one else operation, if the operation is slow, so is the concurrency conflict

CodePudding user response:

reference 1st floor rucypli response:

discontinuous id because the only index, id after the conflict on the 1

Long updateing should be transaction caused by waiting for lock to lock up the corresponding id


You can look at the situation of the transaction the select * from information_schem. INNODB_TRX

INNODB_TRX do have records, kill trx_mysql_thread_id corresponding value,
But its trx_mysql_thread_id in the processlist list corresponding to the id, the command is sleep, and the state, the info two columns are empty

CodePudding user response:

reference ZJCXC reply: 3/f

discontinuous would say, a lot of result, the design of the situation is not in continuous as the goal, don't tangle
Delete the questions, you can try the select, if you select the same slow, system problems should be considered, if only the update/delete slowly, then check the question of whether or not to lock, disconnect all processes, for example, in the case of no one else operation, if the operation is slow, so conflict is concurrent

Select no problem, and should not, because the database is just clear, a total of less than 10 data, now still in the testing phase, 2 testers, innodb_trx does something, the department the processlist is sleep, and there are no SQL statement in the info

CodePudding user response:

Take out your statements separately performed to see if you also slow
If too slow, a PROFILE see
The SET PROFILING=1

CodePudding user response:

refer to 6th floor ZJCXC response:

whether you take out your statements separately perform also slow
If too slow, a PROFILE see
The SET PROFILING=1

There is a data block is the TRX in running, but the corresponding threads in the processlist table data is sleep, but the info column without SQL statements, what could I can see what is blocked?

CodePudding user response:

Check under information_schema innodb_locks, if there is no data, the only data in the TRX, so you need to have a PROFILE