在MS-SQL中防止条件INSERT / UPDATE竞争条件
技术问答
228 人阅读
|
0 人回复
|
2023-09-12
|
|
我想知道我是否遵循正确的方法，需要你的帮助才能弄清楚
4 q! t2 Z5 B3 u* e' t: b这是我不受保护的查询2 W" c7 `( b4 P* E
DECLARE @cl_WordId bigint = NULLSELECT @cl_WordId = cl_WordIdFROM tblWordsWHERE cl_Word = @cl_WordAND cl_WordLangCode = @cl_WordLangCodeIF (@cl_WordId IS NULL)BEGIN INSERT INTO tblWords (cl_Word,cl_WordLangCode,cl_SourceId) VALUES (@cl_Word,@cl_WordLangCode,@cl_SourceId) SET @cl_WordId = SCOPE_IDENTITY() SELECT @cl_WordIdENDELSEBEGIN SELECT @cl_WordIdEND为了保护它，我将其修改如下
1 z! w0 u4 P6 L, g) B2 G: i5 u T2 i4 \DECLARE @cl_WordId bigint = NULLSELECT @cl_WordId = cl_WordIdFROM tblWords WITH (HOLDLOCK)WHERE cl_Word = @cl_WordAND cl_WordLangCode = @cl_WordLangCodeBEGIN IF (@cl_WordId IS NULL) BEGIN INSERT INTO tblWords (cl_Word,cl_WordLangCode,cl_SourceId) VALUES (@cl_Word,@cl_WordLangCode,@cl_SourceId) SET @cl_WordId = SCOPE_IDENTITY() SELECT @cl_WordId END ELSE BEGIN SELECT @cl_WordId ENDEND所以，我已经加了WITH (HOLDLOCK)到选择查询并添加begin与end该选择查询
4 B, q J, F$ s* e9 t这种方法是否正确，防止有条件INSERT / UPDATE竞争条件
9 T$ h/ S8 a+ q9 A 4 h; W' a# Q9 s3 s$ ~- C8 r
解决方案: |
|
|
|
|
|
|
|
|
|
