How to solve the concurrency problem when accessing the database

***************
Set Table1 (A, B, C)
A B C
A1 B1 C1
A2 B2 C2
A3 B3 C3

1) exclusive lock
Create two connections
Execute the following statement in the first connection:
Begin tran
Update Table1
Set a = 'A'
Where B = 'b2'
Waitfor delay '00: 00: 30' -- wait 30 seconds
Commit tran
Execute the following statement in the second connection
Begin tran
Select * From Table1
Where B = 'b2'
Commit tran

If the preceding two statements are executed at the same time, the SELECT query must wait 30 seconds until the update statement is executed.

2) shared lock
Execute the following statement in the first connection:
Begin tran
Select * From Table1 holdlock-holdlock artificial lock
Where B = 'b2'
Waitfor delay '00: 00: 30' -- wait 30 seconds
Commit tran

Execute the following statement in the second connection
Begin tran
Select a, c from Table1
Where B = 'b2'
Update Table1
Set a = 'A'
Where B = 'b2'
Commit tran

If the preceding two statements are executed simultaneously, the SELECT query in the second connection can be executed.
Update can only be executed 30 seconds after the shared lock in the first connection ends.

3) deadlock
Add Table2 (D, E)
D e
D1 E1
D2 E2
Execute the following statement in the first connection:
Begin tran
Update Table1
Set a = 'A'
Where B = 'b2'
Waitfor delay '00: 00: 30'
Update Table2
Set d = 'd5'
Where E = 'e1'
Commit tran

Execute the following statement in the second connection
Begin tran
Update Table2
Set d = 'd5'
Where E = 'e1'
Waitfor delay '00: 00: 10'
Update Table1
Set a = 'A'
Where B = 'b2'
Commit tran

At the same time, the system detects a deadlock and terminates the process.
--------------------------------------------------------------
Set implicit_transactions on -- the user must submit or roll back explicitly each time. Otherwise, when the user disconnects,
-- The transaction and all its data changes will be rolled back.

Set implicit_transactions off -- automatic submission mode. In the automatic submission mode, if each statement is successful
-- Submit the job.

1: As shown above

2: how to lock a row in a table

Execute in connection

SET transaction isolation level Repeatable read

Begin tran

Select * From tablename with (rowlock) Where id = 3

Waitfor delay '00: 00: 05'

Commit tran

If you execute

Update tablename set colname = '10' where id = 3 -- Wait 5 seconds.

Update tablename set colname = '10' where ID <> 3 -- immediate execution

2. Lock a table in the database

Select * from table with (holdlock)

Note: What is the difference between locking a database table?

Select * from table with (holdlock)
Other transactions can read tables, but cannot update or Delete tables.

Select * from table with (tablockx)
Other transactions cannot read, update, or delete tables.

Description of the "Lock options" function in the SELECT statement
SQL Server provides a powerful and complete locking mechanism to help achieve database system concurrency and high performance. You can use the default settings of SQL Server or the "Lock option" in the SELECT statement to achieve the expected results. This article describes the "Lock options" in the SELECT statement and related functions.
Function Description:
Nolock (no lock)
When this option is selected, SQL server does not apply any lock when reading or modifying data. In this case, the user may read the data in the uncommitted transaction or roll back, that is, the so-called "dirty data ".

Holdlock)
When this option is selected, SQL Server will keep the shared lock until the end of the entire transaction, instead of releasing it on the way.

Updlock)
When this option is selected, SQL server uses the modification lock to replace the shared lock when reading data, and keeps the lock until the entire transaction or command ends. This option ensures that multiple processes can read data at the same time, but only the process can modify data.

Tablock)
When this option is selected, SQL Server sets a shared lock on the entire table until the command ends. This option ensures that other processes can only read but cannot modify data.

Paglock)
This option is the default option. When selected, SQL server uses the share page lock.

Tablockx (exclusive table lock)
When this option is selected, SQL Server will lock the entire table until the command or transaction ends. This prevents other processes from reading or modifying table data.

-- Or lock yourself (more flexible control)

-- An Example of locking a record that can only be modified by a single user:

-- Create a test environment
-- Create a test table -- department table
Create Table Department (partition mentid int, name varchar (10 ))

-- Record lock table
Create Table lock (partition mentid int, DT datetime)

Go
-- Because getdate cannot be used in the function, a view is used to obtain the current time.
Create view v_getdate as select dt = getdate ()
Go
-- Create a custom function to determine whether the record is locked
Create Function f_chk (@ brief mentid INT)
Returns bit
As
Begin
Declare @ Re bit, @ DT datetime
Select @ dt = dt from v_getdate
If exists (select 1 from lock where else mentid = @ else mentid
And datediff (SS, DT, @ DT) <5)
Set @ Re = 1
Else
Set @ Re = 0
Return (@ Re)
End
Go

-- Data processing test
If DBO. f_chk (3) = 1
Print 'record locked'
Else
Begin
-- Query
Begin tran
Insert into lock values (3, getdate ())
Update Department set name = 'A' where partition mentid = 3
Delete from lock where initialize mentid = 3
Commit tran
End

-- Delete the test environment
Drop table Department
Drop view v_getdate
Drop function f_chk

If a deadlock occurs, check: 1: sp_who or sp_who22: Select * From sysprocesses where blocked <> 03: choose Enterprise Manager> Server> Administrative Tools> activity> current activity and kill it... In the process information, if there is a lock icon next to it, it indicates that the process is deadlocked, kill 4: SQL event probe, monitor it, it mainly refers to the deadlocks caused by processing. then proceed accordingly. use the new trace of the event probe to monitor your SQL Server pause... The best way is to check the cause of the lock, which is generally caused by yourCode.
 

After tracing debugging. I found the problem as follows:
1. When an operation reads a table. In this case, there is no problem if another table is read. If
When another operation writes the table. This will happen. When you click Save. Will continue.
Only one read operation Program . Exit the read Table interface. This will be saved successfully.
2. If you change the same environment to my notebook, there is no such problem. If it is placed on the IBM server, the above problems will occur.

existing questions:
1. Why does the notebook provide services (p41. 8 CPU. 256 m) No problem. Is there a problem with using IBM as a server?
2. If I put sqlca. Change autocommit = false to sqlca. Autocommit = true. This problem does not exist. But I think it solves this problem on the surface. The problem was not resolved from the root.
3. For multiple workstations. One uses sqlca. Autocommit = true. Is there a problem if another autocommit = false .? Is there a problem if I want to perform the rollback operation?
4. If there are two sqlserver2000 servers in the same CIDR Block, will they be affected?