Enterprise Application architecture Model-concurrency, transactions, locks

First, the generation of concurrency problems

Multithreading/Process simultaneous operation (read/write) the same data

ii. Types of concurrency problems

• Missing updates (lost update)
  • First Class update lost (rollback 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 new one is revoked. This means that the first transaction is lost with the new one.
  • The second type of update is missing (overwrite lost): The second type of update loses the concurrency problems that are often encountered in real-world applications, and he and non-repeatable reads are essentially the same concurrency problem, which is often seen as a special case of non-repeatable reading. When 2 or more transactions query the same record and then update the row based on the original query results, the second class loses the update. Because every transaction is unaware of the existence of other transactions, the last transaction's modification to the record overwrites the other transaction's committed new to the record.
• Dirty Reads (Dirty read)
  • Dirty Read (transaction not committed, read ahead): Dirty reading means that when a transaction is accessing the data, and the data has been modified, and this modification has not been committed to the database, another transaction accesses the data and then uses that data.
• Non-repeatable read (Non-repeatable Read)
  • Non-repeatable read (inconsistent with two reads): 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, the data that the first transaction two reads may be different because of the modification of the second transaction. This occurs when the data that is read two times within a transaction is not the same and is therefore called 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 is not repeatable. 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 other transactions are inserted or deleted): a phenomenon that occurs when a transaction is not executed independently, such as when the first transaction modifies data in a table that involves all rows of data in the table. At the same time, the second transaction modifies the data in the table by inserting a new row of data into the table. Then the user who will be working on the first transaction in the future finds that there are no modified rows of data in the table, as if the illusion had occurred. For example, an editor changes the document submitted by the author, but when the production department merges its changes into the primary copy of the document, it finds 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 have finished 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 solution

• Quarantine-Enables data localization (that is, using unshared data, thread-exclusive), Thread-safe
• Identifying unchanging data-using constants as much as possible
• Locking-Lock the variable data that has to be shared

Four, optimistic locking strategy--conflict detection

Optimistic locking policies are usually built on some version of the data tag. In order to detect "missing 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 two are the same, the system updates the data and updates the version mark, otherwise, you can only discard this update and start over again.

v. Pessimistic locking strategy--avoidance of conflict

Read lock (Shared lock s): Open to read lock, closed to write lock (lock table name in share mode)

Write lock (exclusive say X): both read and write locks are closed (SELECT * from table name where condition for update)

six, deadlock--pessimistic lock will lead to deadlock

Deadlock Cancellation Scheme: Deadlock time-out control (which can lead to long accidental lock-in), deadlock detection mechanism

Deadlock prevention Scenarios:

• Forces all possible locks to be obtained at the beginning, and no more locks are allowed thereafter.
• Specifies the order in which each person acquires the lock. For example, in alphabetical order.
• When the lock is not taken, the automatic sacrifice.
• 。。。。

VII. Transactions-the main tool for dealing with concurrency problems

ACID properties: atomicity (actomicity), consistency (consistency), isolation (isolation), persistence (durability)

When using transactions, to prevent lock escalation (lock escalation), if a transaction locks many rows in a table, the database may not be able to handle so many locks, and the lock can only be upgraded to lock the entire table.

VIII. Transaction ISOLATION LEVEL--transactions are separated from each other by locks

The isolation level is the level of concurrency control for transactions. Ansi/iso SQL divides it into serialization (SERIALIZABLE), repeatable read (repeatable read), read-committed (read commited), read-uncommitted (read uncommited) four levels. In order to achieve the isolation level, the database is typically locked (lock). Generally in the programming time only need to set the isolation level, as to the specific use of what the lock is set by the database.

ix. the level of transaction isolation defined by JDBC

• Connection. The Transaction_none description does not support transactions.
• Connection. Transaction_read_uncommitted indicates that a transaction can see changes in another transaction before committing. Such dirty reads, non-repeatable reads, and virtual reads are allowed.
• Connection. Transaction_read_committed indicates that reading uncommitted data is not allowed. This level still allows non-repeatable reads and virtual reads to occur.
• Connection. Transaction_repeatable_read indicates that the transaction guarantees the ability to read the same data again without failure, but the virtual read will still appear.
• Connection. Transaction_serializable is the highest transaction level, which prevents dirty reads, non-repeatable reads, and virtual reads.

10. Definition and configuration of spring transaction isolation level

