Mysql InnoDB Data Update causes lock table

first, the data table structure

CREATE TABLE ' Jx_attach ' (  ' attach_id ' int (one) not null auto_increment,  ' feed_id ' int (one) DEFAULT NULL,  ' att Ach_name ' varchar (255) NOT NULL,  ' cycore_file_id ' varchar (255) DEFAULT null,    ' attach_size ' bigint (a) NOT null D Efault ' 0 ',  ' complete ' smallint (6) Not NULL DEFAULT ' 0 ',  PRIMARY key (' attach_id '),  key ' JX_TREND_ATTACH_FK ' (' feed_id ') USING BTREE) Engine=innodb auto_increment=394160 DEFAULT Charset=utf8;

Second, the phenomenon

When multiple connections update the data on a table at the same time, the speed will be slower and the data table will be locked for a period of time, affecting other queries and updates.

Stored procedure Cycles 30 update operations

/*30 Update operation */begin  DECLARE v1 INT DEFAULT;  While v1 > 0 do    update jx_attach set complete=1,attach_size=63100 where cycore_file_id= ' 56677142da502cd8907eb58f ';    SET v1 = v1-1;  END while; END

Execution results (very slow)

Time: 29.876s

Procedure executed successfully
Rows affected: 0

200 Data update operations, three database connections executed concurrently

Update Jx_attach set complete=1,attach_size=63100 where cycore_file_id= ' 56677142da502cd8907eb58f '; Update Jx_attach set complete=1,attach_size=63100 where cycore_file_id= ' 56677142da502cd8907eb58f '; Update Jx_attach set complete=1,attach_size=63100 where cycore_file_id= ' 56677142da502cd8907eb58f '; Update Jx_attach set complete=1,attach_size=63100 where cycore_file_id= ' 56677142da502cd8907eb58f '; Update Jx_attach set complete=1,attach_size=63100 where cycore_file_id= ' 56677142da502cd8907eb58f '; Update Jx_attach set complete=1,attach_size=63100 where cycore_file_id= ' 56677142da502cd8907eb58f '; Wait a minute

Execution results (slower and more slowly after a period of time, waiting locks appear)

# time:151208 22:41:24
# [email protected]: Zmduan[zmduan] @ [192.168.235.1] Id:2
# query_time:1.848644 lock_time:0.780778 rows_sent:0 rows_examined:393382
SET timestamp=1449643284;
Update Jx_attach set complete=1,attach_size=63100 where cycore_file_id= ' 56677142da502cd8907eb58f ';

.........
........

#User@Host: Zmduan[Zmduan]@[192.168.235.1]Id:2# Query_time:2.868598Lock_time:1.558542Rows_sent:0Rows_examined:393382SET timestamp=1449643805;UpdateJx_attachSetComplete=1, attach_size=63100 wherecycore_file_id='56677142da502cd8907eb58f';[[email protected] Log]# tail-F Slow_query.Log # User@Host: Zmduan[Zmduan]@[192.168.235.1]Id: +# Query_time:1.356797Lock_time:0.000169Rows_sent:1Rows_examined:393383SET timestamp=1449643805;SELECT * fromJx_attach ja,jx_feed JFwhereja.feed_id=jf.feed_id andja.cycore_file_id='56677146da502cd8907eb5b7';# User@Host: Zmduan[Zmduan]@[192.168.235.1]Id:2# Query_time:2.868598Lock_time:1.558542Rows_sent:0Rows_examined:393382SET timestamp=1449643805;UpdateJx_attachSetComplete=1, attach_size=63100 wherecycore_file_id='56677142da502cd8907eb58f';

Third, the reason analysis

MySQL's innodb storage engine supports row-level locks, andInnoDB row locks are implemented by locking index entries, which means that only innodb uses row locks when retrieving data through index criteria, otherwise table locks are used. Update the statement according to the current data (update Jx_attach set complete=1,attach_size=63100 where cycore_file_id= ' 56677142da502cd8907eb58f ';), the Condition field cycore_file_id does not add an index, causing the data table to be locked.

Iv. Solutions

To add an index to cycore_file_id

V. Final effect (30 update operations)

Time: 0.094s

Procedure executed successfully
Rows affected: 0

　　

Mysql InnoDB Data Update causes lock table