The difference between MySQL storage engines

On the MySQL lock line or lock table, this problem, today is a bit of a clue, MySQL InnoDB is a lock line, but the project actually appeared deadlock, lock table situation. Why is it? Take a look at this article first.

When doing the project due to business logic needs, the data table must be one row or more rows to join the row lock, for the simplest example, the book borrowing system. Suppose Id=1 's book is in stock at 1, but there are 2 people at the same time borrowing the book, The logic here is

Select   restnum from book where id =1 ;     -- 如果 restnum 大于 0 ，执行 update  Update   book set restnum=restnum-1 where id=1 ;  Select   restnum from book where id =1 ;   -- 如果 restnum 大于 0 ，执行 updateUpdate   book set restnum=restnum-1 where id=1;

The problem comes, when 2 people at the same time to borrow, it is possible that the first person to execute the SELECT statement, the second person inserted in, in the first person did not have time to update the Book table, the second person to find the data, is actually dirty data, because the first person will be the Restnum value minus 1, So the second person should have been found id=1 book Restnum is 0, so will not perform the update, but will tell it id=1 book is not in stock, but the database that understand these, the database is only responsible for executing a number of SQL statements, it does not matter whether there are other SQL statements in the middle, it Also do not know to take a session of the SQL statement after executing another session. So it will result in concurrency when the final result of Restnum is-1, which is obviously unreasonable, so the concept of locking, Mysql using the InnoDB engine can be indexed to the data row lock. The above-borrowed statements become:

Begin;  Select   restnum from book where id =1 for   update ;  -- 给 id=1 的行加上排它锁且 id 有索引  Update   book set restnum=restnum-1 where id=1 ;  Commit;

This way, the second person executes to the SELECT statement and waits until the first person executes the commit. This ensures that the second person does not read the data before the first person changes.

That's not foolproof, the answer is no. Look at the following example.

Come with me step-by-step, create a table first

CREATE TABLE `book` (   `id` int(11) NOT NULL auto_increment,   `num` int(11) default NULL,   `name` varchar(0) default NULL,   PRIMARY KEY (`id`),   KEY `asd` (`num`)   ) ENGINE=InnoDB DEFAULT CHARSET=gbk

Where the NUM field is indexed

Then insert data, run,

insert into book(num) values(11),(11),(11),(11),(11);   insert into book(num) values(22),(22),(22),(22),(22);

Then open 2 MySQL console windows, in fact, the establishment of 2 sessions to do concurrent operations

********************************************************************
Run in the first session:

begin;select* from book where num=11 forupdate;

Results appear:

+----+-----+------+| id | num | name |+----+-----+------+| 11 | 11 | NULL || 12 | 11 | NULL || 13 | 11 | NULL || 14 | 11 | NULL || 15 | 11 | NULL |+----+-----+------+5 rows in set

Then run in the second session:

begin;select* from book where num=22 forupdate;

Results appear:

+----+-----+------+| id | num | name |+----+-----+------+| 16 | 22 | NULL || 17 | 22 | NULL || 18 | 22 | NULL || 19 | 22 | NULL || 20 | 22 | NULL |+----+-----+------+5 rows in set

Well, there's nothing wrong here, is it, but the next question is coming, everyone see:
Back to the first session, run:

updatebook set name=‘abc‘ wherenum=11;

********************************************************************************************
The question came, the session unexpectedly in wait state, but Num=11 's line is not the first session oneself lock, why can't update? OK, here you may have their own answers, don't worry, please look at the operation.

Close all 2 sessions and run:

delete   from   Book where   num=11 limit 3;   delete   from  book where  num=22 limit 3;

In fact, the records of Num=11 and 22 were deleted by 3 lines,
Then repeat the action between "***********************"
Unexpectedly found, run update book set name= ' abc ' where num=11; After that there was a result that showed no lock,
Why is it that 2 rows of data and 5 rows of data, for MySQL, will produce both lock and lock tables. After discussing with the netizen and flipping through the data, after careful analysis, found that:

In the case of the above experimental data as test data, because the NUM field repetition rate is too high, there are only 2 values, 11 and 12, respectively. And the amount of data relative to these two values is relatively large, is 10, 5 times times the relationship.
Then MySQL ignores the index when interpreting SQL, because its optimizer finds that, even if the index is used, the full table scan is done, the index is discarded, the row lock is not used, and the table lock is used. Simply put, MYSQL ignores your index, it feels that it is not as good as a direct table lock, after all, it feels that the cost of table lock is smaller than the row lock. The above problem even if you use the force index to enforce indexing, the result is the same, always a table lock.

So the MySQL row lock is not so arbitrary and must be indexed. Let's look at the following example.

select id from items where id in (select id from items where id <6) for update;    --id字段加了索引  select id from items where id in (1,2,3,4,5) for update;

Most will think the result is no different, in fact the difference is big, the difference is that the first SQL statement will produce a table lock, and the second SQL statement is a row lock, why? Because the first SQL statement uses the subquery perimeter query, the index is not used, resulting in a table lock.

Well, back to the borrowing example, because the ID is unique, so there is no problem, but if some of the table has a duplicate index, and MySQL will force the use of table locks, what to do? In general, only the redesign of the table structure and the use of new SQL statements to implement business logic, but in fact, the above examples of borrowing is another way. Take a look at the following code:

Set   sql_mode=  ‘STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION‘;  Begin;  Select restnum from book where id =1   ; -- 取消排它锁 , 设置 restnum 为 unsigned  Update   book set restnum=restnum-1 where id=1 ;  If(update 执行成功 ) commit;  Else   rollback;

This is a trick, by temporarily setting the database schema to strict mode, when Restnum is updated to-1, because Restnum is a unsigned type, the update fails, no matter what database operation the second session does, it will be rolled back, thus The correctness of the data is ensured only to prevent the occurrence of a minimal probability of concurrency. 2 Session SQL statement nesting execution causes data to be dirty read. Of course, the best way is to modify the table structure and SQL statements, let MySQL through the index to add row lock, MySQL test version of 5.0.75-log and 5.1.36-community

So, it can be summed up. Mysql InnoDB is a lock line, but if there is no index, or index as above, then lock the table.

Http://www.cnblogs.com/mingxuan/archive/2011/10/11/2207560.html

Extended reading:
HTTPS://WWW.BAIDU.COM/S?WD=MYSQL%20 Storage Engine%20 Differences
HTTP://WWW.SOGOU.COM/WEB?QUERY=MYSQL%20 Storage Engine%20 Differences
HTTPS://WWW.SO.COM/S?Q=MYSQL%20 Storage Engine%20 Differences

The difference between MySQL storage engines