Shulou(Shulou.com)06/03 Report--

This article mainly introduces how to achieve the lock of MyIsam and InnoDB engine in java, which has a certain reference value, and interested friends can refer to it. I hope you can learn a lot after reading this article.

Three common types of engines:

MyIsam: does not support transactions, does not support foreign keys, so the access speed is fast. The locking mechanism is a table lock that supports full-text indexing.

InnoDB: supports transactions and foreign keys, so it is less efficient than MyISAM,InnoDB, and takes up more disk space to retain data and indexes. The locking mechanism is a row lock and full-text indexing is not supported.

Memory: the data is stored in memory, and the default hash index is very suitable for storing temporary data. When the server is shut down, the data will be lost.

How to choose a storage engine:

MyISAM: applications are mainly read operations and insert operations, only a few update and delete operations, and do not have high requirements for transaction integrity and concurrency.

InnoDB: for transaction processing applications, foreign keys are supported. If the application has high requirements for transaction integrity, data consistency is required under concurrent conditions. Frequent updates and deletions (InnoDB can effectively reduce locks caused by deletions and updates). For those with high data accuracy requirements, this engine is suitable.

Memory: usually used for small tables that are updated less frequently to get access results quickly.

Locks in Mysql

If you are familiar with multithreading, you must have a concept of locks, which are mechanisms by which computers coordinate multiple processes or threads to access a resource concurrently.

Locks in Mysql are divided into table locks and row locks:

As the name implies, a table lock is locking a table, while a row lock is locking a row.

The characteristics of table lock: low overhead, no deadlock, high probability of lock conflict, and low concurrency.

The characteristics of row lock: high cost, deadlock, low probability of lock conflict and high concurrency.

So the MyISAM and Memory engines use table locks, while the InnoDB storage engine uses row locks.

The locking mechanism of MyISAM:

It is divided into shared read lock and exclusive write lock.

The read lock is that when a process reads a table (select), other threads can also read, but cannot write. The simple understanding is that you can't write while I'm reading.

A write lock is that when a process writes to a table or table (insert,update,delete), other threads cannot write or read. It can be understood that when I write, you can't read or write.

Therefore, the read operation, write operation and write operation of MyISAM are serial! MyISAM automatically locks the table when performing read and write operations (that is, using the lock table command without display), and MyISAM always acquires all the locks needed by the SQL statement at once, which is the reason why MyISAM will not have deadlocks.

Here are examples of write locks and read locks, respectively:

Write Lock:

Get the write lock of the first_test table: mysql > lock table first_test write;Query OK, 0 rows affected (0.00 sec)

The current transaction can execute mysql > select * from first_test; +-+ id for query, update, and insert operations.

Mysql > unlock table;Query OK, 0 rows affected (0.00 sec) wait

Mysql > select * from first_test;+----+-+

Examples of read locks are as follows:

Get the lock mysql > lock table first_test read;Query OK of table first_read, 0 rows affected (0.00 sec)

The table record can be queried for the current transaction: mysql > select * from first_test;+----+-+ id

But the current transaction cannot query unlocked tables: mysql > select * from goods;ERROR 1100 (HY000): Table 'goods' was not locked with LOCK TABLES other transactions can query or update unlocked tables: mysql > select * from goods;+----+

And inserting update locked tables will report errors: mysql > insert into first_test (age) values (15); ERROR 1099 (HY000): Table 'first_test' was locked with a READ lock and can't be updatedmysql > update first_test set age=100 where id = 1 domestic error 1099 (HY000): Table 'first_test' was locked with a READ lock and can't be updated waits when updating locked tables: mysql > update first_test set age=100 where id = 1; wait.

Mysql > unlock table;Query OK, 0 rows affected (0.00 sec) mysql > update first_test set age=100 where id = 1 world query OK, 1 row affected (38.82 sec) Rows matched: 1 Changed: 1 Warnings: 0

Concurrent insertion

I just mentioned that Mysql is serial when inserting and modifying, but MyISAM also supports concurrent operations of queries and inserts.

There is a system variable concurrent_insert (default is 1) in MyISAM to control the behavior of concurrent inserts (users insert data at the end of the table).

When concurrent_insert is 0, concurrent insertion is not allowed.

