关于查询更新是锁的问题

cc_net 2014-09-23 12:29:26

现在有一个单机WPF程序来解析文件，然后把文件中的数据插入到数据库中，数据库是SQL SERVER 2008 R2。

数据库的表中大概是这样的结构 SEQ_ID, DATA, STAUS

其中SEQ_ID是自增或GUID，DATA存放文件解析出来的数据，STATUS表示记录的状态，插入数据时，如果数据库中存在相同DATA的记录，STATUS = 1，如果不存在相同的记录STATUS = 0.

程序采用多线程来解析文件并写入数据库，如果按照默认的数据库隔离级别，默认的锁，程序也不做同步处理，那么在插入数据库应该会出现：多个线程处理文件内容相同，在插入数据库时，都发现表中没有记录，那么就会有多条STATUS = 0，但DATA相同的记录。

目前解决办法：
1. 调高数据库的隔离级别，但是这样会影响其他程序，如WEB程序对数据库的查询等操作。
2. 把SELECT和UPDATE操作放到一个事物中处理，对事务的SELECT加上TABLOCKX独占锁，这样其他所有对这个表操作，应该都会阻塞等待。
3. 把SELECT和UPDATE操作放到一个事物中处理，并对此事务的SELECT 加上UPDLOCK更新锁，这样对于其他线程也执行这个事务时会阻塞等待。而对于其他没有加UPDLOCK锁的SELECT，会继续执行。
4. 在我程序中对多线程进行同步，对DB操作的地方加上lock锁，进行同步。这样好处就是对数据库写入是有序的，不会阻塞其他操作。

个人觉得3,4应该都可以行，就目前情况来说，因为是单机程序，对于处理文件和插入数据库的速度要求不是很高，所以采用4应该问题不到。而外部WEB程序只会对这个表进行SELECT查询，所以用方法3，其实也不会有什么问题。

想了解一下，那种方式会比较好。如果不是单机程序，是多个机器同时跑，应该就只能采用方法3了吧。或者还有什么其他比较好的方法？

还有个问题，对于方法3，对SELECT使用UODLOCK锁和在SELECT前使用update tab set status = 0 where 1 = 2；这种效果是否一样，效率上会有区别吗？

...全文

249 19 打赏收藏转发到动态举报

写回复

用AI写文章

19 条回复

切换为时间正序

请发表友善的回复…

发表回复

Tiger_Zhao 2014-09-29

打赏
举报

INSERT 之后就有了锁，再 UPDATE 就要判断锁。A、B 双方在都是先加锁、再等待对方的解锁时就是死锁。
而用 SELECT WITH LOCK 先加锁，则一人占先、其他人等待。

先INSERT后UPDATE方案的好处是通用性，一般多用户数据库都支持。
SELECT WITH LOCK 就不一定支持了。

cc_net 2014-09-26

打赏
举报

引用 17 楼 Tiger_Zhao 的回复:

上来先加锁的确可以避免死锁。给 DATA 单独加个索引。

还是采用了lock+事务的方式。只是事务中，先SELECT COUNT, 如果=0，就status = 0, 否则为1. 目前测试数据库中有500W条记录。数据库中执行这个存储过程基本1,2ms。如果使用先insert在update需要700,800ms。这个速度远远超出了lock的代价。不过我不理解，为什么先insert在update会导致死锁。

cc_net 2014-09-23

打赏
举报

引用 9 楼 Tiger_Zhao 的回复:

[quote=引用 6 楼 cc_net 的回复:]非常感谢你的回答。 Insert是独占锁，所以如果一个线程在执行这个事务时，其他线程应该是无法执行这个插入操作的。那么如果这个时候有WEB程序来进行查询，是不是也会阻塞？

插入、更新都是很快的操作，数据库会管理排队，WEB程序没感觉。[/quote]

引用 7 楼 alimake 的回复:

感觉用个临时表行不行。临时表把DATA设置为主键。写到临时表的时候STASUS都是1，然后插入到正式表的时候在进行更新操作。

