PHP MySQL Transaction processing

Transaction

BEGIN;
COMMIT;
A piece of code that modifies the two fields of the current table

First find it, save it in a variable, and add it to the value of the second field.

The code is as follows	Copy Code
BEGIN; SELECT value into @short_destiption from ' catalog_product_entity_text ' WHERE entity_id = 388 and attribute_id = 62; SELECT value into @destiption from ' catalog_product_entity_text ' WHERE entity_id = 388 and attribute_id = 61; Update Catalog_product_entity_text Set value = Concat (@short_destiption, @destiption) where entity_id = 388 and Attribut e_id = ' 61 '; COMMIT;

operation, to ensure that the three principles:

Atomicity (atomicity): A transaction is an atomic unit of operation whose modification of the data is either fully executed or not implemented;

Consistency (consistent): When a transaction starts and completes, the data must be in a consistent state;

Isolation (Isolation): Database system provides a certain isolation mechanism to ensure that the transaction is not affected by external concurrency operations of the "independent" environment implementation;

Persistence (Durable): After a transaction is completed, its modification to the data is permanent and can be maintained even if a system failure occurs.

So, let's assume that two objects A and B

Concurrent objects A and B

Initial status datasheet query result:

The order in which the transaction starts A->B

A: Start A transaction

No commit at the moment

In this State B began its own MySQL transaction processing:

Obviously, when a does not commit, the action in B's transaction cannot be completed and is always in the transaction waiting stage, provided that B's action cannot be committed without exceeding the time limit.

Now, if a commits:

At the moment B's action, by waiting for the

Transition to

Description A completes the transaction and begins to unlock, b transaction can be done, but at the moment B transaction is not committed (commit)

Note: Here we do two operations, which are two objects to query

A's query:

The item with ID 1 still exists because the result of B is not committed, but a can still read the data, but the data is the data before the deletion.

However, the query against B:

As you can see, the B object results are already being processed, but no unlock is committed.

The analysis can know that a reads a result set that B is not submitted before, but B reads the result sets of its own operation, when B completes the submission

At the moment, the result set of a

It is found that the current state is the same as the set of B, A=b, which is also in the range of theoretical values.

From this, can be found in fact, the simplicity of MySQL lock, of course, also shows that when the database lock operation, can only be write operation control, for reading data, often only access to the modified data, this part of the data is often referred to as "dirty" or dirty data. In reality, we often have such a demand, when a write operation, expect b not to read the data, is the control of read behavior.

1.1. Basic knowledge and related concepts

1 All table types can use locks, but only InnoDB and BDB have built-in transaction capabilities.

2 use begin to start the transaction, use commit to end the transaction, the middle can use rollback ROLLBACK TRANSACTION.

3 The InnoDB table supports consistent reading by default.

4 isolation levels are defined in the SQL standard: Read uncommited,read commited,repeatable read,serializable.

The read uncommited is dirty read, one transaction modifies a row, and the other transaction can read the row.

If the first transaction performs a rollback, the second transaction reads a value that has never been formally seen.

Read commited, which is read uniformly, attempts to solve dirty read problems by reading only the submitted values.

However, this raises the issue of non-repeatable reads.

A transaction executes a query that reads a large number of rows of data. Before it finishes reading, another transaction may have completed the changes to the data row. When the first transaction attempts to execute the same query again, the server returns a different result.

Repeatable read repeats and locks the data rows when a transaction performs a read or write operation on the data row.

But this approach raises the question of fantasy reading.

Because only rows read or written can be locked, no other transaction can be prevented from inserting data, and subsequent execution of the same query can produce more results.

In serializable mode, a transaction is forced to execute sequentially. This is the default behavior recommended by the SQL standard.

4 If multiple transactions update the same row, the deadlock can be unlocked by rolling back one of the transactions.

5 MySQL allows you to set the isolation level using SET transaction.

6 transactions are used only for INSERT and UPDATE statements to update the datasheet and not for changes to the table structure. Executing a change table structure or begin immediately commits the current transaction.

7 All Table types support table-level locks, but MyISAM only supports table-level locks.

8 There are two types of table-level locks: read locks and write locks.

