Read, modify table (locking) question

[John Bokma]

I am quite new to MS SQL, and I want to read rows from a todo table, and
when a row has been processed, I want to delete that row.

Ages ago in MySQL I would probably have locked the table, select, process
a row, delete a row, unlock the table.

I have been reading through the documentation from MS SQL, but it's not
clear what exactly I should do.

Since I want to lock only one table in the select (the others just provide
data, and are not modified), what's a good solution?
--
John MexIT: http://johnbokma.com/mexit/
personal page: http://johnbokma.com/
Experienced programmer available: http://castleamber.com/
Happy Customers: http://castleamber.com/testimonials.html

[John Bokma]

John Bokma <jo**@castleamb er.com> wrote:

I am quite new to MS SQL, and I want to read rows from a todo table,
and when a row has been processed, I want to delete that row.

Ages ago in MySQL I would probably have locked the table, select,
process a row, delete a row, unlock the table.

I have been reading through the documentation from MS SQL, but it's
not clear what exactly I should do.

Since I want to lock only one table in the select (the others just
provide data, and are not modified), what's a good solution?

what I came up with:

BEGIN TRANSACTION
SELECT TOP 10 ..... FROM A WITH(ROWLOCK,HO LDLOCK), B, C WHERE ....
.....
.... delete each row in A in TOP 10
END TRANSACTION

what I want to prevent is that more then one process selects 10 rows, and
starts to delete rows (from A) that are selected by any of the other ones.

to me, a rowlock is sufficient, and fine grained enough, and the hold lock
holds it to the end of transaction.

Am I right?

--
John MexIT: http://johnbokma.com/mexit/
personal page: http://johnbokma.com/
Experienced programmer available: http://castleamber.com/
Happy Customers: http://castleamber.com/testimonials.html

[Erland Sommarskog]

John Bokma (jo**@castleamb er.com) writes:

John Bokma <jo**@castleamb er.com> wrote:

I am quite new to MS SQL, and I want to read rows from a todo table,
and when a row has been processed, I want to delete that row.

Ages ago in MySQL I would probably have locked the table, select,
process a row, delete a row, unlock the table.

I have been reading through the documentation from MS SQL, but it's
not clear what exactly I should do.

Since I want to lock only one table in the select (the others just
provide data, and are not modified), what's a good solution?

what I came up with:

BEGIN TRANSACTION
SELECT TOP 10 ..... FROM A WITH(ROWLOCK,HO LDLOCK), B, C WHERE ....
....
... delete each row in A in TOP 10
END TRANSACTION

what I want to prevent is that more then one process selects 10 rows, and
starts to delete rows (from A) that are selected by any of the other ones.

to me, a rowlock is sufficient, and fine grained enough, and the hold lock
holds it to the end of transaction.

Am I right?

Difficult to say with the small amount of information, but it does not
seem quite right.

The smallest change you should do is to is to remove ROWLOCK, but insert
UPDLOCK instead. ROWLOCK is sort of meaningless. Either you have a good
index to locate the rows, and you will get rowlocks. Or you don't have
good indexes, and SQL Server will have to lock the entire table.

UPDLOCK is a shared lock that does not block other readers, but it
blocks others that try to use UPDLOCK. With only HOLDLOCK and you have
two processes coming to the place at the same time, will lock the same
10 ten rows, and then when they to delete, they will block each other.

But there may be other things you could consider. It could be the case
that application locks are a better choice. An application lock is a
lock on a user-defined resource (that is a text string) which is handled
by the Lock Manager. But I know too little about your application to
tell whether it would fit it here.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx