Usually we are in progress, modify, delete, and query when we face not multiple users, usually do not need to consider the table lock and deadlock of the database, and the deadlock. But if we face a multi-user parallel network environment, we need more careful analysis and consideration to the problem of table lock, otherwise he will give us the trouble, and My problems encountered in this matter and the solution are shared with everyone. It is also in my development process: two users saved new data at the same time, and our program started to process CN.Begintrans Cn.execute "Insert Into Tablea ....." set RS = CN. Execute ("SELECT Count (*) from Tablea Where ...") IF r.RecordCount> 0 Then 'Table A Field A Cannot Never from CN. RollbackTrans Else Cn.committrans End IF
When SQL Server is executing the insert command If we do not add any parameters, the database defaults to apply for an IX lock to Table A. We will analyze the above program, when the first user performs CN.execute "Insert Into Tablea .... "Connection applies for an IX lock to the table A, at the same time, when the second user executes Cn.execute" INSERT INTO TABLEA ... "Connection, also successfully applied to the database, IX lock to the table A, but when the SET RS = CN.Execute ("Select Count (*) from Tablea Where ...") will have a problem, we assume that the first user will perform one step, because the select command needs Apply for a S lock to the table A to the database, but because the table A already has an IX lock and belongs to another connection, he has to wait here. The second user also executed SET RS = cn.execute ("Select Count (*) from Tablea Where ...") He also applies a S lock to Table A to the database, at this time, the data will be automatically ended. The connection between the evening application IX lock is rolled back to this business. This is a big failure for our application.
Solved approach, set data parameters to let us read the data that is not submitted,
Cn.begintrans cn.execute "set Transaction isolation Level Read Uncommitted" cn.execute "Insert Into Tablea ....." set = cn.execute ("Select Count (*) from Tablea Where ...") IF RS . Genecordcount> 0 THEN 'Table A Field A Cannot Never from CN. ROLLBACKTRANS ELSE CN.COMMITTRANS End if Cn.Execute "Set Transaction Isolation Level Read Committed"
Solution 2, set the insert command parameter with (Tablock),
Cn.Begintrans Cn.execute "Insert Into Tablea with (Tablock) ....." SET RS = CN.EXECUTE ("Select Count (*) from Tablea Where ...") ife.recordcount> 0 Then 'table A Field A Can not be resolved from CN. RollbackTrans else cn.commmitTrans End IF three, add a useless lock table, cn.begintrans cn.execute "Update TmpLockTable Set Fieldlock = 1" cn.execute "Insert Into Tablea with (Tablock ..... "SET RS = CN.EXECUTE (" Select Count (*) from Tablea Where ... ") IF f rRecordCount> 0 Then 'Table A Field A Cannot Never from CN. ROLLBACKTRANS ELSE Cn.committrans endiff