• Transactiondefinition.isolation_default This is a platfromtransactionmanager default isolation level, Use the default transaction isolation level for the database. Four additional isolation levels corresponding to JDBC
• transactiondefinition.isolation_read_uncommitted This is the lowest isolation level for transactions, and it allows a transaction to see uncommitted data for this transaction. This isolation level produces dirty reads, non-repeatable reads, and Phantom reads.
• transactiondefinition.isolation_read_committed guarantees that a transaction modified data is committed before it can be read by another transaction. Another transaction cannot read uncommitted data for the transaction. This level of transaction isolation avoids dirty reads, but non-repeatable reads and phantom reads can occur.
• Transactiondefinition.isolation_repeatable_read This 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 uncommitted data from another transaction, it ensures that the following conditions are avoided (non-repeatable read).
• transactiondefinition.isolation_serializable This is the most cost-effective, but most reliable, transaction isolation level. Transactions are processed for sequential execution. In addition to preventing dirty reading, non-repeatable reading, but also avoids phantom reading.

<tx:advice id= "Txadvice" transaction-manager= "TransactionManager" >

<tx:attributes>

<tx:method name= "*" propagation= "REQUIRED" isolation= "read_committed"/>

</tx:attributes>

</tx:advice>

11. Default transaction isolation level for several large databases

Isolation level Each database is different, if you do not specify this property, it is the default

• SQL Server:read commited
• Oracle:read commited
• Mysql:repeatable Read

Appendix I: Oracle's locks

Oracle locks fall into two main categories: data locks (DML locks) and dictionary locks. Dictionary locks include parsing and DDL locks, DBMS control, and user control.

Oracle 5 Data locks: Shared, exclusive, row-level shared (RS), row-level exclusive (RX-lock), shared row-level exclusive (SRX) lock. The lock granularity includes row level and table level.

Data Lock Compatibility Matrix:

S X RS RX SRX

S y n y n N

X n n n n N

RS y N y y y

RX n n y y N

SRX n n Y N n

In general, Oracle locks itself, and users can lock by using statements such as lock table.

Oracle By default, read data is not locked, but the rollback segment prevents dirty reads and guarantees repeatable reads.

Oracle has a deadlock check feature that periodically checks the system for deadlocks and, if there is a deadlock, revokes the transaction with the least number of update operations.

Appendix II: Testing of the JDBC transaction isolation level

View Plain

2. @Test
4. public void Testdbtransactionisolation () throws ClassNotFoundException,
6. SQLException {
8. Connection cn = getconnection ();
10. System.out.print ("The default transaction isolation level is:");
12. Printtransactionisolation (CN);
16. Settransactionisolation (CN, Connection.transaction_none,
18. "Transaction_none");
20. Settransactionisolation (CN, connection.transaction_read_uncommitted,
22. "transaction_read_uncommitted");
24. Settransactionisolation (CN, connection.transaction_read_committed,
26. "transaction_read_committed");
28. Settransactionisolation (CN, Connection.transaction_repeatable_read,
30. "Transaction_repeatable_read");
32. Settransactionisolation (CN, Connection.transaction_serializable,
34. "Transaction_serializable");
38. Cn.close ();
40. }
44. private void Settransactionisolation (Connection cn, int level,
46. String leveldiscription) {
48. try {
50. System.out.print ("SET TRANSACTION isolation level to:" + leveldiscription + ",");
52. Cn.settransactionisolation (level);
54. System.out.print ("Set success,");
56. Printtransactionisolation (CN);
58. } catch (Exception e) {
60. SYSTEM.OUT.PRINTLN ("Setup failed:" + e.getmessage ());
62. }
64. }
68. private void Printtransactionisolation (Connection cn) throws SQLException {
70. int level = Cn.gettransactionisolation ();
72. if (level = = Connection.transaction_none)
74. System.out.println ("Transaction_none");
76. else if (level = = connection.transaction_read_uncommitted)
78. System.out.println ("transaction_read_uncommitted");
80. else if (level = = connection.transaction_read_committed)
82. System.out.println ("transaction_read_committed");
84. else if (level = = Connection.transaction_repeatable_read)
86. System.out.println ("Transaction_repeatable_read");
88. else if (level = = connection.transaction_serializable)
90. System.out.println ("transaction_serializable");
92. }

Enterprise Application architecture Model-concurrency, transactions, locks