CODEWITHSUNDEEP
sqlmultithreadingt-sqlstored-procedures
Vladislav Zalesak
Vladislav Zalesak

Reputation: 713

Make a Stored Procedure Atomic without regards to outer transaction

enter image description herei was looking for a solution but so far no luck.

Description: I (meaning me as in the company) have a stored procedure that does some fairly fast and small work. Lets call it SP_Small. Its small its fass, it has an update and executes another stored procedure. Therefore trying to make it atomic by combining SELECTS and UPDATES into one select is not really workable. (one proposed solution i stumbled upon)

We have a import mechanism, that imports several different documents parallel into the database. Each import is inside a transaction (done by the application that launches the imports). Each import calls the SP_Small to do some importand work.

Problem: Right now, SP_Small is de facto atomic, but the record it access (and which is shared) remains locked until the outer transaction is commited, making the whole proces Serial, not parallel.

Requirement: Make the SP_Small atomic, making sure only one process executes it at the same time, but after its executed the rest can run parallel.

This is a simple (and ungly :)) drawing of the problem, hope that helps to clarify:

Upvotes: 2

Views: 917

Answers (2)

acfrancis
acfrancis

Reputation: 3661

Do you need to run SP_Small at the start of your "outer transactions"? The reason Process 1 blocks Process 2 is because Process 1 will hold an exclusive lock on any rows that it updates until it commits. Exclusive locks stop other transactions from even reading those locked rows. Can you run SP_Small after the long import mechanism or will that affect the result of the import? Doing that will make each process lock rows for a much smaller period of time. If that's not possible, can you commit the transaction immediately after calling SP_Small and undo it some other way if the import mechanism fails?

Upvotes: 0

RBarryYoung
RBarryYoung

Reputation: 56725

Making SP_Small Autonomous from it's process's outer transaction is one option, but it's not the only option, nor is it likely the preferred one.

Two other options are 1) Change SP_Small so that it can perform it's function without being self-blocking or "serializing". This is usually the preferred option.

Or 2) Change the Isolation Level so that it is no-longer self-blocking. Of course we cannot evaluate the work-ability of either of these without knowing more about SP_Small and why it is currently serializing.

Nor are these necessarily the only options, again depending on the details of what's really going on here and what really needs to go on here.

Upvotes: 1

Related Questions