Database deadlock Solution

Recently, on the blog site, I have replied to other users' database deadlocks to avoid problems. I have replied to the same content several times before, and I think it is necessary to converge into a blog.ArticleFor your convenience.

This method applies to all databases.

 

The steps for this solution are as follows:

1. Add the updated_count (integer) field to each table.

 

2. Add a new row of data, updated_count = 0:
Insert into table_x (F1, F2,..., update_count) values (..., 0 );

 

3. Obtain a row of Data SQL based on the primary key and encapsulate it into a DaO function (my habit is to use a uuid field in each table as the primary key. The combination of primary keys is never used. It is difficult to write SQL statements when the combination of primary keys is joined in multiple tables. You do not need to input the business data as the primary key because any data you enter may be wrong, and then you need to change it. It is not suitable for primary keys ).
Select * From table_x where PK =?

 

4. delete a row of data
4.1 first obtain the data of this row through the primary key, as shown in figure 3.

4.2 Delete from table_x where PK =? And update_count =? In the WHERE clause, update_count is obtained through 4.1.
4.3 check 4.2 execution affects the number of rows of data. If the deletion fails, another user has deleted or updated the same row of data. If an exception is thrown, It is rollback on the outermost side, use appropriate words to remind users of concurrent operations. Please try again later.
Int COUNT = cmd. executenonquery ();
If (udpatedcount <1 ){
Throw new exception ("concurrent operations are detected. To prevent deadlocks, the current operation has been abandoned. Please try again later, Table XXX, data key ....");
}

 

5. Update a row of data
5.1 first obtain the data of this row through the primary key, as shown in figure 3.
5.2 update table_x set F1 = ?, F2 = ?, ..., Update_count = update_count + 1 where PK =? And update_count =? In the WHERE clause, update_count is obtained through 5.1.
5.3 check 5.2 execution affects the number of rows of data. If the update fails, the data in the same row has been deleted or updated by another user. if an exception is thrown, the data in the outermost rollback is returned, use appropriate words to remind users of concurrent operations. Please try again later.
Int COUNT = cmd. executenonquery ();
If (udpatedcount <1 ){
Throw new exception ("concurrent operations are detected. To prevent deadlocks, the current operation has been abandoned. Please try again later, Table XXX, data key ....");
}

 

6. in the database access layer Dao, do not write try catch or commit/rollback. because when I write a dao1.insert (XXX) and another person writes a dao2.insert (XXX), two weeks later someone may combine these two functions in a transaction. If dao1.insert (XXX) has been commit, rollback in dao2.insert (XXX) will not achieve the expected results. Examples in many computer booksCode.

The starting range of database transactions should be defined as follows:

6.1 single-host EditionProgramEach button operation corresponds to a transaction. You can pass connection/transaction to Dao. Process the transaction in the response code. Rollback is required for catch to any exception.

6.2 web edition program, each button operation, corresponds to a transaction. You can pass connection/transaction to Dao. Process the transaction in the response code. For Web applications, I strongly recommend that you enable/disable database connections, start database transactions, and commit/rollback all be processed in the filter (Java EE and ASP. net MVC has filters, others do not know), transactions, database connections are passed into Dao through threadlocal. If any exception is caught in the filter, rollback is required.

 

I have seen many people who use spring. I don't know how to start several database transactions in the code. I don't know whether the operators meet my own needs. My suggestion is that you cannot use spring to manage database transactions.

 

7. You should use tools to automatically generate a single table by adding, deleting, modifying, and querying through the primary key. The automatically generated code should be placed in a separate directory so that the database table changes can be followed, and the code can be re-generated and overwritten. The automatically generated file is annotated in the first line, indicating that this is an automatically generated file and will be automatically overwritten in the future. Therefore, do not change this file.

For example, for the table tm_system_user, The tmsystemuserdao can be automatically generated, including the functions: insert (tmsystemuser), update (tmsystemuser), delete (tmsystemuser), getbykey (key ), batchinsert (tmsystemuser []).

8. transactions are always used and written at the readcommited level, even for pure SQL queries. This can simplify the design and write code, without significant excess performance consumption.

 

