Considerations for SQL Server Transaction Processing

During project development, we should be familiar with database transactions, but how can we use transactions well?

I would like to share some of the following notes with you:

1. For the business to be processed, the transaction operation time should be as short as possible. Keeping the transaction open will lock the resource for a long period of time, which will block other users from performing operations or reading data.

2. Minimize the resources locked by transactions. For example, only tables related to the transactions performed by the demon are updated. If data modifications are logically dependent on each other, they should belong to the same transaction. Otherwise, irrelevant updates should be in their own transactions.

3. Only add related T-SQL operations to the transaction. Do not add additional searches or updates that are irrelevant to a transaction. Executing the SELECT statement in a transaction locks the associated table, which causes other users/sessions to be unable to execute or read data.

4. Do not open new things that need to wait for the user or external feedback in the transaction. Opening a transaction will lock the resource, and user feedback may need to be completed for a certain period of time. Therefore, user feedback should be collected before transactions are displayed.