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

This article mainly gives you a brief talk about MySQL transaction features, related technical terms you can check online or find some related books to supplement, here is not involved, we will go straight to the topic, I hope MySQL transaction features this article can bring you some practical help.

Transaction characteristics ACID 1. Atomicity 2. Consistency 3. Isolation 4. Durability View transaction isolation level select @@tx_isolation; Start closing transaction//Start transaction start/begin;//Commit or rollback Set transaction autocommit switch SET autocommit = {0| 1} Set transaction isolation level SET [GLOBAL| SESSION] TRANSACTION ISOLATION LEVEL READ COMMITTED Optional transaction isolation levels are:REPEATABLE READ COMMITTEDREAD UNCOMMITTEDSERIALIZABLE Database concurrency problems Read dirty transaction T1 reads updated data that transaction T2 did not commit. During the execution of non-repeatable read transaction T1, transaction T2 commits new data, and transaction T1 reads inconsistent data before and after transaction T2 commits. Phantom reads the case where insertion or deletion of transaction T1 (transaction committed) causes transaction T2 to be inconsistent in number of rows. MySQL Each transaction isolation level solves concurrency problems Transaction isolation level reads Dirty non-repeatable reads Magic reads Uncommitted (Read-Uncommitted) Possibly Possibly Read Submit (Read-Committed) Impossible Possibly Repeatable (Repeatable-Read) Impossible Impossible Serialization (Serializable) Impossible The following two examples illustrate that RR is a create table goods with illusion reading phenomenon (id int primary key,name varchar(100),amount int not null); insert magic read transaction 1 transaction 2begin;begin;select * from goods; Empty set (0.00 sec)insert into goods(id, name, amount) values(1, 'apple fruit', 100);commit;insert into goods(id, name, amount) values(1, ' apple fruit', 100);ERROR 1062 (23000): Duplicate entry '1' for key 'PRIMARY'

For transaction 1, the illusion occurs when the table is empty at first and then a duplicate key is inserted.

Update Fantasy Read Transaction 1 Transaction 2begin;begin;select * from goods;1 row in set (0.00 sec)insert into goods(id, name, amount) values(2, 'Banana', 100);commit;update goods set amount=1;Rows matched: 2 Changed: 2 Warnings: 0

For transaction 1, the initial lookup table had one entry, but later updates found that two entries had been updated, and the illusion occurred.

A kind of lock that allows other transactions to read the locked object, and to also acquire other shared locks on it, but not to write to it. This means that a shared lock on a resource means that the resource can be read but cannot be deleted or modified. If transaction T1 places a shared lock on a resource, T1 can also place an exclusive lock on the resource if no other transaction places a shared lock on the resource. Usage syntax: begin; select * from tableName where id=2 lock in share mode;commit; exclusive lock (exclusive (X) lock )A kind of lock that prevents any other transaction from locking the same row. Depending on the transactionisolation level, this kind of lock might block other transactions from writing to the same row, or might also blockother transactions from reading the same row. The default InnoDB isolation level, REPEATABLE READ, enableshigher concurrency by allowing transactions to read rows that have exclusive locks, a technique known asconsistent read. Exclusive lock is also called X lock. Deletion, update and insertion in transaction will add exclusive lock to resource. After adding exclusive lock to resource, shared lock and exclusive lock cannot be added to it. If added, it causes blocking until the last lock is released. Use syntax: begin;select * from tableName where id=2 for update;

commit;

MySQL transaction features will be mentioned to you first, for other related issues you want to know can continue to pay attention to our industry information. Our section content captures some industry news and expertise to share with you every day.

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.