Use Java and database to lock in two ways

Java Code plus lock

publicstatic synchronized int Generate (Stringtablename) {
Stringsql = "Select value from t_table_id where table_name=?";
Connectionconn = null;
preparedstatementpstmt = null;
Resultsetrs = null;
intvalue = 0;
try{
Conn= dbutil.getconnection ();
pstmt= conn.preparestatement (SQL);
Pstmt.setstring (1,tablename);
Rs= Pstmt.executequery ();
Rs.next ();
if (!rs.next ()) {
Thrownew runtimeexception ();
//                        }
Value= rs.getint ("value");
value++;
Modifyvaluefield (Conn,tablename,value);
}catch (Exceptione) {
E.printstacktrace ();
Thrownew runtimeexception ();
}finally{
Dbutil.close (RS);
Dbutil.close (PSTMT);
Dbutil.close (conn);
}
returnvalue;
}

Database Lock:

Use pessimistic lock to achieve synchronization
Add a lock to the SQL statement after the for update, when the query is locked, after the lock can not be queried. It can be queried by others after submission.
public static int Generate (String tableName) {
Pessimistic lock for update using Database
String sql = "Select value from t_table_id where Table_name=?" For update ";
Connection conn = null;
PreparedStatement pstmt = null;
ResultSet rs = null;
int value = 0;
try{
conn = Dbutil.getconnection ();
Set auto-commit to False
Dbutil.begintransaction (conn);
pstmt = conn.preparestatement (sql);
Pstmt.setstring (1, tableName);
rs = Pstmt.executequery ();
Rs.next ();
if (!rs.next ()) {
throw new RuntimeException ();
//            }
Value = Rs.getint ("value");
value++;
Modifyvaluefield (Conn,tablename,value);
Commit a transaction
Dbutil.committransaction (conn);
}catch (Exception e) {
E.printstacktrace ();
Rolling back a transaction
Dbutil.rollbacktranscation (conn);
throw new RuntimeException ();
}finally{
Dbutil.close (RS);
Dbutil.close (PSTMT);
Dbutil.resetconnection (conn);
Dbutil.close (conn);
}
return value;
}

Lock (Locking)
In the process of implementing business logic, it is often necessary to guarantee the exclusivity of data access. As in the final settlement of the financial system, we want to process the data for a cut-off point in time, and we do not want the data to change again during the settlement process (which may be a few seconds or maybe a few hours). At this point, we need some mechanism to ensure that the data will not be modified in the process of the outside, such a mechanism, in this case, the so-called "lock", that is, to our selected target data locked, so that it can not be modified by other programs. Hibernate supports two locking mechanisms: "Pessimistic lock (pessimistic Locking)" and "optimistic lock (optimistic Locking)", which is commonly referred to.

One: Pessimistic lock (pessimistic Locking)
Pessimistic locking, as its name implies, is a conservative attitude to the data being modified by the outside world (including other transactions currently in the system, as well as transactions from external systems), so that the data is locked during the entire data processing process. Pessimistic lock implementation, often rely on the database provided by the lock mechanism (also only the database layer provides a lock mechanism to truly guarantee the exclusivity of data access, otherwise, even in this system to implement the locking mechanism, there is no guarantee that the external system will not modify the data). A typical pessimistic lock call that relies on a database: SELECT * from account where name= ' Erica ' for update this SQL statement locks all records that meet the search criteria (name= "Erica") in the list. These records cannot be modified by the outside world before the transaction commits (the locks in the transaction are freed during transaction commits).

Two: Optimistic lock (optimistic Locking)
Relative pessimistic lock, the optimistic locking mechanism adopts a more relaxed locking mechanism. Pessimistic locking relies on the lock mechanism of the database in most cases, to ensure the maximum degree of exclusivity of the operation. But it comes with a lot of overhead for database performance, especially for long transactions, which are often unsustainable. As a financial system, when an operator reads a user's data and modifies it on the basis of the user's data being read (such as changing the user account balance), the pessimistic locking mechanism means that the entire operation (from the operator reads the data, starts the modification until the entire process of submitting the modification result, even includes the operation Staff to cook coffee in the middle of the time), database records are always locked, you can imagine, if faced with hundreds of thousands of concurrent, this situation will lead to what consequences. The optimistic locking mechanism solves this problem to some extent. Optimistic locks are mostly implemented based on the data version (versions) recording mechanism. What is a data version? is to add a version identity to the data, which is typically done by adding a "version" field to the database table in the version solution based on the database table. When the data is read, the version number is read together, and then the version number is added one after the update. At this point, the version data of the submitted data is compared to the current version information of the database table corresponding to the record, and if the submitted version number is greater than the current version number of the database table, it is updated, otherwise it is considered to be outdated data. For the example above to modify user account information, assume that there is a version field in the Account information table in the database, the current value is 1, and the Current Account balance field (balance) is $ $.
1: operator A reads it out at this time (version=1) and deducts $ ($100-$50) from its account balance.
2: During operator A's operation, operator B also reads this user information (version=1) and deducts $ $100-$20 from its account balance.
3: operator a completed the modification work, the data version number plus one (version=2), together with account deduction after the balance (BALANCE=$50), submitted to the database update, at this time because the submission version of the data is larger than the current version of database records, the data is updated, database record version updated to 2.
4: operator B completes the operation, also the version number plus a version=2) attempt to submit data to the database (balance=$80), but at this time than the database record version found that operator B submitted the data version number 2, the current version of the database record is 2, does not meet the "commit version must be greater than the The current version to perform an update ", so the submission of operator B is dismissed. This avoids the possibility of operator B overwriting operator A's results with the results of old version=1-based data modifications. As can be seen from the above example, the optimistic locking mechanism avoids the database lock-up overhead in a long transaction (both operator A and operator B do not locking the database data), which greatly improves the overall performance of the system under large concurrent volume. It should be noted that the optimistic locking mechanism is often based on the data storage logic in the system, so there are some limitations, as in the above example, because the optimistic locking mechanism is implemented in our system, the user balance update from the external system is not controlled by our system, it may cause dirty data to be updated into the database. In the system design phase, we should take full account of the possibility of these situations, and make appropriate adjustments (such as the optimistic locking policy implemented in the database stored procedures, external only open the data update path based on this stored procedure, rather than the database table directly to the public).

Use Java and database to lock in two ways