关于X锁的问题--由select+X锁是否持有到事务结束的误区

王朝学院·作者佚名  2016-05-26
窄屏简体版  字體: 小  |  中  |  大  |  超大  

关于X锁的问题--由select+X锁是否持有到事务结束的误区前言:看了宋桑的文章《一次意外的X锁不阻塞问题》,结合本人的测试,说明一下我对select中使用X锁是否会持有到事务结束产生的误区;

详情不多说了,详见宋桑的《一次意外的X锁不阻塞问题》和《消失的共享锁》,对Select+X锁和Select+S锁的情况进行了解释。以下只描述我的测试;测试表结构及数据如下:

1 /****** Script for SelectTopNRows command from SSMS ******/ 2 CREATE TABLE [test_a].[dbo].[tmp_byxl_01](id INT IDENTITY,flag int) 3 INSERT INTO [test_a].[dbo].[tmp_byxl_01](flag) VALUES(null) 4 go 7 5 UPDATE [test_a].[dbo].[tmp_byxl_01] SET flag=id 6 7 SELECT TOP 1000 [id] 8 ,[flag] 9 FROM [test_a].[dbo].[tmp_byxl_01]10 11 12 -------------------------------13 id flag14 ----------- ----15 1 116 2 217 3 318 4 419 5 520 6 621 7 722 23 (7 行受影响)

由于案例出自系统续费问题,业务采用的是调用存储过程的方式实现,因此每一次调用时,都是select+X锁的方式;这和上述文章中提到的“Select+X锁和Select+S锁”的情况不太相同

先说我的误区

误区:select中指定的X锁将在查询结束后立即释放,并不持续到tran结束

测试代码如下:

--session_ABEGIN TRANSELECT * FROM [test_a].[dbo].[tmp_byxl_01](xlock) WHERE flag=2 WAITFOR DELAY '00:00:10'COMMIT--Session_BBEGIN TRANSELECT * FROM [test_a].[dbo].[tmp_byxl_01](xlock) WHERE flag=2COMMIT

由于两个tran都是申请xlock,在执行时,Session_A(spid=53)先执行,Session_B(spid=55)大约5秒后执行,通过SP_LOCK可以看到,spid=55申请X锁时被阻塞

