I need to lock a table so that no one else can update it nor read from it until I have completed. Does anyone know which level would accomplish this?

Lock a table, or lock a few rows? Locking a table is pretty severe (as in: it will block other apps)

Otis wrote:

Lock a table, or lock a few rows? Locking a table is pretty severe (as in: it will block other apps)

I would say just lock the rows I am currently working with.

I have the following table:

Amount Maximum Used 25 10 0 100 8 2

Basically, we are distributing out awards and we only have a given amount to distribute and we have to stay within the max range.

I only select a subset of the records and I need to lock those records so I can read them and then update the used column.

I never use actual database locks for this, as they are best suited IMHO to avoid contention with concurrent users. So in a case where you want to avoid allowing someone to update a record until you say you're done with it, I'd write it into the application.

I'd add a column to the table "LockedBy" or something like that, and not allow the application to save the record unless the current user is the "LockedBy" user. This is a basic "version control" system without the history.

MarcoP wrote:

Otis wrote:

Lock a table, or lock a few rows? Locking a table is pretty severe (as in: it will block other apps)

I would say just lock the rows I am currently working with.

I have the following table:

Amount Maximum Used 25 10 0 100 8 2

Basically, we are distributing out awards and we only have a given amount to distribute and we have to stay within the max range.

I only select a subset of the records and I need to lock those records so I can read them and then update the used column.

Short answer: RepeatableRead is the level you need to use - it basically locks all the rows you select during the transaction so they cannot be updated during the transaction.

Correct answer: this is potentially a larger subject than can be dealt with in the average forum reply so reading up on isolation levels and locking with particular reference to the db you are using is advised.

Helpful answer (?): As pointed out, depending on database vendor, version and the isolation level of other connections, other transactions attempting to access these rows (and possibly others) may block/timeout while you have the locks so keep the transaction short. I.e. database locks are only really useful for transactions that do not wait on user interaction.

Another simple technique that may be helpful (depending on the nature of the problem) is to set a check contraint to prevent the Used column from exceeding the Maximum or Amount column. I.e. any update that would violate the contraint will fail - you would need to handle the failure appropriately.

HTH

Thank you for your help. I have only one method in the application that can possibly update the column, and in that method, I start a transaction with the RepeatableRead isolation level. Does this mean if say I have two theads calling this same method, the first method call will lock the rows as soon as I perform my read and the second method call will be blocked from reading the same rows until the first method is complete?

Thanks!

MarcoP wrote:

Thank you for your help. I have only one method in the application that can possibly update the column, and in that method, I start a transaction with the RepeatableRead isolation level. Does this mean if say I have two theads calling this same method, the first method call will lock the rows as soon as I perform my read and the second method call will be blocked from reading the same rows until the first method is complete?

Thanks!

No, to do what you are looking for, try adding an UPDLOCK hint to the table you are reading the record from first. I.e.

begin trans...

select * from a_table with (updlock)

...

update a_table set ...

commit

As soon as the first thread selects the data, any other select with (updlock) will block i.e. the transaction on the other thread will block on the select statement until the first transaction is complete.

In that case the isolation level of the transaction does not matter.

Ok, that makes sense. Does that lock the entire table or just the sub-set of data returned? Also, does LLBL support this type of locking?

MarcoP wrote:

Ok, that makes sense. Does that lock the entire table or just the sub-set of data returned? Also, does LLBL support this type of locking?

That is a decision the sql server optimiser will make - my understanding is that in general it tries to balance locking overhead and concurrency. Therefore if you only retrieve a few records for update then it will only get a rowlock for those records, leaving all others accessible. If you retrieve a lot of records (particularly if they represent a large proportion of the number in the table) then it may escalate to a table lock as it is so much more efficient. I think you could request (and probably be granted) a rowlock by adding that to the hint i.e. (updlock, rowlock). Note that other readers without updlock should not be blocked.

Unfortunately it would appear that it is not possible via LL directly http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=5158&HighLight=1. You may need to issue the SQL directly against the db at the beginning of the transaction then use LL after that.

Any plans / alternative suggestions Frans?

No there are no plans in implementing a locking system.