CODEWITHSUNDEEP
sqlsql-servert-sql
DoronBM
DoronBM

Reputation: 515

SQL Server Table locking within a store procedure without using a transaction

I need to execute a store procedure which includes 3 queries referring to the same table:

MERGE …

SELECT X FROM TABLE WHERE { BLA BLA }

UPDATE Y FROM TABLE WHERE { BLA BLA }

The store procedure should be thread safe and should executed as an atomic operation.

Currently I am using a transaction with serializable isolation level and a WITH ( XLOCK, TABLOCK ) hint on every query.

Is there a way to sustain a table lock for the time span of the store procedure without using a transaction which causing a performance penalty?

Cheers, Doron

Upvotes: 0

Views: 1126

Answers (2)

Remus Rusanu
Remus Rusanu

Reputation: 294187

Whenever a developer chooses TABLOCKX or (XLOCK, TABLOCK) it had instantaneously lost the right to ask questions about performance.

Is not the transaction that is causing performance penalties. Is holding the locks. So your question really is:

Is there a way to produce performance penalties without the effect of producing performance penalties?

The answer is no, there is not such way.

Upvotes: 3

Ryk
Ryk

Reputation: 3102

Doron, to accomplish what you want to do, can only be done using a transaction. I don't understand why you say a transaction causes a performance overhead, that is a completely untrue statement. Unless what you mean is that your transaction takes eg. 10 seconds, and in that 10 seconds other transactions are blocked.

Now I regularly work and design databases that has to sustain around 80K transactions per second, and doing this you learn a few tricks. What I would suggest you do is to take a step back and re-evaluate your query and table architecture, and if this is a highly transactional database, the first this I suggest is to get rid of any foreign key constraints, that is a performance hit on any transactional db.

The other thing is to look at indexes, do you have the right indexes, and are you perhaps over-indexing tables that have to be inserted into and updated? This can cause massive perf impacts!

Maybe can I suggest if you cannot re-architect tables etc, think outside the box a little, perhaps select the data you want (with nolock) into temp tables, and then perform your merges etc.

Perhaps if you give me a more concrete example, I can assist more.

But for now, tell me what you can and cannot do. Hope it helps!

Upvotes: 0

Related Questions