Accidentally MySQL became a table lock

As we all know MySQL database InnoDB engine is the default row lock, but today accidentally found my database how to become a table lock, let me very puzzled ~ ~ ~

1. First look at my database engine

Show variables like '%storage_engine% ';

2. Create Test table

CREATE TABLE ' Test ' (
' id ' int (one) DEFAULT NULL,
' Name ' varchar (255) DEFAULT NULL
) Engine=innodb DEFAULT Charset=utf8;

Only two fields, one ID field, one name field

Insert four piece of data

3. Process Simulation

Since MySQL is an automated transaction, we'll start the transaction manually.

First open a SQL window to execute

Start TRANSACTION;
Update Test T set t.name= ' 111 ' where id=1;

------------------------Explain------------------------------

Start TRANSACTION; This is a manual open transaction

Update Test T set t.name= ' 111 ' where id=1; this is a name value that updates ID 1

(note there is no commit method, the transaction is not ended)

Open a SQL window again (equivalent to re-opening a transaction), execute the following statement

Start TRANSACTION;
Update Test T set t.name= ' 222 ' where id=2;

-------------------------explain-----------------------------

Start TRANSACTION; This is a manual open transaction

Update Test T set t.name= ' 222 ' where id=2; this is a name value that updates ID 2

Theoretically, the InnoDB storage engine is a row lock, and the first transaction adds a lock to the row with ID 1, but has no effect on records with ID 2

But the experimental result is that the update ID of 2 is also waiting, indicating that a table lock was added. Transaction one has locked the whole table. （）

After the investigation ~~~~~

The original data table does not set the primary key, the ID is set as the primary key, you can achieve the effect of row lock.

You are welcome to discuss ~~~~~

Accidentally MySQL became a table lock