蛙蛙池塘  
人生价值的最好体现就是做好本职工作...
公告
  • 残荷听雨，梨花飞雪，落英缤纷时节。晓来谁染枫林醉？点点都是离人泪
    活着，就是快乐！自信，就是美丽！ 有人爱，就是幸福。
    春天来了
    但愿野百合也有春天

    第三季度的计划



    木了
    我的最新闪存
    晚上一个人看会儿《读者乡土人文版》，听会儿广播挺不错的，想起了三年前在石家庄没电脑的日子，时光飞逝呀，现在笔记本都用上了，以前从没想过，确实得知足常乐。
日历
<2008年4月>
日一二三四五六
303112345
6789101112
13141516171819
20212223242526
27282930123
45678910
统计
  • 随笔 - 248
  • 文章 - 2
  • 评论 - 2319
  • 引用 - 75

导航

与我联系

搜索

 

常用链接

留言簿

我参加的小组

我参与的团队

我的标签

随笔分类

随笔档案

相册

朋友

积分与排名

  • 积分 - 540458
  • 排名 - 49

最新评论

阅读排行榜

评论排行榜

60天内阅读排行

 
蛙蛙推荐：多进程多线程访问数据库

蛙蛙推荐：多进程多线程访问数据库

如何让多进程多线程访问数据库，而不会选择相同的数据，这在设计分布式程序的时候经常用到，多台机器的多个进程，每个进程都有多个线程，每个线程要从数据库里取数据来处理，要实现不能漏取数据，也不能重复取数据，这里给出答案

创建一个数据表，如下，一个自增列，一个表示rss链接地址

CREATE TABLE [dbo].[Rss_RssSources](
 [SourceId] [int] IDENTITY(1,1) NOT NULL,
 [Link] [varchar](1024) NOT NULL
) ON [PRIMARY] 

先放1w条数据

declare @i int
set @i = 1
while @i <10000
begin
 select @i = @i +1
 insert into [Rss_RssSources] values(newid())
end 

再创建一个锁表，一个字段表示是否已经锁定的资源，另一个表示已经读取的rss源的最大id

create table Rss_RssSourceLock
(
IsLock bit,
MaxSourceId int
)

初始化数据

insert into Rss_RssSourceLock values (0,0) 

下面我们要设计一个存储过程，让这个存储过程每次返回10个rss源，知道返回所有的rss源，要求无遗漏，无重复返回。如下

CREATE PROCEDURE [dbo].[USP_GetRssSources]
AS
BEGIN
if exists(select * from Rss_RssSourceLock with(READPAST) where IsLock = 0)
begin
 declare @select_count int
 begin tran
  update Rss_RssSourceLock set IsLock = 1

  if object_id('tempdb..#t') is not null  
   drop table #t

  select top 10 a.* into #t from [Rss_RssSources] as a
  inner join Rss_RssSourceLock as b
  on a.SourceId > b.MaxSourceId
  order by a.[SourceId]

  select @select_count = count(*) from #t

  update Rss_RssSourceLock set IsLock = 0,MaxSourceId = MaxSourceId + @select_count

  select * from #t
 commit tran
end
END

1、如果锁表里显示没有进程正在读取rss源（IsLock = 0），那么就返回从最大的rss源id往后的10个rss源，否则返回空。
2、用with(READPAST)表示忽略锁住的行，如果另一个进程正在执行update Rss_RssSourceLock的语句，并且在事务提交前，update语句会锁住这些要更新的行，而Rss_RssSourceLock表就一行数据，这时候select Rss_RssSourceLock表并且忽略被锁的行肯定是没数据的，所以本次存储过程执行会返回空。
3、begin tran和commit tran保证了即使本次存储过程出错，也不会让Rss_RssSourceLock表处于IsLock = 1的脏数据状态，如果处于这种状态，后面的进程执行存储过程就永远也返回不了数据了。
4、因为有时候一次选取的记录可能不够10条，所以这里用了个临时表来暂存记录，再算出来选取的条数，最后更新Rss_RssSourceLock表的MaxSourceId字段。但用临时表肯定会增加数据库的压力，这里不知道用表变量是不是会改善性能，暂时先这样了。
5、应用里调用这个存储过程，如果返回了数据，就进行处理，如果没返回数据，就sleep几秒才执行，直到返回数据。

