In the interview, I was asked whether select...for update would lock the table or the row.

The select query statement will not lock, but select .......for update will lock in addition to its query function, and it is a pessimistic lock.

So whether it adds a row lock or a table lock depends on whether the index/primary key is used.

If there is no index/primary key, it is a table lock, otherwise it is a row lock.

verify:

Create table sql

//id is the primary key //name is the unique index CREATE TABLE `user` (
	`id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(255) DEFAULT NULL,
	`age` INT ( 11 ) DEFAULT NULL,
    `code` VARCHAR(255) DEFAULT NULL,
	PRIMARY KEY ( `id` ),
    KEY `idx_age` ( `age` ) USING BTREE 
) ENGINE = INNODB AUTO_INCREMENT = 1570068 DEFAULT CHARSET = utf8

You need to turn off automatic submission and set it to manual submission by setting @@autocommit=0; 0 represents manual submission, 1 represents automatic submission.

Combined with the example verification

Example 1:

Use the primary key id as the condition to query, and then start another transaction to update the data. The update is blocked and locked, locking the row data with id 1 to be queried.

Figure 1 shows the first transaction, and no transaction is committed

Figure 2 shows the second transaction, which is blocked when updating data.

Figure 3 shows the second transaction, where an error occurs because the lock cannot be obtained for a long time.

Example 2:

We open a transaction to update another data with id 2.

Example 3 (index):

When the table was first created, a unique index was created for age.

Example 4:

Use common field codes to operate

In another transaction, I update another piece of data. If my update succeeds, the row is locked; if it fails, the table is locked.

result:

If the query condition uses an index or primary key, select ..... for update will perform a row lock.

If it is a normal field (no index/primary key), then select ..... for update will lock the table.

This is the end of this article about the interview question "whether select for update will lock the table or the row". For more relevant select for update content, please search 123WORDPRESS.COM's previous articles or continue to browse the following related articles. I hope you will support 123WORDPRESS.COM in the future!