QQtPfz4okDU1p+t56qnTdb6nztPT4sBnvnzgM1+enV9JKtSpU6dOnTp16lHVxdTcwoHPPJAU6tSpU6dOnTr12Opidn5F/UCdOnXq1KlTpx5bXST/Tc0t/B9f+b/evPjf5P4mhdLI8s6lSw//n6vq1I/hXoE6derUqbvUu2Jldn7lzQsXZOd3FEqDy4X/5y31BojhXoE6derUqbvU+zkrsv1bCqXx5cCo7w+oU6dOnbpvvZ+zIveuUSiNLwdGfX9AnTp16tR96/2cFbn3awql8UWd+jHcK1CnTp06dZd6P2dFtj6kUBpf1BsghnsF6tSpU6fuUhdTcwsHDj9w4PADcvdXE1/+alH872dH70ZF5b//3zPiwPH/XmkQCvYZUTlw+AF1zk9w/cXz8tLjpfr57nl5adV0zPK5dXnt1MOhxnXskrSyfuaJ1PFPnL5q9u3h19cvvtgf49Vz9+ntvnheXlo9/OLPsqbMMaEetG49P8u/Rx5+fV1eO70calxDz/BXH0sd/9jpq2bfls+tX3yxP8ar5xb0dl/8mXxn9fCL53NneLl1o3hdzM6vJDW588vB8uKi6LL4w1T7u388IxQHjr+rfvUPxw8OHjnk+CrL2Yf6NsTBP75csJ8XF8XC2Zrtnn1IiIdeVD9e/taB1Av/4fjBgd92W/oo915cHBrMd/94pkjArUEw93n5WwcSz40x+eFCehRnHxIz3/oHFRNV743abyLcy4HDD6hzvlfvvyHPH0u1L59b779Fu2/+2fmVA4efOH1Vnj+W68d4fNpW+fqLb6aui0X7efG8vPRUKX9eUhcDzTHL59bltZcf1bz2vjPX5FsvHTj8wOrF1AJ49dx92bl4L+l89aL88AfPHsjVfX2778w1+3Lf9eHRnyqxMjv/khIrGVurF7unSqH4P3H6apm5K3PODJ63y+c+TJ2xqfl64uWPBt8LQ46vweflcx/2VGb152f598ijP103vMfvO3NNXnzxwOEHnnordXZ99NP7eudw+pQ7Or+yerE7zNnBuq9vw8/wxIflc+sXX+z3c/XcUZ2t1Yvyze8UnscnXv6o+/6t8NwQyX9Tcwty559S5S8WhVj8YVL/yfGDXzqbtP/wS0Lccvzd3mHvPjYzcJiq94rt+CrL2YfEzLd+Urqfv1gUvZH62z37kBAP/cXwV7372Ew/Jn+xmInPwVtmBnwYjOoPvyR6Abz8rVuqGHIFQeh5/thly1z88Euid0BqaGrsqfoPvyTELTMPfWnxYCVzmi3q1E/dP8mfPa3uuXv3DccuyfTewPK59b6UeeLUlf5SPjX8+ArvMx5LrnMHDj/w+AUp+3sAkd13Wu5He5f/xy/0L0WpPY9un6sXpeze/z2R3Cbm6gV9e/yCcV5SF5J3TmeX/r7d5IpSMG7HLkl5bf3CO+evZvZ7qj1PMnPdHXL6/Jl6+h2Z3hvonbG9OKt6rx/b8XWc59fWr5aIc917h6b68rn13l6F9jxRciTV/sSpKx8ngR2sF/QtUdLa16bO8Eu5M7xvV/fedK4//Y6U19YvXjp/NbPfU/bc6IqV2fkVuf1+v/z8GzMHv3E53bL9vtz+7qIQiz8YbPzB7wvx+2e335fbPz5+MPNb+/FVlrNHxcwzPy7dz3cXPX1L2738zKfF0e86vOrHxw/2XvWD3xepOF9+5tMzz/z47NF00HJR/fk3ZhIn9XNUvngHIYmDclI3Fz8+fvDTx3+ee+EPfr8fsXT959+YyZ85uvKT7z379cU7fvK9Z4c2qqLeAP09AM29xYvnZW7v5Dvv9Tb58/ed9uPruW/+znvScN/vUK/5vtNQf+qtftyeest633zsklTzcuySVPeC6brRH7Nvy+c+7N3jHjj84psyd/+Xvu/snxv9vZD7zlxL3H7qretqcS+wZ6DZt6ip/uhP1zXnyYtv5n04dkl245bfO7QfX7HPR39wff3ME8l1PdK9Q0M9EQqqnvdfv3fynffUjcdA3eiP2bflc+sD+5S5Pbz03mH/3Ojvhdx35lqyf7l68drp5ULz2BOy1Z7PSc7KkQOHj8itd1Pl+UUhFr//7kDj939phpyjy1vv5o789PGfvyu3Xj9+cPAl9uN//kcz4vfOJo3p+tbz6vHTzDOvyy3V8/OLQoiDv7d4ULX3TZw9qh48qH765fIzn879tm9FCNHz8/lF3cst5ezRjJNuL+y6nXnJ68cPfvr4zzNxy3erWgZ+dfmZT6vRnT0qxNHncxHuPdTr95YOQjoy5rkYmKmc58p6ps+f/9HMwT86fnSwq1775Ww9ccB1Ll77mye+vnjH+df/NPnx/Ot/+vXFO177mydMxx84fESd8wcOH+nddx4ZaD92SV55/b7+MUn7yfPdvZPV01fl+WPOxy+fW5eXVpP2dP3wSfX4af3M6oHDRw4cXj11RZ4/dvK8lPLqpZ9dUe1Hpp5+R149d1+q/9WLcv3M6uBYFlJbwe+sdttPDuRVdP082bvvzPjsXk/u7fox7A7k1ccOHD6SDDPX/+rpq9dOL/f9Vzd5q7n+H7+QGnt3PyZXd/BnsP/Hkok70I+/mosjfVsXT65evLQ6YGv11BUVz26c18+sDmzpKy6edIhb+fi710/29g5T7ccuJadTzrdrp5fVeeh8/PK5dXW+peupc693ribvnZM/k1JevXT+auocPnZJJnsS3cvt4DnQs5s6wzXvIyllz8/0+VA4bmqOsm/VqYdfX9f0v3rqysenl/vnsHJVzXXmtO++tr8fM1h38Gew/9XTV9NzffJncuC87fZ/8eTjF95ZHbC1evrqwHvhvjPX1l99TH+GX/iuQ9zKxz9bT3JWjhw4fERuvjNQvvdFIYQQXzybbjn4h5czh23+6PhBsfi9dMXt+Et/OKM679f/bFH8u+OXND33PUl1e/bBQYub71x++t9ljaYNdcufLYr+C88+KHov+bPF7JFDytkH04onP1hT+dHxg2Lx6T+cSb+kPy5tEPIBf0d+74viwT9LOzPz9I/6/fQH/qPjD/YMfe+Loh9qMfP0j6Sm3TwXmmAa49afi+99UaiAX/rDmX636Rd6B1+V1/7m8a8v3nH+7/7j+b/7j19fvOO1v3nccvCBw0fUOd+td5PXkvuwnvj46Kf3pY/pLQRvfmdFiZVZx+N7AqV3z5HUX+pdGI7Mzj/bu4iuvvxR/+La2zs5cuDwkafe6l08enkDyY8DY3n0p2oB7bWfPC8Tn3sLZdfPk+fT483HZHj9pd5idPJNeb23QK++/JE8f6wrVnL9d8VBps/7zlxLhqnuyWTK50FbmbrfMUd/cF12hUKe7kX65Y+6sV29KOVH19cvnuyN6z216OvifKR3P1o4nnXWe2d43+ee+Bg8fuA8VGJl+PHL5z6U7ymx0qufPN/Na8m+d/rvtf7+2ZHVi8l7Svlw5Km3+mKla1dzXr10PvVeWL0oe36azhP3unqPnEzeqrPzqfe+7hw4cPjkmzJ1nqTOjfz6kJz2R7O2LO/N4cfcd+aalJeemn9Je4a//KiKv3zzO0msrq1fPNkbV+q9qXv/JgJ9hOezSP6bmluQ117Jl8tPfkoIIe7/hrz2inz5TnHrA5ezx3z7+K2fOn4hqYjFl1O/sh9/4YEZcefZpFHVX75TDDLz5LdzPX9jUSQWv7GoeriW/226RYh0Y8ax/o+mDhMy3b4ir71y9v7Ew14/mpcbyst3CjEQrnRXqXouqv2A5x0eHGk6wmoqhRAq1AOzowz1+tTOxWCfUk2fZqJTczFoa3B0aizpund57a/+/dcX7/j64h2v/dW/tx+pTn39nsTFk+b7yCTH/ojzfWfveO19Zy69f/3Vx3J7NmovR7MH8PgFKbN7DMlluH9vl92P6fupve9R98H9/Q9trNRy+fic5nMK62dW9fedy+fWNftPid2Uz2rfpX9/udr7/E6mrvMnW+8d05VB6fbMfedjp69eO33s3HpyDnSXfrWXlt4by/TT32sxxCqKujrDDxw27QWq2DruHfaO1+4dPv1O/sTIvXfUXk53Lu47c0324q/bO0z2zFLnp9qPyfqp3btSOjV7vunqvfeI7gzX7x329oRyfXbljmEfK722ZOqWvUPd+yslSXV7h6unr1479fTrPQl+ZOHVj2V/Ly29Rsn8fmey1zLCc7grVmbnV8zr+zcWhbqMZS6c6St0/rJqPd4kVvT6ZqCfy09+aubJb19+8lNdFaW9QOo6Ebpr5zCxYisDYsVo2hTVvM5IY4iq8cL/ilGsXHhgRvQOM4ZaaYWUWMnPhVasGOPWC8igmkn5nH6hd/DT5Scv/4dErPzk5f/gKFYM91LJ/d/J5HZt4JhuDsoRzX2n/XjtfWfqnjLVT3bPJrlfT1bwnM/pvZns/W6yBqX3Zgbvj6u779Tec/vcdw7uzaT7V/flFdx3rl68dno5067zZ/lc714zudc/+aaU54/17zt1/URx3+m8H5acusm4Bo/p56Dk9g7tx2v3DjPnXref7HsniVvvM2Ld+/4B3nopN5bksOunl3Pvo/4eZ3V7h9p9U5+9w8G9Ge37t4K9w9WL115+NNOu8+fRnyqx8tRb3YfO54/19w51/fTXohGew2JqbmH68JHpw0ey16S+Dkhdg1++c2CD4cIDM305khcr1uMvPDDT+9XlJz/VuzYP6Juz9xsu2BcemLn1zsX0BkPP27P3i+4lVvV/4YHjWQ/TVr59/FZRjVjp67Z+/2fvT+1LZdVJ6kfNb5MeBseeiee1AR2QOHP5yU+lIpCVHZlQDzifDYJuLoxixToX1759/Na0WtLt/eh7dirnv/9YsqeS7K+c//5jloOnDx9R5/x0L0ehW+/d60x37wt79bmF6W7KWPLa/n1nvx/L8cvn1nv3Vck+7WrvHvH8se7xSZLEdO++s+fPwvTyufWrl85fvXbq4YXpw0eml7v3/dO9beSFdP8Pv366+1rVTzdfIWk8fbWbszLdu+/s+6+p91+7erG75zR97FLK/8Tnkz+TfZ+TZ+HTvfvOwT7795fTh0+ev3iyN/ZenkFqLhZe/ThpnE7lB2TrA/0rfzJ10zGJP937Ts350NsD68Xz49PLJ88P7kul+0/uO7v9LJ9blx+fXk4q1049PHAOWGNeQz11zkz39uqmDx+Z7n1+LeWzmsfceWg/Xo23u3+TxC197h1ZvZicD7n3zvK59SvvnL+aeu/0/E9yVjLxPJ19D6bfR4+dvprNWbHGp//axy9Imcz7sUsp/xOfu7ciyWv7+Uya/rt7hN36he/2xp563/XmQu0d9gbYe7/o3zuZ96zp/Ztp7+5X6c+H1H7YdDef7OTPzOtDIiun++tbd81JKulzoKbzWczOr0znxcrAU4OcUOjf/acvnMnWRQ91J208vm9i5v47By9aGbsaGZS6EKqreN5u19zZ+3s9KgXWf8bxqeNPltpZye2FFBEr/cOyLYao9kt3RySjDMT930hd/vudDIR6YDtHt8ORnwuDpDh7f0ZR5b3t2xo4Mj0jmn2y4SXZU1EbKpkftWJFnfPTvXs7dRenFujZ+ZXp7N9N6bVn7v96+wrm4/tZgesXL6l70PTx6iKR3Hf2fegtakd7PmcSDHv9dO/b+glxb73UHWN/E/va6TPde8Tp3n1nOg65enefabonVmbnV5RYUfd20919lC7J8dO9+85Mn728hJUBx3qiZHAu3lOL7Ox33pM9ETNQH+g/5c9A3XTMynTqvrN/zKM/Xb94Mj32pDz11vXkCZGp/+S+U12AP5TXlVh5+dGBObLGvJZ65gzvH5P7uym913Zzd7p0zxnL8Uk+UPcM/1DNXer4nt3+nk0qtgPnQLpdiZVuPNMJqt25WEmd4ddPn8nuHVrj85ISK0+9lRYr7ymx0j2Hc29VtXeY97n//v3Oe+oMV+/fzGnffy8k769MPft+VO8p0/s3097dOxw4prt32B9777157fSxcx921w1N/0qsDKw53b83MzBHNZ3PYrqnX3wvEql79FqOH6oSUo8/yhX9s6exKYbHYWGLMW3FUipIWNFKE7teUae+u65PtkOG7UMUP95e799flr9HSeXWVOKbd733+Rqf1/Y/QzRYV8c8pnl2kCF1v/izC8ZjTl+UUsr1M6uamB+7JPv7BJl73O7FO783MC71hVc/lsP3IYofb6+r3JQKxtLLWRlZPHufr/F5bfczRLm6Okb3dCxDaj/pvPYjPFLKq+fUGa6J+dPvyP4eVWafsnuG9/fVRhHbrlix5qyYi7qHdrxY+h5vuTRWp3vGXayUzPaoqvhqJkNCce1FvQH8dL26H1L3c9Ueb6r3clyKvDZfT+WXlOqnRD3Jv3E/PllV8/VR+d/Yujpj1Z5ctceb6ql8lwrGcuxSfx9oRPFM8m/cj0/+LnO+HtG5EUfdkLMScUmevGSTY8qUsRcrFI8yHce9rOOeSupepxH3ndSpp+rJMx2V11JBnyPfO6ReW92Ys0KhNLJMj/r+gDp16tSp+9aL56xQKONY1Kkfw70CderUqVN3qYvpww9OH35wtljOCoUybiU54ZNznjp16tSpj0VdTM0tTN/54PSDD8prr8h/Hf21hEKhUCgUCiVdxOz8SlesoFQoFAqFQqHEV0SiVMhZoVAoFAqFEmfpihVyVigUCoVCocRZ

 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
 
© 2005- 王朝網路 版權所有 導航