2

As I understand it, a SELECT statement will place a shared lock on the rows that it will return. While that SELECT is running, if an UPDATE statement comes along and needs to grab an intent exclusive lock then that UPDATE statement will need to wait until the SELECT statement releases its shared locks.

I am trying to test this SELECT shared lock thing by doing a BEGIN TRAN and then running a SELECT, not COMMITing, and then running an UPDATE in another session on the exact same row. The UPDATE worked fine -- no lock, no wait. So this must not be a valid way to simulate a shared lock blocking an intent exclusive lock? Can you give me a scenario where I can create a lock with a SELECT that would force an UPDATE to wait?

I'm working with SQL Server 2000 and 2005 across a linked server: the table is on the 2005 instance, the select is happening on 2000, and the update is executed from 2005. All in SSMS 2005.

FremenFreedom
  • 136
  • 1
  • Am really looking for way to do this WITHOUT using WITH (HOLDLOCK). I understand that I could use this to create a lock explicitly, but I am really trying to understand and test how this kind of lock is created implicitly. – FremenFreedom Oct 13 '10 at 14:40
  • Rather than SELECTing for one row how about for your entire target table while simultaneously attempting the UPDATE against the target? Blocking often occurs when longer running queries are involved. – jl. Oct 13 '10 at 15:18

1 Answers1

0

You can change the isolation levels from READ COMMITTED to REPEATABLE READ or SERIALIZABLE to force blocking.

mrdenny
  • 27,074
  • 4
  • 40
  • 68