Workaround for Database deadlock

Recently in the blog site, the reply to other people's database deadlock to avoid problems, has been a few times before the same content, I feel it is necessary to converge into a blog post, convenient for everyone.

The approach here is applicable to all databases.

The workaround steps are as follows:

1. Add Updated_count (integer) field in each table

2. Add a row of data, Updated_count = 0:
Insert INTO table_x (f1,f2,..., update_count) VALUES (..., 0);

3. Obtain a row of data SQL from the primary key, encapsulated into a DAO function (my custom is to have one UUID field per table key.) From the combination of the primary key, the combination of the primary key in the multi-table join SQL is cumbersome to write, do not use the user input business Data key, because the user input data can be wrong, and then to change, not suitable for the master key.
SELECT * from table_x where pk =?

4. Delete a row of data
4.1 Get this row data first by primary key, see 3.

4.2 Delete from table_x where pk =? and update_count=? Where the update_count in the where is obtained by 4.1
4.3 Check 4.2 Execution affects the number of rows of data, if the deletion fails, someone else has deleted or updated the same row of data, throw exceptions, at the outermost rollback, and through the appropriate words to remind users of concurrent operations, please wait and try again.
int count = cmd. ExecuteNonQuery ();
if (Udpatedcount < 1) {
throw new Exception ("detected concurrent operation, to prevent deadlock, has abandoned the current operation, please try again later, table XXX, Data Key ....");
}

5. Update a row of data
5.1 Get this row data first by primary key, see 3.
5.2 Update table_x set f1=?,f2=?, ..., update_count=update_count+1 where pk =? and update_count=? Where the update_count in the where is obtained by 5.1
5.3 Check 5.2 execution affects the number of rows of data, if the update fails, someone else has deleted or updated the same row of data, throw an exception, at the outermost rollback, and through the appropriate words to remind the user to have concurrent operations, please wait and try again.
int count = cmd. ExecuteNonQuery ();
if (Udpatedcount < 1) {
throw new Exception ("detected concurrent operation, to prevent deadlock, has abandoned the current operation, please try again later, table XXX, Data Key ....");
}

6. Database access layer in DAO, never write try catch, nor write Commit/rollback. Because when I wrote a dao1.insert (XXX), another person wrote Dao2.insert (XXX), two weeks later it was possible that the two functions would be grouped together in a single transaction. If Dao1.insert (XXX) has been commit, then Dao2.insert (XXX) rollback will not achieve the desired effect. There are many examples of computer books that have this error.

The database transaction should define the starting range as follows:

6.1 Standalone program, each button operation, corresponding to a transaction. You can pass the connection/transaction to the DAO. The transaction is processed at the code where the button responds. Catch to any Exception to rollback.

6.2 Web version of the program, each button operation, corresponding to a transaction. You can pass the connection/transaction to the DAO. The transaction is processed at the code where the button responds. I strongly recommend that for Web applications, the opening/closing of database connections, the start of database transactions, and Commit/rollback are all handled in filter (both Java EE and ASP. NET MVC have filter, others do not know), transactions, database connections through thre The adlocal is passed into the DAO. The catch to any Exception in the filter is rollback.

Seen a lot of people with Spring, the code started a few database transactions themselves do not know, the characters do not meet their own needs, do not know. My recommendation is to prohibit the use of Spring to manage database transactions.

7. Single-table increase, deletion, change, through the primary key search, should be automatically generated using tools. Automatically generated code, should be placed in a separate directory, so that later database table changes, you can regenerate the code and overwrite. Automatically generated files, in the first line to write a note, indicating that this is an auto-generated file, will be automatically overwritten later, so do not change this file.

For example, for Tm_system_user tables, you can automatically generate Tmsystemuserdao, which includes functions: Insert (Tmsystemuser), update (tmsystemuser), delete ( Tmsystemuser), Getbykey (key), Batchinsert (tmsystemuser[]).

8. Always use transactions and readcommited level, even if it is pure query SQL, also write this. This simplifies the design and writing of code, and does not reveal significant excess performance consumption.

9. When designing the data, try to avoid update/delete. For example, if it is a leave-of-absence approval process, the request is designed as a table, and the leader approves the design of another table. Try to avoid merging the design into a single table, the Approval status (consent/veto), approval time as a "request for leave" attribute.
Say extreme, preferably from the database design, avoid the subsequent programming has update/delete, only insert. It seems that the popular NoSQL is the same idea.