我测试了一下，应该没问题，俺是数据库菜鸟，有高手的话给指点指点有没有隐患和bug
相关链接：
关于sqlserver锁的一点儿讨论
http://topic.csdn.net/t/20061127/12/5187714.html#

posted on 2008-04-19 23:36 蛙蛙池塘 阅读(3816) 评论(49)  编辑 收藏 网摘
评论:
  • #1楼  SPARON       Posted @ 2008-04-20 00:15
    呵呵，学习了。
      回复  引用  查看    

  • #2楼  sban       Posted @ 2008-04-20 00:20
    为什么说“有时候一次选取的记录可能不够10条”？   回复  引用  查看    

  • #3楼  SPARON       Posted @ 2008-04-20 01:02
    @sban
    这个问题很有可能啊。       回复  引用  查看    

  • #4楼  镜涛       Posted @ 2008-04-20 02:09
    学习，现在还没用到这个！   回复  引用  查看    

  • #5楼  BAsil Posted @ 2008-04-20 07:10
    好   回复  引用    

  • #6楼[楼主]  蛙蛙池塘       Posted @ 2008-04-20 09:24
    @SPARON
    有啥问题，给指点指点       回复  引用  查看    

  • #7楼  侯垒       Posted @ 2008-04-20 09:41
    学习了,支持楼主.   回复  引用  查看    

  • #8楼[楼主]  蛙蛙池塘       Posted @ 2008-04-20 09:42
    先别学习，我还不知道我写的对不对呢，本蛙是数据库菜鸟也。   回复  引用  查看    

  • #9楼  狼Robot       Posted @ 2008-04-20 14:41
    不学习,顶一个.   回复  引用  查看    

  • #10楼  jakeen[未注册用户] Posted @ 2008-04-20 17:22
    菜鸟都这么强.....!   回复  引用    

  • #11楼  PerfectDesign       Posted @ 2008-04-20 18:50
    有几个地方，可以值得商榷
    select @select_count = count(*) from #t
    这句话可以作为@@rowcount 简洁明了


    另外楼主提到的是否可以使用表变量的问题，最好在实际中使用2个版本的存储过程，来测试对比一下就知道了。个人建议是你在没有对临时数据进行更新操作的时候，小数据量的情况下使用表变量速度要大大快于临时表。临时表的优势就是还能建立索引，加快访问速度。而且支持回滚级事务。       回复  引用  查看    

  • #12楼  PerfectDesign       Posted @ 2008-04-20 18:53
    另外，如果不想存储过程老是在被锁的情况下返空，可以建议在else后，直接加上 wait for delay 多少时间后，再嵌套自己执行自己，不过这样有一定风险，可能会经常收到sql timeout。   回复  引用  查看    

  • #13楼[楼主]  蛙蛙池塘       Posted @ 2008-04-20 18:59
    @PerfectDesign
    感谢指导，
    select @@rowcount只能计算更新的条数吧，还是少不了临时表的使用，我这个临时表应该都是五六万的级别，还是用临时表好吧。

    wait for delay相当于sleep了吧，我还是觉得这个逻辑放在业务里吧。省得到时候sqlserver的计数器里显示好多timeout。       回复  引用  查看    

  • #14楼  PerfectDesign       Posted @ 2008-04-20 19:07
    不明白为什么在同一个事务里面，又更新为1，又更新为0？
    是为的的别的线程来选取数据吗？
    你可以直接加上with(updlock）啊
    这样其他线程就不能取到数据了

    除非你的事务被提交，否则别人访问不到数据。       回复  引用  查看    

  • #15楼  PerfectDesign       Posted @ 2008-04-20 19:09
    关于临时表和表变量对比可以参看以前写的：
    http://www.cnblogs.com/perfectdesign/archive/2008/02/26/tablevariableandtemptable.html" target="_new">http://www.cnblogs.com/perfectdesign/archive/2008/02/26/tablevariableandtemptable.html       回复  引用  查看    

  • #16楼  PerfectDesign       Posted @ 2008-04-20 19:10
    不是，他只是返回上语句的受影响行数。just have a try for @identity   回复  引用  查看    

  • #17楼[楼主]  蛙蛙池塘       Posted @ 2008-04-20 19:13
    @PerfectDesign
    谢谢你的连接，
    你是说select * from Rss_RssSourceLock with(updlock,READPAST) 吗？这样是不是IsLock字段就不需要了呀，而且事务要移到外面。       回复  引用  查看    

  • #18楼  PerfectDesign       Posted @ 2008-04-20 19:14
    另外，关于事务的测试，我写了一个简单的demo：
    在管理器的一个会话里执行如下语句：
    begin tran
    select * from test1 with(updlock)
    waitfor delay '00:01';
    commit tran

    新开一个会话，执行如下语句：
    select * from test1

    你会发现没有到1分钟，第二个会话选取不到数据的。       回复  引用  查看    

  • #19楼  PerfectDesign       Posted @ 2008-04-20 19:16
    不需要啊，如果有另外的事务在访问的话，独占锁和独占锁是不可以对同一资源施加的。   回复  引用  查看    

  • #20楼[楼主]  蛙蛙池塘       Posted @ 2008-04-20 19:21
    @PerfectDesign
    你的示例不是在Select外面加了begin tran了吗？这样select的锁就维持到了事务的结束，和holdlock作用类似了吧。
    还有你的select * from test1 with(updlock) 语句是锁的资源是啥？表还是行还是页，假设这个表上没有任何索引。       回复  引用  查看    

  • #21楼  PerfectDesign       Posted @ 2008-04-20 19:54
    仅仅是个示例。
    不过我想来想去还是在使用
    select * from Rss_RssSourceLock with(updlock) where IsLock = 0
    的时候有问题，如果有线程访问，那么会一直等到锁资源被释放。       回复  引用  查看    

  • #22楼  PerfectDesign       Posted @ 2008-04-20 19:57
    有个另外的想法：
    Rss资源表内有个lastvisitRSSDate字段。
    每次在选取十条记录的时候只需要对比这个时间字段，拿出要抓取的网页url。
    当然在选取出来的时候做表锁with paglock
    当然还要将字段更新为当前时间。
    这个我觉得能满足你的需要，而且很简单，也考虑了并发。       回复  引用  查看    

  • #23楼[楼主]  蛙蛙池塘       Posted @ 2008-04-20 19:58
    @PerfectDesign
    在IsLock列上建索引，
    select * from Rss_RssSourceLock with(updlock,readpast) where IsLock = 0 就不会阻塞了，你那个语句table scan，两个更新锁不兼容，所以不能并发执行。       回复  引用  查看    

  • #24楼[楼主]  蛙蛙池塘       Posted @ 2008-04-20 20:01
    我觉得那样比现在的方案性能还要差，你说的那样首先得在时间列上建索引，还要用update...output语句，

    --引用--------------------------------------------------
    PerfectDesign: 有个另外的想法：
    Rss资源表内有个lastvisitRSSDate字段。
    每次在选取十条记录的时候只需要对比这个时间字段，拿出要抓取的网页url。
    当然在选取出来的时候做表锁with paglock
    当然还要将字段更新为当前时间。
    这个我觉得能满足你的需要，而且很简单，也考虑了并发。
    --------------------------------------------------------
       回复  引用  查看    

  • #25楼  PerfectDesign       Posted @ 2008-04-20 20:01
    不过你说的每次选取可能10w条，那么还是适合做页锁，不适合表锁
    假设你每页装100条行？然后有1000个页面锁？好像mssql直接会在5000个锁的时候直接升级为高级锁了。       回复  引用  查看    

  • #26楼  PerfectDesign       Posted @ 2008-04-20 20:02
    你的Rss_RssSourceLock这个表只有一行吧？   回复  引用  查看    

  • #27楼  PerfectDesign       Posted @ 2008-04-20 20:03
    这种方案的确是有全表扫描，而且不会再那个时间字段上建索引，因为更新非常频繁。

    楼主一针见血啊，呵呵       回复  引用  查看    

  • #28楼  PerfectDesign       Posted @ 2008-04-20 20:04
    另外既然已经锁了，就没必要使用output来获取了   回复  引用  查看    

  • #29楼  PerfectDesign       Posted @ 2008-04-20 20:07
    你在16楼误会我的意思了，这两个表提示是不可能并行的。
    一个是独占，一个是不加锁，悖论.即使这样写，也只是起到了updlock的作用，并没有readpast       回复  引用  查看    

  • #30楼[楼主]  蛙蛙池塘       Posted @ 2008-04-20 20:20
    是吗？updlock和readpast不能同时用吗？我觉得看select的where条件，如果条件上有索引，还是可以并行的，readpast是跳过被锁的行，而这时候update可能独占的是索引上的页锁。这样就可以并行了呀，不知道我理解的对不对。

    --引用--------------------------------------------------
    PerfectDesign: 你在16楼误会我的意思了，这两个表提示是不可能并行的。
    一个是独占，一个是不加锁，悖论
    --------------------------------------------------------

    另外Rss_RssSourceLock现在是一行，以后有可能是多行，update...output只是一条语句把先select后update的行为做了，更简单，我现在在锁表里有一个返回的最大ID的列，不需要在源表里用lastvisitRSSDate列了，对吧，加上一个lastvisitRSSDate列，我还得传入一个时间参数和这列比较。
       回复  引用  查看    

  • #31楼  PerfectDesign       Posted @ 2008-04-20 20:24
    恩，直接更新这个锁表的最大id就可以，并不要islock字段。

    不明白为什么会有多行锁表。如果会有，那我可能彻头彻底理解错你的意思了       回复  引用  查看    

  • #32楼  PerfectDesign       Posted @ 2008-04-20 20:25
    这样用id绕出来，可以避免全表扫描资源表。

      回复  引用  查看    

  • #33楼[楼主]  蛙蛙池塘       Posted @ 2008-04-20 20:31
    呵呵，这里的评论有些乱，我几楼说会有多行锁表了呀，下次回复的时候写清回多少楼，呵呵。你21楼说的方案在时间列上建聚集索引性能也不会很差的，你是在select的时候加with(tablock)是为了防止select住同样的记录，我理解的对吧。

    --引用--------------------------------------------------
    PerfectDesign: 恩，直接更新这个锁表的最大id就可以，并不要islock字段。

    不明白为什么会有多行锁表。如果会有，那我可能彻头彻底理解错你的意思了
    --------------------------------------------------------
       回复  引用  查看    

  • #34楼  冷眼胡话[未注册用户] Posted @ 2008-04-20 20:32
    、因为有时候一次选取的记录可能不够10条，所以这里用了个临时表来暂存记录，再算出来选取的条数，最后更新Rss_RssSourceLock表的MaxSourceId字段。但用临时表肯定会增加数据库的压力，这里不知道用表变量是不是会改善性能，暂时先这样了。


    用表变量当然性能会比临时表高一些，表变量是不产生数据库操作日志的，临时表会。       回复  引用    

  • #35楼[楼主]  蛙蛙池塘       Posted @ 2008-04-20 20:39
    @冷眼胡话
    谢谢，那我就用临时表了，反正我也不用临时表的特性，另外dudu应该把评论下面的“回复”按钮做成“@冷眼胡话 33楼\r\n”而不是“@冷眼胡话\r\n”，呵呵       回复  引用  查看    

  • #36楼  PerfectDesign       Posted @ 2008-04-20 21:13
    @冷眼胡话
    你可以看看sql2005内幕一书中对于临时表和表变量的对比，以及性能测试。
    并不是单纯的表变量性能高。       回复  引用  查看    

  • #37楼  PerfectDesign       Posted @ 2008-04-20 21:16
    @蛙蛙池塘 21楼
    呵呵，我的意思并不是说在时间字段上建聚集索引，照我理解你这个RSS实体表还是应该要有一个自增ID的做主键的好。我是说如果我想的那个笨办法只能用上全表扫描了。而且建索引也不可取，因为太频繁更新了。       回复  引用  查看    

  • #38楼[楼主]  蛙蛙池塘       Posted @ 2008-04-20 21:18
    @PerfectDesign 36楼
    明白了，呵呵，我的实体表里第一列就是自增的。
       回复  引用  查看    

  • #39楼  PerfectDesign       Posted @ 2008-04-20 21:21
    @蛙蛙池塘
    还有就是我说的updlock readpast不能并用，是因为你加上了updlock后，会导致一直阻塞在这了，在这个场景中根本不能用。

    另外你一次取10万记录，如果期间web抓取服务器崩溃，那么十万条记录你是算已经抓取完了还是没有抓完呢？我觉得取得稍微少点没关系吧？
    而且10万次抓取怎么也得耗费个10万/60分钟吧？别的网站给你的RSS响应时间应该不会太快了。       回复  引用  查看    

  • #40楼  PerfectDesign       Posted @ 2008-04-20 21:23
    我说的多行锁表的意思是那个Rss_RssSourceLock表有多行记录的意思，而不是锁定多条记录的意思，呵呵，简化描述，让你误会了。   回复  引用  查看    

  • #41楼  fox23       Posted @ 2008-04-20 21:48
    娃娃发起这样一个讨论的话题这样蛮好的,PerfectDesign也挺热心,这样的气氛值得发扬~ 赞   回复  引用  查看    

  • #42楼  PerfectDesign       Posted @ 2008-04-20 22:08
    @fox23
    我也只是喜欢交流，娃娃的博客很有质量，所以订阅了。       回复  引用  查看    

  • #43楼[楼主]  蛙蛙池塘       Posted @ 2008-04-21 09:35
    @PerfectDesign
    呵呵，谢谢谢谢。       回复  引用  查看    

  • #44楼  yzlhccdec       Posted @ 2008-04-21 11:54
    要是我做，我可能会把那个统计表放到内存里面去   回复  引用  查看    

  • #45楼  PerfectDesign       Posted @ 2008-04-21 12:03
    没有持久化，系统崩溃不好吧？那要重新抓取啊。
    不过如果抓完全部数据不是很浪费资源的话，可以不考虑这点重复抓取。       回复  引用  查看    

  • #46楼  Cheney Shue       Posted @ 2008-05-23 15:40
    题目不对，跟多进程和多线程没关系。你要讨论的是并发事务的问题，试试研究研究锁级别和隔离级别的问题。
    另外你可以去了解了解Oracle DB中UNDO和REDO的原理，看看Oracle是如何实现读写事务分离的。       回复  引用  查看    

  • #47楼[楼主]  蛙蛙池塘       Posted @ 2008-05-23 20:26
    @Cheney Shue
    谢谢，多线程多进程访问数据库，其实就是并发的问题，oracle了解的不多。       回复  引用  查看    




发表评论

昵称： [登录] [注册]

主页：

邮箱：(仅博主可见)

评论内容：

  登录  注册

[使用Ctrl+Enter键快速提交评论]

0 1161748




相关文章:

相关链接:
 
Copyright © 蛙蛙池塘 Powered by: 博客园 模板提供：沪江博客