UPDATE of MySQL lock Learning

UPDATE of MySQL lock Learning

I have been learning MySQL for quite a long time and can stay in a state where I can work but I am not proficient. In addition, many MySQL knowledge points are affected by SQL Server's understanding bias. I can only continue and work hard!

Because you do not understand the source code, MySQL does not provide a good view to verify the following points. Therefore, you can only talk about the test process and experiment results. Please read it with suspicion.

Problem:

How does MySQL lock the UPDATE operation?

Test method:

Determine whether the SQL statements executed by two sessions are blocked.

Test environment:

MySQL: 5.5.14-log Source distribution

Test Table:

CREATETABLE"t_test1"( "id"int(11)NOTNULLAUTO_INCREMENT, "c1"int(11)DEFAULTNULL, "c2"int(11)DEFAULTNULL, "c3"int(11)DEFAULTNULL, "c4"int(11)DEFAULTNULL, PRIMARYKEY("id"), KEY"idx_c1_c2"("c1","c2"), KEY"idx_c3"("c3")) ENGINE=InnoDB AUTO_INCREMENT=16DEFAULTCHARSET=utf8

Current table data:

Test 1:

Session 1 executes the SQL statement but does not submit it: insert into t_test1 (c1, c2, c3, c4) select;

Session 2: Execute SQL: insert into t_test1 (c1, c2, c3, c4) select;

Experiment results: Session 2 can be executed normally without blocking.

Test 2:

Session 1 executes the SQL statement but does not submit it: update t_test1 set c4 = 1 where id = 8;

Session 2: Execute SQL: update t_test1 set c4 = 1 where id = 9;

Experiment results: Session 2 can be executed normally without blocking. Although C1 and C2 with the record ID 8 and 9 are the same and C1 and C2 have indexes.

Session 1 executes the SQL statement but does not submit it: update t_test1 set c4 = 0 where c1 = 1 and c2 = 1;

Session 2: Execute SQL: update t_test1 set c4 = 0 where c1 = 1 and c2 = 1;

Experiment results: Session 2 fails to run normally and is blocked.

Because Session 1 and Session 2 must update the same record, there must be a lock problem. Blocking is understandable.

Test 4:

Session 1 executes the SQL statement but does not submit it: update t_test1 set c4 = 0 where c1 = 1 and c2 = 1 and c4 = 8;

2. Execute SQL: update t_test1 set c4 = 0 where c1 = 1 and c2 = 1 and c4 = 9;

Experiment results: Session 2 fails to run normally and is blocked.

Guess: due to the existence of index idx_c1_c2 (c1, c2), first locate the "first match" record on index idx_c1_c2 according to the condition c1 = 1 and c2 = 1, and then lock the record, then, locate the "final match" Record Based on condition C4 = 8, and finally update the record. However, Session 2 is blocked due to lock during the "first match ".

Test 5:

Session 1 executes the SQL statement but does not submit it: update t_test1 set c4 = 0 where c1 = 1 and c2 = 1 and id = 8;

2. Execute SQL: update t_test1 set c4 = 0 where c1 = 1 and c2 = 1 and id = 9;

Experiment results: Session 2 can be executed normally without blocking.

Because ID is the unique primary key, even if the WHERE condition of Session 1 and Session 2 contains the c1 = 1 and c2 = 1 conditions, it will not cause blocking.

Test 6-1:

Session 1 executes the SQL statement but does not submit it: update t_test1 force index (idx_c1_c2) set c4 = 1 where c1 = 1 and c2 = 2 and c3 = 8;

2. Execute SQL: update t_test1 force index (idx_c1_c2) set c4 = 1 where c1 = 1 and c2 = 2 and c3 = 9;

Experiment results: Session 2 fails to run normally and is blocked.

Because idx_c1_c2 is forcibly used, first lock the index idx_c1_c2 according to the condition c1 = 1 and c2 = 1, resulting in session 2 being blocked.

Test 6-2:

Session 1 executes the SQL statement but does not submit it: update t_test1 force index (idx_c3) set c4 = 1 where c1 = 1 and c2 = 2 and c3 = 8;

2. Execute SQL: update t_test1 force index (idx_c3) set c4 = 1 where c1 = 1 and c2 = 2 and c3 = 9;

Experiment results: Session 2 can be executed normally without blocking.

Because idx_c3 is forcibly used, locks idx_c3 Based on C3 = 8 and C3 = 9, so Session 2 is not blocked by Session 1.

Close the job.