Mysql Lock table for update (engine/transaction)

Because you used Oracle before, you know that you can lock a table with SELECT * for update. So it is natural to think in MySQL can not adapt to the for update to lock the table.

Learn the following references

Because the InnoDB preset is Row-level lock, MySQL executes row lock (only the selected data sample) only if the specified primary key is "clear", otherwise MySQL will execute table lock (lock the entire data form).
As an example:
Suppose there is a form of products with ID and name two fields, ID is the primary key.
Example 1: (explicitly specify the primary key, and there is this information, row lock)
SELECT * FROM Products WHERE id='3' For UPDATE;
Example 2: (explicitly specify the primary key, if the information is not found, no lock)
SELECT * FROM Products WHERE id='-1' For UPDATE;
Example 2: (No primary key, table lock)
SELECT * FROM Products WHERE name=' mouse' For UPDATE;
Example 3: (primary key ambiguous, table lock)
SELECT * FROM Products WHERE id<>&apos;3&apos; For UPDATE;
Example 4: (primary key ambiguous, table lock)
SELECT * FROM products WHERE ID like &apos;3&apos; For UPDATE;
Note 1:for Update applies only to InnoDB and must be in the transaction block (Begin/commit) to take effect.
NOTE 2: To test the condition of the lock, you can use the command Mode of MySQL and open two windows to do the test.

Two questions are drawn from the InnoDB already traded block above.

1. What is InnoDB?

MySQL is a kind of database software that we use more often. It has many advantages, such as open source, free and so on. In fact, it has a very good feature, that is, there are a variety of engines to choose from. If the driver can change the terrain and the most suitable engine according to different road conditions, then they will create a miracle. However, they do not have the same easy to replace the engine, but we can!
The so-called know-how can be baizhanbudai, in order to bring them to the extreme, first of all we should come to understand the MySQL provides us with these kinds of engines.
In general, MySQL has the following engines: ISAM, MyISAM, HEAP, InnoDB, and Berkley (BDB). Note: Different versions of the supported engines are differentiated.

Further:

How to viewMySQLCurrent storage engine?

In general, MySQL will provide a variety of storage engines by default, which you can view by:

See what storage engine your MySQL has now provided:
Mysql> show engines;

The above instructions for my MySQL default to use the InnoDB engine, and support Myisam,memory,archive,mrg_myisam.

Explained later, InnoDB's explanation is: support transactions, row-level locking, foreign keys.

Look at your MySQL current default storage engine:
Mysql> Show variables like &apos;%storage_engine%&apos;;

You want to see what engine the table is using (in the results, the storage engine that the table is currently using is the one behind the parameter engine):
Mysql> Show create table table name;

This is true, because my MySQL engine is InnoDB, and the tables created by default are now InnoDB engines.

2. What are trading blocks?

Reference: http://hi.baidu.com/cuihu0706/blog/item/1dd6ccb1621c355709230278.html

Transaction processing in various management systems have a wide range of applications, such as personnel management system, many synchronous database operations need to use the transaction processing. For example, in the Personnel Management system, you delete a person, you need to delete the basic information of the person, but also to delete the information related to the person, such as mailbox, articles and so on, so that these database operation statements constitute a transaction!
SQL statement to delete
Delete from userinfo where ~ ~ ~
Delete from Mail where ~ ~
Delete from article where~~
~~
If there is no transaction, in the process of your deletion, assuming that the error, only executed the first sentence, then the consequences are unimaginable!
But with transaction processing. If you delete an error, you can cancel the delete operation as long as you rollback (in fact, if you do not have a commit you do not actually perform the deletion) in general, in business-grade applications, you must consider transaction processing!

The

MySQL database supports transactional functionality starting from 4.1, and it is said that 5.0 will introduce a stored procedure ^_^
      briefly introduce the transaction. A transaction is the execution unit of a DBMS. It is made up of a limited number of database operation sequences. But not any database operation sequence can be a transaction. In general, a transaction is required to meet 4 conditions (ACID)
      atomicity (autmic): The transaction is executed, to do "either do it or do it all!" ", which means that the transactional part is not allowed to execute. Even if the transaction can not be completed because of the failure, also eliminate the impact on the database when rollback!
     Consistency (consistency): Transactional operations should enable the database to be transformed from one consistent state to another consistent state! Take online shopping, you only have to let the goods out of the library, and let the goods into the customer's shopping basket to constitute a business!
     Isolation (Isolation): If more than one transaction executes concurrently, it should be done independently of each transaction!
     Persistence (Durability): A successful transaction has a lasting effect on the database, even if the database should fail, it should be able to recover!

There are two main ways to handle MySQL transactions.
1, with Begin,rollback,commit to achieve
Begin a transaction
ROLLBACK TRANSACTION Rollback
Commit TRANSACTION Acknowledgement
2, directly with set to change the MySQL automatic submission mode
MySQL is automatically submitted by default, that is, you submit a query, it is executed directly! We can pass
Set autocommit=0 prohibit auto-commit
Set autocommit=1 turn on auto-commit
But note that when you use set autocommit=0, all of your later SQL will be transacted until you end with commit confirmation or rollback, and notice that when you end the transaction, you start a new transaction! Press the first method to only present the current as a transaction! The first method of personal recommendation!
only InnoDB and BDB types of data tables in MySQL can support transactional processing! Other types are not supported! ( remember!) ）

MYSQL5.0 winxp under test through ~ ^_^

mysql> use test;
Database changed
mysql> CREATE TABLE ' dbtest ' (
ID Int (4)
) Type=innodb;
Query OK, 0 rows affected, 1 warning (0.05 sec)

Mysql> SELECT * from Dbtest
;
Empty Set (0.01 sec)

Mysql> begin;
Query OK, 0 rows Affected (0.00 sec)

mysql> INSERT into Dbtest value (5);
Query OK, 1 row Affected (0.00 sec)

mysql> INSERT into Dbtest value (6);
Query OK, 1 row Affected (0.00 sec)

Mysql> commit;
Query OK, 0 rows Affected (0.00 sec)

Mysql> select * from Dbtest;
+------+
| ID |
+------+
| 5 |
| 6 |
+------+
2 rows in Set (0.00 sec)

Mysql> begin;
Query OK, 0 rows Affected (0.00 sec)

mysql> INSERT into dbtest values (7);
Query OK, 1 row Affected (0.00 sec)

mysql> rollback; //Roll back here.
Query OK, 0 rows Affected (0.00 sec)

Mysql> select * from Dbtest;
+------+
| ID |
+------+
| 5 |
| 6 |
+------+
2 rows in Set (0.00 sec)

Mysql>

Now that you know the relevant knowledge, then use the for update to lock the row, to experiment

For example, the student table in the above example,

1. Start a transaction using begin

2. Use SELECT * for update to lock the row,

3. Verify that the unchecked row is locked in a new window----not locked

4. In a new window, verify that the selected row is locked-----locked, and the update statement fails after a period of waiting.

Mysql Lock table for update (engine/transaction)