SELECT @red=COUNT(*) FROM Authority WHERE roomID=@roomID AND authorityID=2
        IF(@red>19)
            RETURN 2--管理员数目满了直接返回

USE CSDN
go

CREATE TABLE Authority 
(
    ID INT IDENTITY,
    [NAME] VARCHAR(10),
    [PASSWORD] VARCHAR(10)
)
GO

--#1.用表锁
BEGIN TRAN
    DECLARE @count INT
    SELECT @count=COUNT(*) FROM Authority WITH(TABLOCKX) --可以保证同时只有一个线程可以访问Authority表
    IF(@count < 20)
    BEGIN
        INSERT Authority
        SELECT 'test'+CAST(@count AS VARCHAR), '123456'
    END
COMMIT TRAN

------解决方案--------------------

SQL code
--#2.锁表影响的范围比较大，效率也不好。可增加一个辅助表来解决
CREATE TABLE LockTable
(
    TableName VARCHAR(100),
    tcount INT
)
INSERT LockTable VALUES('Authority', 0)
GO
--SQL如下:
BEGIN TRAN
    UPDATE LockTable
    SET tcount = tcount+1
    WHERE TableName = 'Authority' --此句放在一个事务中，保证了添加管理员进程必须排队
    
    DECLARE @count INT
    SELECT @count=COUNT(*) FROM Authority
    IF(@count < 20)
    BEGIN
        INSERT Authority
        SELECT 'test'+CAST(@count AS VARCHAR), '123456'
    END
COMMIT TRAN