Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I need to get the MAX aggregate of certain field in a transaction. I use this value further inside the transaction. I want to avoid inserts in the entire table from which I'm getting the MAX while the transaction is completed.

I researched the isolation modes and lock hints available. The closest seems to be TABLOCKX, which is the only one that avoids inserts in the entire table (also updates and selects).

Since I no will be affected by updates or selects in the table I wish to avoid only inserts during the transaction. This is possible?

UPDATE: The field on wich the MAX is performed is seted only on inserts, thus there's no risk of get stale results for updates.

I'm using SQL Server 2000.

share|improve this question

closed as unclear what you're asking by Paul White, StanleyJohns, Mark Storey-Smith, dezso, Michael - sqlbot Jul 8 '13 at 2:00

Please clarify your specific problem or add additional details to highlight exactly what you need. As it's currently written, it’s hard to tell exactly what you're asking. See the How to Ask page for help clarifying this question.If this question can be reworded to fit the rules in the help center, please edit the question.

I hope you are not doing this to generate the next ID of some sort. If you are, this is a really bad idea. – mustaccio Jul 5 '13 at 14:51
Yeah, locks are bad in general, but needed sometimes. – Apocatastasis Jul 5 '13 at 16:13
I'd put it this way: Locks are not bad. Unnecessary lock are bad. Identity columns and sequences should be used to generate ID values, then locking won't be an issue. – mustaccio Jul 5 '13 at 16:24
To be honest it didn't occur to me that was likely being used for a sequence generator. If that is the case I echo @mustaccio's comment, bad form. – Mark Storey-Smith Jul 5 '13 at 17:30
@Apocatastasis It would far more helpful to others (and to you since all these people are trying to help you) to either add the transaction code in your question or describe what problem you are trying to solve (what the whole transaction really does) and not only the very narrow sub-problem. – ypercubeᵀᴹ Jul 6 '13 at 13:21
up vote 5 down vote accepted

Setting the isolation level to SERIALIZABLE would have the same effect:

The highest isolation level, serializable, guarantees that a transaction will retrieve exactly the same data every time it repeats a read operation, but it does this by performing a level of locking that is likely to impact other users in multi-user systems.

TABLOCK will suffice, there is no need for TABLOCKX to achieve what you've described so far. If the transaction you mention later updates or inserts to the table then TABLOCKX, if it's just to read for the MAX it's unnecessary and other processes will at least be able to read from the table.

Whether you use the isolation level or TABLOCK hint depends on what proportion of the table you will be scanning to find the MAX aggregate. If you need to touch any significant percentage of the rows TABLOCK is logical. A small percentage and a desire to maintain some degree of concurrency then try SERIALIZABLE. TABLOCK would also eliminate the deadlock risk.

Both options would achieve your aim of preventing an inconsistent result from the aggregate.

share|improve this answer
I think this isn't true for selects. A serializable transaction sets a shared lock on selects. Only in modifications exclusive locks are raised. – Apocatastasis Jul 5 '13 at 15:12
A serializable transaction will only block if a modification occurs. Regardless, a TABLOCK(X) is obviously going to do exactly the same. Perhaps you need to re-phrase your question slightly, am I misunderstanding your objective? – Mark Storey-Smith Jul 5 '13 at 15:28
TABLOCKX issues an exclusive lock, even for selects. Serializable <> TABLOCKX – Apocatastasis Jul 5 '13 at 15:31
Sorry, let me try type that again while paying attention... A serializable transaction will only block if a modification occurs. Regardless, a TABLOCK (not X) is obviously going to do exactly the same. Perhaps you need to re-phrase your question slightly, am I misunderstanding your objective? – Mark Storey-Smith Jul 5 '13 at 16:36
I only need to avoid insert in the entire table during a transaction originated in a SELECT statement, allowing selects and updates. Neither TABLOCKX hint or SERIALIZABLE isolation do this. I'm asking if there's a way to achieve this, but seems that isn't possible. The only way to avoid inserts in a table is to issue an exclusive lock, and for SELECT the only way to achieve this is the TABLOCKX hint, wich also blocks selects and updates. – Apocatastasis Jul 5 '13 at 16:45

Per Mark's determination, this isn't possible using locking hints or isolation level.

If all the INSERTs you care about go through predetermined code paths, you can use a sp_getapplock-based mechanism to serialize them while still allowing normal access to the table during the operation.

It would be preferable to simply use an IDENTITY column for this purpose, though, as the generated values are automatically serialized for you.

share|improve this answer
I can't go this way, since I'm using SQL Server 2000. I'm updating my question. +1 for an alternative out of hints and isolation level. – Apocatastasis Jul 5 '13 at 18:34
@Apocatastasis: The current documentation site I linked to doesn't include SQL 2000 because support is over. I've updated my answer to include a link to the correct documentation. – Jon Seigel Jul 5 '13 at 19:13

Depending on the size of your data maybe it would be more useful for you to create a 2nd instance of the data. If it's small maybe just chuck the required columns and rows into a #temporary table. If it's larger then set up a separate DB as a DataMart that only you populate. Suck all the data in you require, then operate on it safe that nothing else will be adding to it.

share|improve this answer

Not the answer you're looking for? Browse other questions tagged or ask your own question.