How to Exclusive lock a table.

[JNeave]

Hi,

Two tables, people and their addresses.

People 1-->Many Addresses.

For every person, if addressCount > 0 then 1 and only 1 address row exists with address.current = 1

To change the current address, First you update the current currentAddress row, then you update the new addressNotCurrent row.

But, although this locks the two rows (it's all whithin a transaction), it is possible for a second user to have read which address is burrent at the same time as the first user, wait for the first users update transaction to finish and then create a second current address.

We need to find a way to write a SProc to do three things.

CREATE PROC Bob
(
   @intNewCurrentID AS INT,
   @intPersonID AS INT
)
AS
BEGIN
   Start Transaction
   1) Lock all rows in address with PersonID = @intPersonID
   2) Update 1: Make current address NOT current.
   3) Update 2: Make @intNewCurrentID the current address.
   Commit Transaction
END

I don't know if I'm just being blind, but all I can find about locking in SQL Server in Bokks Online, etc., is, "Don't worry, SQLServer does it all for you!!"

Uh Huh.

Right...

Any suggestions?

Cheers,

Jim.

 

[Gundapaneni]

Hi,
Try in following manner

USE pubs
BEGIN TRAN
SELECT COUNT(*)
FROM authors WITH (TABLOCK, HOLDLOCK)


or read BOL locking,hints topic.

Madhu.