刚我在SQL SERVER控制台试验了一下，在SQL中加入了waitfor delay ‘00:00:10’ 看看效果。 1. 使用Tiger_Zhao 给的SQL，先insert在update，在整个过程中，SELECT会被阻塞，直到commit。 2. 使用SELECT WITH UPDLOCK，其他SELECT被阻塞，直到commit。 3. 使用Update WHERE 1=2 ，其他SELECT不会阻塞。效果来看1，2是完全一样的。但是方法1比较方便。有一点不太明白，为什么SELECT时使用UPDLOCK锁也会导致其他的SELECT阻塞等待？看解释是：使用UPDLOCK的作用是允许用户先读取数据（而且不阻塞其他用户读数据），并且保证在后来再更新数据时，这一段时间内这些数据没有被其他用户修改。那么和排他锁TABLOCKX有什么区别呢？

Tiger_Zhao 2014-09-23

打赏
举报

引用 8 楼 alimake 的回复:

[quote=引用 5 楼 Tiger_Zhao 的回复:] [quote=引用 4 楼 alimake 的回复:] [quote=引用 3 楼 Tiger_Zhao 的回复:] [quote=引用 2 楼 alimake 的回复:]2个人同时更新可能吗？能解决吗

有什么不可能？请举例说明。[/quote] 有2个用户A ,B 插入和更新分别是事务1 ，2 2个人都插入成功没问题。现在A2，B2这2个事务运行。不是都更新为1了吗。貌似这里面第一个记录应该是0. [/quote] 事务没结束之前，EXISTS判断能看到对方的记录？[/quote] 你插入和更新不是2个事务吗。A B2个人的插入操作都完成了啊然后进行2个人的更新操作。这样不会都会更新为1吗？[/quote] 事务≠语句

Tiger_Zhao 2014-09-23

打赏
举报

引用 6 楼 cc_net 的回复:

非常感谢你的回答。 Insert是独占锁，所以如果一个线程在执行这个事务时，其他线程应该是无法执行这个插入操作的。那么如果这个时候有WEB程序来进行查询，是不是也会阻塞？

插入、更新都是很快的操作，数据库会管理排队，WEB程序没感觉。

xiaodongni 2014-09-23

打赏
举报

引用 5 楼 Tiger_Zhao 的回复:

[quote=引用 4 楼 alimake 的回复:] [quote=引用 3 楼 Tiger_Zhao 的回复:] [quote=引用 2 楼 alimake 的回复:]2个人同时更新可能吗？能解决吗

xiaodongni 2014-09-23

打赏
举报

感觉用个临时表行不行。临时表把DATA设置为主键。写到临时表的时候STASUS都是1，然后插入到正式表的时候在进行更新操作。

cc_net 2014-09-23

打赏
举报

引用 5 楼 Tiger_Zhao 的回复:

[quote=引用 4 楼 alimake 的回复:] [quote=引用 3 楼 Tiger_Zhao 的回复:] [quote=引用 2 楼 alimake 的回复:]2个人同时更新可能吗？能解决吗

有什么不可能？请举例说明。[/quote] 有2个用户A ,B 插入和更新分别是事务1 ，2 2个人都插入成功没问题。现在A2，B2这2个事务运行。不是都更新为1了吗。貌似这里面第一个记录应该是0. [/quote] 事务没结束之前，EXISTS判断能看到对方的记录？[/quote] 非常感谢你的回答。 Insert是独占锁，所以如果一个线程在执行这个事务时，其他线程应该是无法执行这个插入操作的。那么如果这个时候有WEB程序来进行查询，是不是也会阻塞？

Tiger_Zhao 2014-09-23

打赏
举报

引用 4 楼 alimake 的回复:

[quote=引用 3 楼 Tiger_Zhao 的回复:] [quote=引用 2 楼 alimake 的回复:]2个人同时更新可能吗？能解决吗

有什么不可能？请举例说明。[/quote] 有2个用户A ,B 插入和更新分别是事务1 ，2 2个人都插入成功没问题。现在A2，B2这2个事务运行。不是都更新为1了吗。貌似这里面第一个记录应该是0. [/quote] 事务没结束之前，EXISTS判断能看到对方的记录？

xiaodongni 2014-09-23

打赏
举报

引用 3 楼 Tiger_Zhao 的回复:

[quote=引用 2 楼 alimake 的回复:]2个人同时更新可能吗？能解决吗

有什么不可能？请举例说明。[/quote] 有2个用户A ,B 插入和更新分别是事务1 ，2 2个人都插入成功没问题。现在A2，B2这2个事务运行。不是都更新为1了吗。貌似这里面第一个记录应该是0.

Tiger_Zhao 2014-09-23

打赏
举报

