How do I understand the row level lock of MySQL InnoDB?

Because recently in the company of a second kill project, is now some of the most common shopping sites. But considering the concurrency of some problems (perhaps concurrency is not the main, the main is now the second kill will appear seconds kill tools and so on) resulting in the second out of the goods than the actual inventory more, so on the internet to look at the MySQL lock, the company is using the InnoDB engine, thinkphp3.2 Framework, according to the relevant information on the Internet, should be used is row-level lock (for MySQL, I rookie, understand is not very deep).

 public function test_sql(){        set_time_limit(0);        $model = D('Liren/GroupPurchase');        $row = $model->lock(true)->where(array('id'=>1))->find();        if($row){            dump($row);            sleep(10);        }    }

The Test_sql method is to query a data with a lock, then delay for 10 seconds, at the same time, I opened another process:

 public function test_lock(){        $model = D('Liren/GroupPurchase');        $info = $model->find(1);        if($info){            dump($info);        }            }

Test_lock has been waiting until the test_sql is over. And even if I'm querying the Test_lock method for data that's not a primary key (ID) of 1, it's also going to wait until Test_sql ends. This is not the entire table is locked, is it necessary to inherit the thinkphp Advmodel to really achieve row-level lock?

There is one more problem. Is there a relationship between a transaction and a lock?

public function test_sql(){        set_time_limit(0);        $model = D('Liren/GroupPurchase');        $model->startTrans();        $row = $model->lock(true)->where(array('id'=>1))->find();       // echo $model->getLastSql();        if($row){            $ret = $model->lock(true)->save(array('id'=>1,'is_show'=>0));            echo $model->getLastSql();        }        if($ret){            $model->commit();            sleep(10);            echo 'success';        }    }    

Although the transaction is committed, the status of the database has changed a long time, but still have to wait until the end of the Test_sql process Test_lock method to output data, if you can end the transaction when the table lock can end, this is not a good point.

Little brother Dull, hope pointing!

Reply content:

Because recently in the company of a second kill project, is now some of the most common shopping sites. But considering the concurrency of some problems (perhaps concurrency is not the main, the main is now the second kill will appear seconds kill tools and so on) resulting in the second out of the goods than the actual inventory more, so on the internet to look at the MySQL lock, the company is using the InnoDB engine, thinkphp3.2 Framework, according to the relevant information on the Internet, should be used is row-level lock (for MySQL, I rookie, understand is not very deep).

 public function test_sql(){        set_time_limit(0);        $model = D('Liren/GroupPurchase');        $row = $model->lock(true)->where(array('id'=>1))->find();        if($row){            dump($row);            sleep(10);        }    }

The Test_sql method is to query a data with a lock, then delay for 10 seconds, at the same time, I opened another process:

 public function test_lock(){        $model = D('Liren/GroupPurchase');        $info = $model->find(1);        if($info){            dump($info);        }            }

Test_lock has been waiting until the test_sql is over. And even if I'm querying the Test_lock method for data that's not a primary key (ID) of 1, it's also going to wait until Test_sql ends. This is not the entire table is locked, is it necessary to inherit the thinkphp Advmodel to really achieve row-level lock?

There is one more problem. Is there a relationship between a transaction and a lock?

public function test_sql(){        set_time_limit(0);        $model = D('Liren/GroupPurchase');        $model->startTrans();        $row = $model->lock(true)->where(array('id'=>1))->find();       // echo $model->getLastSql();        if($row){            $ret = $model->lock(true)->save(array('id'=>1,'is_show'=>0));            echo $model->getLastSql();        }        if($ret){            $model->commit();            sleep(10);            echo 'success';        }    }    

Although the transaction is committed, the status of the database has changed a long time, but still have to wait until the end of the Test_sql process Test_lock method to output data, if you can end the transaction when the table lock can end, this is not a good point.

Little brother Dull, hope pointing!

Why use MySQL if it's a second-kill feature? Why not consider a memory cache database like Redis?
After all, the IO efficiency of memory and the IO efficiency of disk are roughly the same as the economic power between China and America.

So talk about the lock problem:
I have a clear idea of what the concept should be when compared to the main topic:
Read lock, Shared lock (S)
Write lock, exclusive lock (X)

Compatibility:

    X           SX    不兼容    不兼容S    不兼容    兼容

There is also a kind of call optimistic lock/pessimistic lock

This answer is very good, I took it directly, summed up is:

• Optimistic locks are implemented by logic and do not lock the database in nature

• Pessimistic locking is done through a real database lock mechanism.

Finally, back to your question:

• Test_sql () function: First of all to confirm that when the table gets row lock, try to use index to retrieve records, if you do not use index access, even if you just want to update one row of records, is also full table lock. To ensure that SQL accesses records using an index, when necessary, use explain to check the SQL execution plan to determine if the index is used as expected.
  Since the MySQL row lock is for the index plus lock, not for the record plus the lock, so although it is access to the record, but if the same index key, it will be locked.

• Transactions and locks do not matter, locking mechanism is related to the storage engine

The 1.innodb engine supports row locks, but does not specify a unique index key to lock the table
Test_sql, a pessimistic lock is used, which is the select where for update
---row lock when a Where condition specifies a unique index key
---table lock when non-unique index key

2. In the transaction
Select operation Shared Lock
Update,delete,insert Lock
Commit will cancel the lock.

Under the InnoDB engine, the executed SQL uses row-level locks or full-table locks. It has to do with the isolation level that MySQL uses, the index that SQL uses, and the MySQL itself for this SQL execution optimization.
So how to understand the MySQL lock depends on the specific configuration of MySQL you are using. There is a blog that is particularly thorough and hopefully useful to you. http://blog.sae.sina.com.cn/archives/2127