When concurrent_insert is 1, if there are no holes in the table (there are no deleted rows in the middle), MyISAM allows one process to read the table while another process inserts records from the footer.

When concurrent_insert is 2, records can be inserted at the end, regardless of whether there are holes in the MyISAM table.

Mysql > lock table first_test read local;Query OK, 0 rows affected (0.00 sec)-- adding the local option means that data is allowed to be inserted at the end if the table satisfies concurrent insertions

The current process cannot insert and update mysql > insert into first_test (age) values (15); ERROR 1099 (HY000): Table 'first_test' was locked with a READ lock and can't be updatedmysql > update first_test set age=200 where id = 1 position error 1099 (HY000): Table' first_test' was locked with a READ lock and can't be updated other processes can insert, but updates will wait: mysql > insert into first_test (age) values (15) Query OK, 1 row affected (0.00 sec) mysql > update first_test set age=200 where id = 2; wait.

The current process cannot access data inserted by other processes mysql > select * from first_test;+----+-+ id

After releasing the lock, everyone is happy mysql > unlock table;Query OK, 0 rows affected (0.00 sec) wait

Both inserted and updated: mysql > select * from first_test;+----+-+ id

What to pay attention to:

Concurrent inserts resolve lock contention for queries and inserts in the same table.

If you insert a table with holes concurrently, it will result in fragmentation, so you can use the optimize table command to reclaim the holes caused by deleting records when you are idle.

Lock scheduling

In MyISAM, when one process requests a read lock for a table while another process also requests a write lock, Mysql will first let the latter acquire the write lock. Even if the read request reaches the lock waiting queue before the write request, the write lock is inserted before the read lock.

Because Mysql always thinks that write requests are generally more important than read requests, which is why MyISAM is not suitable for applications with a large number of read and write operations, because a large number of write requests make it difficult for query operations to obtain read locks and may block forever.

How to handle it:

1. Specify the low_priority attribute of Insert, update, and delete statements and lower their priority.

2. Specify the startup parameter low-priority-updates, which makes MyISAM give priority to read requests by default.

3. Execute the command set low_priority_updates=1 to reduce the request issued by the connection.

4. Specify max_write_lock_count to set an appropriate value. When the write lock reaches this value, temporarily lower the priority of the write request and let the read request acquire the lock.

However, the reason for the above treatment is that when complex query statements are encountered, it may be difficult to obtain locks for writing requests, which is a very complicated problem, so we generally avoid using complex query statements. If so, it can be executed in the database idle phase (late at night).

We know that mysql in the past, the storage engine defaults to MyISAM, but with the increasing requirements for transactions and concurrency, we introduced the InnoDB engine, which has a series of features such as supporting transaction security.

InnoDB lock mode

InnoDB implements two types of row locks.

Shared lock (S): an exclusive lock that allows one transaction to read a row and prevents other transactions from acquiring the same dataset.

Exclusive lock (X): allows transaction update data to be acquired with exclusive locks, but organizes other transactions to acquire shared and exclusive locks for the same dataset.

It can be understood like this:

A shared lock is when I read it, you can read it, but you can't write it. Exclusive lock is when I write, you can't read or write. In fact, it is the read lock and write lock of MyISAM, but the target is different.

In addition, InnoDB has two table locks:

Intention shared lock (IS): indicates that the transaction is ready to add a shared lock to a data row, that is, a data row must acquire the IS lock of the table before adding a shared lock.

Intention exclusive lock (IX): similar to the above, it indicates that the transaction is ready to add an exclusive lock to the data row, indicating that the transaction must acquire the IX lock of the table before adding the exclusive lock to the data row.

Note:

When the lock mode of a transaction request is compatible with the current lock, InnoDB grants the requested lock to the transaction; otherwise, if the request is not compatible, the transaction waits for the lock to be released.

The intention lock is automatically added by InnoDB and does not require user intervention.

For insert, update, and delete,InnoDB, exclusive locks are automatically added to the data involved; for general Select statements, InnoDB does not add any locks, and transactions can add shared or exclusive locks to the display through the following statements.

Shared lock: select * from table_name where .lock in share mode.

Exclusive lock: select * from table_name where. For update.

How to implement the lock:

