Discussion on concurrency test method of SQL Server database

1. The use of testing tools to simulate multiple end users concurrent testing;

The disadvantage of this test approach is that end users are often not directly connected to the database, but rather pass through one or more intermediary services, so it is not guaranteed to access the database or concurrency. Second, this test method needs to wait until the client program, the server-side program is complete to do;

2. Using test tools to write scripts, directly connected to the database for concurrent testing;

This method can guarantee the concurrent operation effectively, and can be tested by the database access program, and the test time is shortened greatly, and the test result is better.

The following is a demo program that uses robot to use the second test method for concurrent testing of the database.

First step: Create a demo program

Open SQL Server Query Analyzer and execute the following script in the SQL Server test database (script execution: CREATE table testtable and insert a record; Create stored procedure test):

if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ Test] ') and OBJECTPROPERTY (ID, N ' isprocedure ') = 1)
drop procedure [dbo]. [Test]

Go

if exists (SELECT * from dbo.sysobjects WHERE id = object_id (N ' [dbo].[ TestTable] and OBJECTPROPERTY (ID, N ' isusertable ') = 1)

drop table [dbo]. [TestTable]

Go

CREATE TABLE [dbo].　[TestTable] (

[TestID]　[INT] Null

[Counts]　[INT] Null

) on [PRIMARY]

Go

Insert into TestTable (testid,counts) VALUES (1,0)

Go

SET QUOTED_IDENTIFIER ON

Go

SET ANSI_NULLS on

Go

CREATE Procedure dbo. Test

As

DECLARE @count int

BEGIN Tran TEST

Select @count =countsfrom testtable where testid=1

Update TestTable setcounts= @count +1

if (@ @error >0) begin

Rollback Tran TEST

End ELSE begin

Commit Tran TEST

End

Go

SET QUOTED_IDENTIFIER OFF

Go

SET ANSI_NULLS on

Go