引用 2 楼 alimake 的回复:

2个人同时更新可能吗？能解决吗

有什么不可能？请举例说明。

xiaodongni 2014-09-23

打赏
举报

引用 1 楼 Tiger_Zhao 的回复:

不需要SELECT语句的，一般隔离级别的事务用下面的语句就不会有问题

--先插入 STAUS = 0
INSERT INTO table1 (DATA, STAUS) VALUES (@data, 0)
--取得ID
SET @seq_id = @@IDENTITY
--如果存在其他相同DATA的记录，更改 STAUS = 1
UPDATE SET STAUS = 1
  FROM table1
 WHERE SEQ_ID = @seq_id
   AND EXISTS (SELECT *
                 FROM table1 t
                WHERE t.DATA = @data
                  AND t.SEQ_ID <> @seq_id
              )

2个人同时更新可能吗？能解决吗

Tiger_Zhao 2014-09-23

打赏
举报

不需要SELECT语句的，一般隔离级别的事务用下面的语句就不会有问题

--先插入 STAUS = 0

INSERT INTO table1 (DATA, STAUS) VALUES (@data, 0)

--取得ID

SET @seq_id = @@IDENTITY

--如果存在其他相同DATA的记录，更改 STAUS = 1

UPDATE SET STAUS = 1

  FROM table1

 WHERE SEQ_ID = @seq_id

   AND EXISTS (SELECT *

                 FROM table1 t

                WHERE t.DATA = @data

                  AND t.SEQ_ID <> @seq_id

              )

Tiger_Zhao 2014-09-23

打赏
举报

上来先加锁的确可以避免死锁。
给 DATA 单独加个索引。

cc_net 2014-09-23

打赏
举报

引用 15 楼 Tiger_Zhao 的回复:

表真的只有 SEQ_ID, DATA, STAUS 三个字段？那么同一个 DATA 记多条有什么意义？还不如一个 DATA 一条记录，记重复次数好处理点。

不是，里面有大概5个字段的数据。我在程序中加上lock，就OK，但是多个同时操作，速度比较慢。我改用程序lock + SELCET,INSET 的存储过程，速度反倒快

Tiger_Zhao 2014-09-23

打赏
举报

表真的只有 SEQ_ID, DATA, STAUS 三个字段？
那么同一个 DATA 记多条有什么意义？
还不如一个 DATA 一条记录，记重复次数好处理点。

cc_net 2014-09-23

打赏
举报

System.Data.SqlClient.SqlException (0x80131904): Transaction (Process ID 62) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. 数据库操作代码


           using (SqlConnection conn = new SqlConnection(strConn))
            {
                using (SqlCommand cmd = new SqlCommand(strSQL, conn))
                {
                    cmd.CommandType = cmdType;
                    if (paras != null)
                    {
                        cmd.Parameters.AddRange(paras);
                    }
                    conn.Open();
                    ret = cmd.ExecuteNonQuery();
                }
            }

cc_net 2014-09-23

打赏
举报

引用 12 楼 Tiger_Zhao 的回复:

因为你的 WHERE 条件是 DATA 而不是 SEQ_ID，只能全表搜索，只要有一条被锁就得等。 WITH UPDLOCK 只能在大家都用 SEQ_ID 条件时才能降低冲突。

存储过程大概是这样，但是当有2个线程同时执行时，会报死锁的exception。



ALTER PROCEDURE [dbo].[insertInstallData]
	-- Add the parameters for the stored procedure here
	@data nvarchar(40),
	@status int
AS
BEGIN
	SET XACT_ABORT ON
	BEGIN TRAN
	
	DECLARE @seq_id int

	--Insert data to table with status
	INSERT INTO [dbo].[App_Setup_List]
			   ([DATA],[STATUS]
			  VALUES
			   (@data, @status);
	          
    --Modify the status from 0 to 1, if have the same records.
	IF @status = 0
	BEGIN
		--get seq_id
		SET @seq_id = @@IDENTITY

		--update status to 1 if have the same item
		UPDATE [dbo].[App_Setup_List]
		SET [STATUS] = 1
		WHERE SEQ_ID = @seq_id
		AND EXISTS (SELECT * FROM XXX tab
				WHERE tab.DATA= @data AND tab.SEQ_ID <> @seq_id)
	END

	--Commit the tran
	COMMIT TRAN

END

Tiger_Zhao 2014-09-23