10. Supplement, if the background check page input data, error handling, there are the following two ways:

10.1 To have one error, throw exception.

10.2 All errors are detected, for example, the user name is not entered, e-mail is not entered, placed in a list, and then throw exception.

2012-3-30, because many netizens on the database deadlock know not deep, even some netizens, do not know the database will deadlock, special supplement some information. The following is an excerpt from "LINQ Combat":
8.1.1 Pessimistic concurrency

Before. NET appears, many applications need to manage their own connections to the database. In these systems, developers often lock up a record after acquiring it to block changes that other users might make at the same time. This type of locking strategy is called pessimistic concurrency. Pessimistic concurrency may not be a problem for some small Windows desktop programs, but the overall performance of the system will quickly be dragged down if the same strategy is used in a large number of user-wide systems.

As the scale of the system expands, the problem of scalability begins to surface. As a result, many systems migrate from a client-server architecture to a Web-based application with less state information, which also reduces the cost of deployment. A stateless Web application also makes the overly conservative pessimistic concurrency policy useless.

In order for developers to avoid falling into the scalability and lock-up morass of pessimistic concurrency, the. NET framework has been designed to take into account the offline nature of Web applications. NET and the API provided by ADO cannot lock a data table, which naturally ends the possibility of pessimistic concurrency. However, if necessary, the application can also add a "checkout" label to a record at the same time as the first time, so that the "checkout" condition is obtained when the second attempt is made and the corresponding processing is done as necessary. In many cases, however, it is difficult to determine whether the user is no longer using the label, so the Checkout tab is often left in a non-reset state. Because of this, pessimistic concurrency in the offline program is also used less frequently.

8.1.2 Optimistic concurrency

Because programs in off-line environments are often not suitable for pessimistic concurrency, another strategy for dealing with it is that optimistic concurrency is gradually appearing in people's eyes. Optimistic concurrency allows any number of users to modify copies of their own data at any time. When you commit the changes, the program checks to see if the previous data has changed. If there is no change, the program only needs to save the changes. If there is a change and there is a conflict, then the program will decide whether to overwrite the previous modification, discard the new modification, or attempt to merge two changes.

The first half of optimistic concurrency is relatively straightforward. In cases where concurrency checking is not required, the SQL statement used in the database will resemble the following syntax: UPDATE TABLE SET [field = value] WHERE [Id = value]. in optimistic concurrency, however, the WHERE clause will not only include the ID column, but also compare whether the other columns in the table are the same as the original values.

In code listing 8-1, we finally checked the rowcount to see if the update was successful. If ROWCOUNT is 1, it indicates that the original record was not updated by others during the user's modified period, that is, the update was successful. If rowcount is 0, it means someone has modified the record during the period. At this point the record will not be updated, and the program can also tell the user about the conflict and, if necessary, perform the appropriate action ...

2012-3-31 Supplement:

The timestamp (timestamp) in Oracle differs greatly from SQL Server. Timestamp in SQL Server is stored in a binary format in a database, and you can set this field type in the dataset to the base64binary type. The timestamp in Oracle is stored in a time format.

SQL Server has a function called Current_timestamp, with the TIMESTAMP data column type in SQL Server, without a dime relationship.

The individual thinks that the timestamp data column type in SQL Server belongs to "noun disorderly use", and the timestamp that the general person understands is not a meaning. Continue to look up from the Internet, sure enough to find:

The Transact-SQL timestamp data type differs from the timestamp data type defined in the SQL-92 standard. The SQL-92 timestamp data type is equivalent to the Transact-SQL datetime data type.
Future versions of Microsoft SQL Server may modify the behavior of the Transact-SQL timestamp data type so that it is consistent with the behavior defined in the standard. By then, the current timestamp data type will be replaced with the rowversion data type.
Microsoft SQL Server 2000 introduced the rowversion synonym for the timestamp data type. Use rowversion as much as possible in DDL statements instead of using timestamp. RowVersion is subject to the behavior of data type synonyms.

I don't see any relationship between SQL Server timestamp and database deadlock!!! even Microsoft LINQ for SQL Auto-generated code is not used for timestamp, but rather uses the technology in this blog post. If there is a user to provide information, the SQL Server timestamp and database deadlock a little relationship, will be greatly appreciated.

Workaround for Database deadlock