I. Concurrent problem generation multithreading/process simultaneous operation (read/write) the same data
Ii. Types of concurrent problems Lost Update (lost update) The first class update is lost (rollback is lost): when 2 transactions update the same data source, if the first transaction is committed and the other transaction is revoked, then the same as the first, and the new will be revoked. That means the first transaction is lost with the new one. Second Class update loss (overwrite loss): the second type of update loss is the concurrency problem that is often encountered in practical applications, and he is essentially the same concurrency problem as non-repeatable reading, and is often viewed as a special case of non repetitive reading. The second category of lost updates occurs when 2 or more transactions query the same records and then update the row based on the original query results. Because each transaction is unaware of the existence of other transactions, the last transaction's modifications to the record will overwrite the submitted and new transactions made to the record. Dirty Read (dirty Read) dirty read (transaction not submitted, read ahead): Dirty reading means that when a transaction is accessing data and the data has been modified, and the modification has not been committed to the database, then another transaction accesses the data and then uses the data. Non-repeatable read (Non-repeatable Read) non-repeatable reads (inconsistent two-read): Refers to reading the same data multiple times within a transaction. When this transaction is not finished, another transaction accesses the same data. Then, between the two read data in the first transaction and the modification of the second transaction, the data read by the first transaction two times may be different. This makes it happen that the data read two times within a transaction is not the same, so it is called a non repeatable read. For example, an editor reads the same document two times, but between two reads, the author rewrites the document. When the editor reads the document for the second time, the document has changed. The original read cannot be duplicated. You can avoid this problem if the editor can read the document only after the author has finished writing it all. Phantom Read (Phantom Read) Phantom Read (occurs after another transaction has been inserted or deleted): Refers to a phenomenon that occurs when a transaction is not executed independently, for example, the first transaction modifies the data in a table, which involves all the rows of data in the table. At the same time, the second transaction modifies the data in this table, which is inserting a row of new data into the table. So, it's going to happen later. The user of the first transaction discovers that there are no modified data rows in the table, as if there were hallucinations. For example, an editor changes the document submitted by the author, but when the production department merges its changes to the master copy of the document, it discovers that the author has added the unedited new material to the document. This problem can be avoided if no one is able to add new material to the document until the editor and production department finish processing the original document. Http://msdn.microsoft.com/en-us/library/aa213029 (v=sql.80). aspx HTTP://WWW.CNBLOGS.COM/PYQ228/ARCHIVE/2012/05/26 /2519447.html
Third, the solutionQuarantine----to localize data (that is, use unshared data, thread-exclusive), thread-safe identification of unchanging data--try to use constant locking--to lock variable data that has to be shared
Iv. optimistic locking strategy-conflict detectionOptimistic lock policies are usually based on some version of the data tag. To detect "lost updates," the system checks the version mark of the data that will be updated and the version tag of the shared data, and if the system updates the data and updates the version tag, you can only discard this update and start over.
v. Pessimistic lock strategy-conflict avoidanceRead lock (Shared lock s): Open to read lock, write lock closed (lock table table name in share mode) (exclusive x): Closed for both read and write locks (select * from table name where condition for update)
VI, deadlock--pessimistic lock will cause deadlockDeadlock Cancellation Scheme: Deadlock timeout control (which can result in a long locking time), deadlock detection mechanism deadlock prevention scheme: Force to get all the locks that may be needed at the start, and then no more locks are allowed. Specify the order in which each person acquires the lock. For example, in alphabetical order. When the lock is not taken, the automatic sacrifice ...
Vii. Business--the main tool for dealing with concurrency problemsACID Properties: Atomic (actomicity), consistency (consistency), isolation (isolation), persistence (durability) to prevent lock escalation (lock escalation) when using transactions If a transaction locks many rows in a table, the database may not be able to handle so many locks and can only escalate the lock to lock the entire table.
VIII. Transaction ISOLATION LEVEL--separate locks between transactionsIsolation level is the level of concurrency control over transactions. Ansi/iso SQL divides it into serialized (SERIALIZABLE), repeatable read (repeatable read), read-committed (reading commited), read-uncommitted (reading uncommited) four levels. In order to implement isolation levels, the database typically uses locks (lock). Generally in the programming when only need to set the isolation level, as to the specific use of what locks are set by the database.
ix. The transaction isolation level defined by JDBC
Connection. Transaction_noneDescription does not support transactions.
Connection. Transaction_read_uncommittedNote A transaction can see a change in another transaction before committing. Such dirty reads, non repeatable read and virtual reads are allowed.
Connection. Transaction_read_committedNote Reading uncommitted data is not allowed. This level still allows non repeatable read and virtual reads to occur.
Connection. Transaction_repeatable_readIndicates that the transaction guarantees that the same data can be read again without fail, but that virtual reads will still occur.
Connection. Transaction_serializableis the highest transaction level, preventing dirty reads, non repeatable reads, and virtual reads.
10. Definition and configuration of spring transaction isolation level
Transactiondefinition.isolation_defaultThis is a Platfromtransactionmanager default isolation level that uses the default transaction isolation level of the database. The other four is corresponding to the isolation level of JDBC
transactiondefinition.isolation_read_uncommittedThis is the lowest level of isolation for a transaction, which allows a transaction to see uncommitted data for this transaction. This isolation level produces dirty reads, no repeat reads, and Phantom reads.
transactiondefinition.isolation_read_committedGuarantees that a transaction can be modified before it is read by another transaction. Another transaction cannot read data that is not committed by the transaction. This transaction isolation level prevents dirty reads from appearing, but may occur with no repeatable reads and Phantom reads.
Transactiondefinition.isolation_repeatable_readThis transaction isolation level prevents dirty reads and cannot be read repeatedly. However, Phantom reads may occur. In addition to ensuring that one transaction cannot read data that is not committed by another transaction, it also ensures that the following conditions are avoided (non-repeatable reads).
transactiondefinition.isolation_serializableThis is the highest-cost but most reliable transaction isolation level. Transactions are processed for sequential execution. In addition to preventing dirty reading, it is not repeatable to read, but also avoid phantom reading. <tx:advice id= "Txadvice" transaction-manager= "TransactionManager" > <tx:attributes> <tx:method N Ame= "*" propagation= "REQUIRED" isolation= "read_committed"/> </tx:attributes> </tx:advice>
11, the default transaction isolation level for several large databasesIsolation level Each database is different, if you do not specify this attribute, it is default SQL server:read commited Oracle:read commited mysql:repeatable Read
Appendix I: Oracle LockOracle locks fall into two broad categories: data locks (DML locks) and dictionary locks. Dictionary locks include parsing locks and DDL locks, which are controlled by the DBMS and are not authorized by the user. Oracle 5 Data locks: shared locks, exclusive locks, row-level shared locks (Rs locks), row-level exclusive locks (Rx locks), shared row-level exclusive locks (SRX locks). The lock granularity consists of row and table levels. Data-Lock compatibility matrix: s X rs RX SRX s y n y n n X n n o \ n n RS Y-N-y RX n n y y n SRX n n y n n
In general, Oracle locks itself, and the user can also lock by using statements such as lock table. Oracle, by default, reads data without locking, and instead prevents dirty reads and guarantees repeatable reads by rolling back segments. Oracle has a deadlock check feature that periodically checks the system for deadlocks and, if there are deadlocks, undo transactions with the least number of update operations.
Appendix II: Tests for JDBC transaction isolation level
@Test public void Testdbtransactionisolation () throws ClassNotFoundException, SQLException {Connection cn = Getcon
Nection ();
System.out.print ("Default transaction Isolation Level is:");
Printtransactionisolation (CN);
Settransactionisolation (CN, Connection.transaction_none, "Transaction_none");
Settransactionisolation (CN, connection.transaction_read_uncommitted, "transaction_read_uncommitted");
Settransactionisolation (CN, connection.transaction_read_committed, "transaction_read_committed");
Settransactionisolation (CN, Connection.transaction_repeatable_read, "Transaction_repeatable_read");
Settransactionisolation (CN, connection.transaction_serializable, "transaction_serializable");
Cn.close (); private void Settransactionisolation (Connection cn, int level, String leveldiscription) {try {System.out.pri
NT ("SET TRANSACTION ISOLATION Level: + Leveldiscription +", ");
Cn.settransactionisolation (level);
System.out.print ("Set success,");
Printtransactionisolation (CN); catch (ExCeption e) {System.out.println ("Set failed:" + e.getmessage ()); } private void Printtransactionisolation (Connection cn) throws SQLException {int level = Cn.gettransactionisolatio
N ();
if (level = = Connection.transaction_none) System.out.println ("Transaction_none");
else if (level = = connection.transaction_read_uncommitted) System.out.println ("transaction_read_uncommitted");
else if (level = = connection.transaction_read_committed) System.out.println ("transaction_read_committed");
else if (level = = Connection.transaction_repeatable_read) System.out.println ("Transaction_repeatable_read");
else if (level = = connection.transaction_serializable) System.out.println ("transaction_serializable"); }