9. During data design, try to avoid update/Delete. For example, if it is an application form approval process, design the application form as one table and design the approval form as another table. Avoid merging data into a table during design, and regard the approval status (consent/rejection) and approval time as the attribute of "Application Form.
To be more extreme, it is best to design a database to avoid the use of update/delete in subsequent programming and insert only. It seems that the popular nosql is also like this.

 

10. Additionally, if you enter data on the background check page and report an error, you can use either of the following methods:

10.1 throw exception as long as there is an error.

10.2 all errors are detected. For example, if the user name is not input and the email is not input, put the error in a list and throw exception.

 

, Many netizens did not know much about database deadlocks, or even some netizens did not know that the database would be deadlocked. Some additional information is required. The following content is excerpted from "real-time writing with LINQ":
8.1.1 pessimistic concurrency

Before. Net appeared, many applications needed to manage the connection with the database on their own. In these systems, developers often lock a record after obtaining it to prevent other users from making modifications at the same time. Such locking policies are called pessimistic concurrency. Pessimistic concurrency may not be a problem for some small windows desktop programs, but if the same policy is used in many large systems of users, then the overall system performance will soon be dragged down.

With the expansion of the system, the scalability problem began to emerge. Therefore, many systems migrate from the client-server architecture to Web-based applications with less state information, which also reduces deployment costs. Stateless web applications also make too conservative and pessimistic concurrency policies useless.

To prevent developers from getting stuck in the scalability and locking caused by pessimistic concurrency,. NET Framework considers the offline features of Web applications at the beginning of design .. The APIS provided by net and ADO. Net cannot lock a data table, which naturally ends the possibility of pessimistic concurrency. However, if necessary, the application can also add a "check-out" tag for a record while obtaining it for the first time, so that during the second access attempt, you can obtain the "check-out" Information and handle it as needed. However, in many cases, it is difficult to determine whether the user no longer uses this label, so the "check out" label is often in the unreset status. Because of this, the usage frequency of pessimistic concurrency in offline programs is getting lower and lower.

8.1.2 Optimistic Concurrency

Because programs in offline environments are often not suitable for pessimistic concurrency, another processing strategy, that is, optimistic concurrency gradually appears in the eyes of people. Optimistic Concurrency allows any number of users to modify a copy of their own data at any time. When submitting changes, the program will check whether 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, the program will decide whether to overwrite the previous modification, discard the new modification, or try to merge the two changes according to the actual situation.

The first half of Optimistic Concurrency operations are relatively simple. Without concurrent checks, the SQL statements used in the database are similar to the following syntax: Update table set [field = value] Where [ID = value].However, in optimistic concurrency, the where clause will not only contain the ID column, but also compare whether other columns in the table are the same as the original values.

In code list 8-1, we check rowcount to check whether the update is successful. If rowcount is 1, it indicates that the original record is not updated by others during the user's modification, that is, the update is successful. If rowcount is 0, it means someone modified the record during the period. At this time, the record will not be updated, and the program will be able to inform the user of the conflict information, and perform appropriate operations as needed...

Supplement:

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

SQL Server has a function named current_timestamp, which has no relation to the timestamp data column type in SQL Server.

I personally think that the timestamp data column type in sqlserver is a term that is not commonly understood. I continued searching on the Internet and found that:

The Transact-SQL timestamp data type is different 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.
In future versions, Microsoft SQL Server may modify the behavior of the transact-SQL timestamp data type to make it consistent with the behavior defined in the standard. By that time, the current timestamp data type will be replaced by the rowversion data type.
Microsoft SQL Server 2000 introduces the rowversion synonym of the timestamp data type. Try to use rowversion instead of timestamp in DDL statements. Rowversion is restricted by the behavior of Data Type synonyms.

The relationship between SQL Server Timestamp and database deadlock is not seen !!!Even the code automatically generated by Microsoft LINQ for SQL does not use timestamp, but uses the technology in this blog. Which of the following statements about the relationship between SQL Server Timestamp and database deadlock is appreciated.