实际上Select是不锁表的,只有Update,Insert,delete是锁表的 一个使用事物的实例(存储过程) CREATE PROCEDURE SP_CLUB_GOMYCLUB @user_number int, @user_id varchar(20) AS BEGIN --设置存储过程的脱离时间 SET LOCK_TIMEOUT 1800 BEGIN TRANSACTION SELECT club_code,club_name FROM club_common_member WITH(NOLOCK) WHERE club_mem_n o = @user_number and club_mem_id =@user_id UNION SELECT club_code,club_name FROM club_game_member WITH(NOLOCK) WHERE club_mem_no = @user_number and club_mem_id = @user_id UNION SELECT club_code,club_name FROM club_favor_member WITH(NOLOCK) WHERE club_mem_no = @user_number and club_mem_id = @user_id IF @@ERROR <> 0 OR @@ROWCOUNT = 0 BEGIN ROLLBACK TRANSACTION END ELSE BEGIN COMMIT TRANSACTION END END GO
使用存储过程 + 事物处理
并且,建立日志文件,LOG数据库
一个使用事物的实例(存储过程)
CREATE PROCEDURE SP_CLUB_GOMYCLUB
@user_number int,
@user_id varchar(20)
AS
BEGIN
--设置存储过程的脱离时间
SET LOCK_TIMEOUT 1800
BEGIN TRANSACTION
SELECT club_code,club_name FROM club_common_member WITH(NOLOCK) WHERE club_mem_n
o = @user_number and club_mem_id =@user_id
UNION
SELECT club_code,club_name FROM club_game_member WITH(NOLOCK) WHERE club_mem_no
= @user_number and club_mem_id = @user_id
UNION
SELECT club_code,club_name FROM club_favor_member WITH(NOLOCK) WHERE club_mem_no
= @user_number and club_mem_id = @user_id
IF @@ERROR <> 0 OR @@ROWCOUNT = 0
BEGIN
ROLLBACK TRANSACTION
END
ELSE
BEGIN
COMMIT TRANSACTION
END
END
GO