知方可补不足~Sqlserver发布订阅与sql事务的关系回到目录
前几讲说了一下通过sqlserver的发布与订阅来实现数据的同步,再通过EF这个ORM架构最终实现架构系统的读写分离,而在使用发布与订阅来实现数据同步时,需要我们注意几点,那就是当操作被使用在“事务上下文”时,你的同步操作有可能会被延时,嘟嘟!
这个不难理解,我们都知道事务有一些级别,而最高级别serializable 又是.net TransactionScope默认的级别,所以,在程序开发中,只要用了事务,基本都是serializable,而这个级别是最安全的,当然对于SQL来说,也是最容易发生死锁及阻塞的,呵呵。
如果对要SQL锁不清楚的同学,可以看我的这篇文章《知方可补不足~Sqlserver中的几把锁和.net中的事务级别》
下面是我总结的,在事务为serializable级别,对于发布订订阅同步的关系
set transaction isolation level serializable begin transelect * from User_Info --读取所有数据,等待事务结束后才能同步TAB(S) ,TAB(IX)update User_Info set Status=1 where UserInfoID=1 --更新其他数据,可以立即同步 TAB(IX),Page(IX),Key(X)select * from User_Info where UserInfoID=1 --事务中读取其他数据,可以立即同步TAB(IS),KEY(S),Page(IS)select * from User_Info where UserInfoID=28 --事务中读当前数据,等待事务结束后才能同步 TAB(IS),KEY(S),Page(IS)select * from User_Info where UserInfoID<30 --事务中读取范围数据,包括要同步的数据,等待事务结束后才能同步,tab(is), KEY(ranges-s),page(is)select * from User_Info where UserInfoID<10 --事务中读取范围数据,不包括要同步的数据,可以立即同步,tab(is), KEY(ranges-s),page(is)waitfor delay '00:02:00' --等待2分钟 commit tran
通过上面的结果,我们可以知道,只要当前需要同步(正在发生变化的数据,就是要同步的数据)的数据不在被锁的范围里,就不会对同步有所影响,当然,你要是在事务里来个select * from table,那你就玩完了,需要等待你的事务结束后,你这个张表发生变
化的数据才能被同步,所以,经验告诉我们,在事务里,能不写查询就不要写,呵呵。
下面图中显示的是在一个事务里添加了范围锁的例子,看上支挺恐怖的,它对应的语句是select * from User_Info where UserInfoID<10,直接查询出10条数据,这时,SQL会把这10条数据分别加上范围共享锁,以对这10条数据进行保护,你此时,要想对这10条数据的任何一条进行修改,那只能等待事务结束后了......
PRiIv00F3z42qZwI7/JAquVfQQRP9JdyvI7frWZRgL4luw69O3mqH/9HgcNdnBWK9bVeb9J+EA6uU71ddKkcGoHhJohZ7G5mQe9Z/MOng1OSI1s/4ixitRW1XiUzO3DKNPxJo0x72rEzz6xOQ4arK342uO/pda/011DeEJ/lNpFu6y3640zzwYYgDMVoAe8LrVrI+3bXTLNftz4vzJG8GhOnT1AOROvBi7RozG4MhyrO+Io+dP3qYVVMvcLnDC+VNQ1mkHY24qTm8YZp+IOpjlmttMvHV6BNuAR22oN0D+xDdi10TTaOrcjOFY3w6D/3/PU0K7FuFV8cj6T/9peCp6PeB1q1kfvPHjmsy1Liia471xXJTEFThkonVnuhdDvAsGf5QCtPEVXhWnFUL/KYX/9P0ySeI1GW3dqMhw2yes7Ldr4MNxyESbM1M41jfC6f+/F1kV62p20Iua//1TDWZFzTTJB8OcZqIVIo3uGd2tZn10n3Rz9sqKS80rRcSAEkU/RHLLMPpErMmgGoE+Oda3w4nfP3mr4nfPeDVXrQiedn9Ecmnmvz+iWDZLztYR74i3uC+ASD20eNr9e9M75CMHY2jDAHZKztYR74g0Yoi30wPv31verc9rHfGOSCOGeDs9yP3p+rciXvZX28ni+PeZfxQJ/9q/W+jhZ38693WHCf9+//7N4vj3yX8UCf/av1vo4e/+lMGhIxKJRCKx4s/3e88OtanXhO9ri7u4dQx3DBdrkiF5yK+H78/Zn569YeSXGsPbA40Yhjbk1wP9p1Xh2dshwx0DjRiGNuTXA/2nVeHZ2yHDHQONGIY25NdDyH+qt1lcUqVVAQ/G6W0EG0Z7QUgkPhLyS43h7QEbMc+Y5gzxENRDK4myD/qRyD9xJmYN3USiciCv5AFvGCY/EsCG4RV3pBr0nxi6YUKTDA8OQT383m8tkWT1n0BXEZn1/afHCPfiyRncDn87Yz9aDfpPDN0Q1yTDJ4QJPYA95g/Xzlbk2W4Nn78/5fSffp+0P9F/YuiGuCYZPiFM6GF0f/rtrG+gCLOGz9+fbuE/8fyJYV3g+RNDGyJrlFCFd3Rk8uP7E/2n37/pPzF8RggaMbee5gzxcOL5E9iffquNyswK1/D5+9Mt/KfpatB/YuiGoBFz62nOEA8H1ygRr/0qcBals8I1fP7+lNN/ivBIoP/E0A38/3sMbYjrAe9PWjDmWdQS/+l3szd2s8sc+Psnhg8PPH9iaENcD8f3p+4/cQ2fL0reH8Hw4YFGDEMb8uuB9++tCs/eDhnuGGjEMLQhvx7oP60Kz94OGe4YaMQwtCG/Hug/rQrP3g4Z7hhoxDC0Ib8e6D+tCs/eDhnuGGjEMLQhvx5+/KcMr/IlEolEIrHi3/3pfXvkReGb/hPDZ4eLNcmQPOTXw9fn7E/P3jDyS43h7YFGDEMb8uuB/tOq8OztkOGOgUYMQxvy64H+06rw7O2Q4Y6BRgxDG/LrIeQ/PePiEzwYp7cRbBgr7jfKLzWGtwdsxDxjmjPEQ1AP+ho9TyrBC41wJmYNn38/LN4wTH4kgA3DK+5INeg/MXTDhCYZHhyCevj9u3P/Hvgn0NU59+89RrgXT87gdvjbGfvRatB/YuiGuCYZPiFM6AHsMfpO2OP7E/2n37/5/ieGzwhxTTJ8QpjQw+j+9NtZ30ARZg2fvz/dwn/i+RPDusDzJ4Y2RNYooQrv6Mjkx/cn+k+/f9N/YviMEDRibj3NGeLhxPMnsD/9VhuVmRWu4fP3p1v4T9PVoP/E0A1BI+bW05whHg6uUSJe+1XgLEpnhWv4/P0pp/8U4ZFA/4mhG/j/9xjaENcD3p+0YMyzqCX+0+9mb+xmlznw908MHx54/sTQhrgeju9P3X/iGj5flLw/guHDA40Yhjbk1wPv31sVnr0dMtwx0IhhaEN+PdB/WhWevR0y3DHQiGFoQ3490H9aFZ69HTLcMdCIYWhDfj3Qf1oVnr0dMtwx0IhhaEN+Pfz4T1///lPK8/HtrysmEolEYhD/7k/bf7+2//vn13+/tv/755H8699Ly/3+958r2/j1b5Z+Jk/LL9YkeXKeXw/fP/vT//2z/fefB2Mpl7bx699LW/dHahn6mZgWL9YkMTnm18OP/5Rht3ySh/HaMC5q48XeIfkd+cWaJE/O8+uB/tMqpP9EzIb57WUi9SBqGDp/ei3uf+Pr1Rc1TY0BaUQ+ZR+CaXSMV9aQh6Hr7+UT5OC7XdGESD+LeM15/kR+UJN/9GbGJ6k/+WV62PYrbWTt0vqp67PQkrmGm3Xo+0/Nwv0TI3gwBuTjlSU+9Z4CeVYsvv8UzOFc28Ssjyi9TQPqv9F/Ih7TZHkZfxGVEp+BXT3UmLouefHep2AFi6y00fMnb5fT8drSb9N4vFtW3Znj+cc9jDbPrqcS5N3vdj3rQ1grQUuW50/kRzRpzq+z5gJ5Th7RA47Xa6/QT5see1cmj54/YZ8mHiN46+Lhsmoa/dRj/Cdtw9J/ip6IQXvZ86WID8O4/+TFD32Xdsh/wjut6bV4HgBI41ln2GrremBq0zLaAjyMoAc2xLu/LehaIsF2/eE8fyI/okkwT/PUn/x6PYj1p+yDqZ/22VZLExo7039al+aI91Yxuf9k1oH+E/FEpP9EnNDDFvCBzJVKfHrIf8I7redVtJb+Ee8EpNFeWuXY04p7GNh7m+Oj50/CMNFpQD/8+i/Pn8gPabLO3xVzgTwnj58/eeu8+U2PpyXwjZFXzxn/SXPsxxzhW8DPCHo/mf0nXTdtcdB/Iq7TpOcz0Yt6MMb9aew/eeu/WFcP+U94pxWWVN0nfzV7Y2v76zSRfLw02vMQu7GZz5CHofP08gnyofMns42/eP5Efirn75/I42tUu/LU9cdcq7UnZMbjdd7kvD9iFfL+CGI2zH9fAJF6EDXk/XtLOO/fI8/G89+3Rk49tJz+0yqk/0TMhvntZSL1IGrI9z8t4Xz/E3k2nv99P+TUQ8vpP61C+k/EbJjfXiZSD6KGf/enkuDl66sxw+uKiUQikRjET/Gfvq/1ML7oPxGT4cWaJCbH/Hr44vnTIv7N8yfyZPxiTZIn5/n1QP9pFdJ/ImbD/PYykXoQNeTvn1bt/Fe28WLvkPyO/GJNkifn+fUQ9Z+KuhOvuXQnGrNZNzI1FwuF0phpmWUJ7HoYJXAfVByBbSJ6CbQ0XkPcOrNE0A96RETMXB96o4Z10i2r2yIi1uSQ0oiPwbgevE+L2hdqvLm2T9Swc/60Xz6+tv29SUXds9TGiDQgH6+stj66LK8+ox4GLnd65/fSlP37UTQH/ePlCbzDmk8jF7f+Rd2Y3qbvPgv6EOcjyu2ORVdL5HFNRrRH/jwe10NwvRXzGucT4ZP+05Dl5e238Ty3/c4c389bzOY/RTyGNg32EkDrPBsH1M2rZ/dZ0IcgH13D7liINOeO3VNx1KcnPhsn9DC0PhyfldHzJ28nHLX6BTcterOsmqZ9KuKFRDyMs3Z7sfN7aartH7FYWxGAcrF32MYX571Top8r1x6VeMqrp0hj5uNZZCAfL82JY/dUjs8b9stNljqTv0UPeh3weHBez9Vz3n9qlgx71zXTeDYvtoW7Kfdbnd2KVP6T15+RXh1tnSirzU3zuZhaSjsiZhrddlMh3Xy6miEOaTKuNOJj8Ij/5K3DbT7HZ2X090/eTqit2hVpPOtYx4Mdu/s/3M61HLvnT60lAtqorRKTR86fNmgN6bJ0PbXFJPKpZYE0ER7JJ6Ir8qAmt73xkafO5Bn0oLVh8viaFuSnnT8Byyti54I0wZxx/lsy/yneM7gHIq0L+k/epzimxgv/L5KzWbdIPiLN6WP3VOT5EzGoh23/LYX5jcVuGlrKOT4rZ86fNNfWa3enDXJtQWurGTzb8lTnT9gbAOm9NHPnT5rrukXS6LIi+YA+B/lENEM+pEk8f/PUn/waPQTXija9nuOmrkb5Ob9/8vbSNk0kHy+NqI9+ysxHYEL/qS3X7EOvB4Zah3PTJc7F6LKC+XTrHCzrdOvyecjfPxEjeqgo1liTtzF4JZ+rIe/fW8Lz3L/3Ek2isTidf0Ibb6dJ8uQ8vx54/94qTHX/XsQDuy8+u3X31SQxOebXA+/fW7jzX9lG3r9Hnk2T5Ml5fj3Qf1qFqfwnIvF6TRKTY3498Pxp4c5/ZRv5/ifybJokT87z6+HHf8rwKl8ikUgkEivy/GnVzn9lG794/kSeTJPkyXl+PXzz/OkZ3+1e3DriHfFiTRKTY349fPH8aRH/5vkTeTJ+sSbJk/P8eqD/tArpPxGzYX57mUg9iBrOvP+puXLmb5oaA9Js6s603Y0+sTQ6xitr1MMQbTzIwXe7sgmwDm1KUG7XO/RuWDD7v+1bnWao/uR5ONbk1rt1/u31J79md5t8adGPHry5r/Uj1pya3lzDzTr0/aemKj8xggdjQD5eWeJT7ymQZ0XgYeBy5xDYJq8h6dcfpDyldW3+Rd2gFYkRuZ3bh8RzsatJPbLeWBMfgBNrFF6R9Kd69fCe9Wp42vufgAfQ5d2yin/jdSTPIx7GHAff7XpWRundDg74nHeoyyrwtnKz/m38uX1Ifi6PaLLViakZ8sfw+BoV8ZBaE6fqR68n5rNePaPnT9iniccI/mPa98qqafRTx/2n7rNX2ibA4gA41DrTGop7S7sh2OdJ/ykzBjWJvWTiYzC+RnnxeAUQMYf8p1Hr+7U8dXZXkcazzrDV1vXAxKZ1oocxzUfPn7x2tZYIsDiOnz8V//1Pom91mm2/qOWxEMnjmlw3F8hz8oge9NzXa1dwTZjQ2Jn+07o0kadwyi2x/wR6IG5xHG9dKyYcA546tw+J5yL9J+KEHrbAimSuEuLT+GrW1vCc86fi/+8y0zOIpxG7Lt6BV3gYc3z0u11pmAxaHMe9w+otiXp6VpJIY1pVJ/Yn+TWabHVCL+rZfPqM3Ixvg6kl71lQzxn/SXPsxxzhG9x1dek39Z+6fkkd4HNbF/GWhjwq+k+Zkf9/jzikh8qx/+St/2JlPuQ/DVnfwlLWtr9OE8nHS6MtfbEbm/mc7mEM8fh3/V4bRd9u0OIY8g4Fr/n/UjHxNLS483P+/ok8qIfNWnm0Hrx1yYzH67zJeX/EKuT9EcRsmP++ACL1IGrI+/eWcN6/R56N579vjZx6aDn9p1VI/4mYDfPby0TqQdSQ739atfNf2Ua+/4k8mybJk/P8eqD/tArpPxGzYX57mUg9iBr+3Z8yvMqXSCQSicSK9J+eYJtc3DriHfFiTRKTY349fPH8aRH/5vkTeTJ+sSbJk/P8eqD/tArpPxGzYX57mUg9iBry909L+Dd//0SejF+sSfLkPL8eQv7T/jKbFPvqBGIP4/Q2AttkdxdUIP5464hErMltzVukiZkxqId2VSz7IJ4SMXp90zljvfXPn4pzb1uG3fUsD2NFG8F3u+09VBEeKZfnT+QrNPn2OpO/XQ9b+B1OBb7/b8n9e+3+dmvbCngYK9qI/SezdGGnnNU6InFUk8RPwAk9AA+p9O4sx96VV8PO+VO7y0V2vLQceBgr2gi+29VWho4v/vu0TM7zJ/ITNUn+CXxCD97ate3vLP+lYvCzXj3pP61q45z/xPMn4jrk+RMxqAd9VlTjzfMnkwuP6pD/5O1gPH+a4we/2+X5E/npPPh7F54/fQg/8fzJXLuE58TzJ4S38J+8NEdaRyR2NQn0SXwqHlyjNue7H+E/Vc7zp0kP443nTxEeKZfnT+QrNPn2OpNn0AM+Q9JrpnkWteT8aWv2RpAmP/L3T8QPR54/Eef0gP0nrRkz/pD/lGdXX8R5/x75h/P8962RUw8t5/17q5D37xGzYf771ojUg6gh799bwnn/Hnk2nv++NXLqoeX0n1Yh/SdiNsxvLxOpB1FDnj+t2vmvbCPPn8izaZI8Oc+vhx//KcOrfIlEIpFIrMjzp1U7/5Vt/OL5E3kyTZIn5/n18M3zp2d8t3tx64h3xIs1SUyO+fXwxfOnRfyb50/kyfjFmiRPzvPrgf7TKqT/RMyG+e1lIvUgatg/f2ouunj/jrrIwzi9jeC7XXGRYjc+wnn+RH5Ek9v+iq88dSZ/ux70XXw6/s+zYv3U69svlTPWW99/am9JuvXdXMDDWNFGYJu091BF+MHWEYnTmiQ+GIN62MLvcNKfAl2dc//eK1P