InnoDB row locking is achieved by locking index items, and if there is no index, InnoDB locks records through hidden clustered indexes.

In other words: if the data is not retrieved through the index condition, then InnoDB will lock all the data in the table, the actual effect is the same as the table lock.

Row locks are divided into three situations:

Record lock: lock an index entry, that is, lock a record.

Gap lock: lock the gap between index entries, the gap before the first record, or the gap after the last record, that is, lock a range of records, excluding the record itself.

Next-key Lock: locks a range of records and contains the record itself (a combination of the above two).

Note: the default level of InnoDB is the repeatable-read level, so what is said below is in the RR level.

I didn't understand the difference between Gap Lock and Next-key Lock until I saw a sentence on the Internet. I hope it will be helpful to you.

Next-Key Lock is a combination of row locks and gap locks, so that when InnoDB scans an index record, it first adds a row lock (Record Lock) to the selected index record, and then adds a gap lock (Gap Lock) to the gap between the two sides of the index record. If a gap is locked by transaction T1, other transactions cannot insert records in that gap.

It's boring to talk about it dryly. Let's take a look at the concrete examples:

Suppose we have a table:

+-+ +

| | id | age |

+-+ +

| | 1 | 3 |

| | 2 | 6 |

| | 3 | 9 |

+-+ +

The table structure is as follows:

CREATE TABLE test (id int (11) NOT NULL AUTO_INCREMENT, age int (11) DEFAULT NULL, PRIMARY KEY (id), KEY keyname (age)) ENGINE=InnoDB AUTO_INCREMENT=302 DEFAULT CHARSET=gbk

So the index of our age segment is divided into:

(negative infinity, 3)

(3pd6)

(6pr 9)

(9pr positive infinity)

Let's take a look at several situations:

1. When transaction An executes the following statement:

Mysql > select * from fenye where age=6for update

Not only do row locks lock the corresponding rows of data, but also in the intervals on both sides, gap locks are added to (5, 6) and (6, 9).

In this way, transaction B cannot insert new data in these two intervals, but transaction B can insert data in intervals outside the two intervals.

2. When transaction An executes

Select * from fenye where age=7 for update

Then the interval (6. 9) is locked, and no other transaction can insert or update data in this interval.

3. If the queried data is no longer within the scope

For example, transaction An executes select * from fenye where age=100 for update

Then the lock infinity is (9 positive zone).

Summary:

The row lock prevents other transactions from being modified or deleted, the GAP lock prevents other transactions from being added, and the Next-Key lock formed by the combination of row lock and GAP lock solves the problem of phantom reading when writing data at the RR level.

When to use table locks in InnoDB:

InnoDB uses row-level locks in most cases, because transactions and row locks are often the reasons why we choose InnoDB, but in some cases we also consider using table-level locks.

1. When the transaction needs to update most of the data, the table is relatively large. If you use the default row lock, it is not only inefficient, but also easy to cause other transactions to wait for a long time and lock conflicts.

2. The transaction is complex, which is likely to cause deadlock and rollback.

Deadlock:

We said that deadlocks do not occur in MyISAM because MyISAM always gets all the locks needed at once, either satisfying them or waiting for them all. In InnoDB, the lock is acquired step by step, resulting in the possibility of deadlock.

In the above example, we can see that when two transactions need to obtain the lock held by the other to continue to complete the transaction, resulting in both parties waiting, resulting in a deadlock.

After a deadlock occurs, InnoDB can generally detect and cause one transaction to release the lock and roll back, while the other acquires the lock to complete the transaction.

Avoid deadlocks:

There are several ways to avoid deadlocks, and here are only three common ones:

1. If different programs access multiple tables concurrently, try to agree to access the tables in the same order, which can greatly reduce the chance of deadlock.

2. In the same transaction, try to lock all the resources needed at once to reduce the probability of deadlock.

3. For business parts that are very prone to deadlocks, you can try to use upgrade locking granularity to reduce the probability of deadlocks through table-level locking.

Thank you for reading this article carefully. I hope the article "how to realize the lock of MyIsam and InnoDB engine in java" shared by the editor will be helpful to you. At the same time, I also hope you will support us and pay attention to the industry information channel. More related knowledge is waiting for you to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.