Read locks are shared locks that support concurrent reads and write operations are locked.

Write locks are exclusive locks, and other threads cannot read or write tables during locks.

8 If you want to support concurrent read and write, the recommended InnoDB table, because it is the use of row-level locks, you can get more update performance.

9 many times, it is possible to evaluate what locks are more appropriate for the application through experience, but it is often difficult to say that a lock is better than the other, depending on the application, and different places may require different locks. Currently MySQL already supports table-level locks for ISAM, MyISAM, MEMORY (HEAP) type tables, BDB tables support page-level locks, and InnoDB tables support row-level locks.

MySQL table-level locks are write-lock priority, but the queuing mechanism, so there will be no deadlock situation. For InnoDB and BDB storage engines, deadlocks can occur. This is because InnoDB automatically captures row locks, and BDB captures page locks when executing SQL statements, rather than doing so at the beginning of a transaction.
1.2. The advantages and disadvantages of different locks and selection

The advantages and options of row-level locks:

1 Reduce conflicting locks when many threads request different records.

2 Reduce data change when transaction rollback.

3 Make it possible to lock a single row of records for a long time.

The disadvantage of row-level locks:

1 consumes more memory than page-level locks and table-level locks.

2 when used in a large number of tables, it is slower than page-level and table-level locks because he needs to request more resources.

3 It is significantly worse than other locks when it is necessary to perform GROUP by operations on most data frequently or to scan the entire table frequently.

4 using higher-level locks, it is easier to support a variety of different types of applications, because the cost of this lock is much smaller than the row-level lock.

5 You can use application-level locks instead of row-level locks, such as Get_lock () and Release_lock () in MySQL. But they are advisory locks (Original: These are advisory locks) and therefore can only be used in secure and trusted applications.

6 for InnoDB and BDB tables, MySQL uses table-level locks only when it specifies lock tables with lock tables. In both tables, it is recommended that you do not use lock tables because InnoDB automatically uses row-level locks, BDB to secure transaction isolation with page-level locks.

Table lock Advantages and options:

1 Many operations are read table.

2 Read and update on the strict condition index, when the update or deletion can be read with a separate index to obtain: Update tbl_name SET column=value WHERE unique_key_col=key_value;delete from Tbl_name WHERE Unique_key_col=key_value;

3 The SELECT and INSERT statements execute concurrently, but few UPDATE and DELETE statements are available.

4 Many scan tables and GROUP by operations on the whole table, but there is no write table.

Disadvantages of Table Locks:

1 A client submits a SELECT operation that takes a long time to run.

2 The other client submits an UPDATE operation on the same table, and the client waits until the SELECT completes to begin execution.

3 Other clients also submit a SELECT request to the same table. Because update takes precedence over Select, the select waits until the update completes before it starts execution, and it waits for the first select operation.

1.3. How to avoid the lock resource competition

1 make the SELECT speed as fast as possible, which may require creating some summary tables.

2 use parameter--low-priority-updates when starting mysqld. This will give the update operation a lower priority than SELECT.

In this case, in the above assumptions, the second select is executed before the INSERT, and there is no need to wait for the first select.

3 You can execute the SET low_priority_updates=1 command, specifying that all update operations are placed in a specified link to complete.

4) Use the Low_priority attribute to reduce the priority of the insert,update,delete.

5) Use high_priority to increase the priority of the SELECT statement.

6 starting with MySQL 3.23.7, you can specify the system variable Max_write_lock_count as a lower value when you start the mysqld, which forces a temporary increase in the precedence of all SELECT operations after the table's insertion number reaches a specific value. It allows a READ lock to reach a certain number after the WRITE lock is reached.

7 when there is a problem with INSERT and select, you can instead use the MyISAM table, which supports concurrent SELECT and insert operations.

8 Insert delayed can be useful when there are both inserts and deletions on the same table.

9 The LIMIT parameter of the delete may be useful when a problem occurs with the Select and delete.

10 using Sql_buffer_result in the execution of a select helps reduce the duration of the short lock table.

11) You can modify the source code ' MYSYS/THR_LOCK.C ', using only one queue. In this case, the write lock and read lock priority is the same, which may be helpful for some applications.