Long running query in stored procedure and transaction

Hi!

PB2019 2353 (32 bit)

MSSQL 2012 and MSSQL 2017 (natív client 11.0)

Windows 10 64 bit

We have several long running MSSQL stored procedures with brief insert expression in begin, after a slow select to collect the results, and write results to the tables at the end. How to use these without start a transaction automatically.

I would like handling transactions in the stored procedure, because the long running queries are lock the database, it I run all stored procedure in the transaction.

Regards,

Géza

John Fauss Accepted Answer Pending Moderation

Monday, 5 July 2021 01:29 AM UTC
PowerBuilder
# 1

Greetings, Géza -

Is the value of your application's transaction object (SQLCA) AutoCommit property set to True or False?

If it is False, this is why the stored procedure is invoked within the scope of a transaction. You can refer to the PowerBuilder Help topic "transaction object', sub-topic "description of properties", and read about the impact of the AutoCommit property.

Perhaps you could set AutoCommit to True just before invoking the stored procedure, then restoring the value to False immediately after it executes. In this way, the stored procedure can be coded to manage its own transaction(s), as needed, greatly reducing the amount of elapsed time a database transaction is open.

Best regards,
John Fauss

Comment

Chris Pollach @Appeon
Monday, 5 July 2021 02:11 AM UTC

Hi John;

FYI: the AutoCommit setting is only observed during a "Connect" command.

Regards ... Chris

Helpful 0

John Fauss
Monday, 5 July 2021 03:44 AM UTC

Thanks for the information, Chris!

Helpful 0

John Fauss
Monday, 5 July 2021 14:39 PM UTC

Given that restriction, then why not create/use/destroy a 2nd transaction object that uses AutoCommit = True to execute the stored procedure? If the stored procedure is already long-running, then the additional overhead of creating, initializing, and connecting a second transaction object would be negligible overall.

Helpful 0

There are no comments made yet.

Géza Bartha Accepted Answer Pending Moderation

Saturday, 3 July 2021 17:35 PM UTC
PowerBuilder
# 2

--This is a pseudo-code, not a valid sp

create procedure testsp (
@param1 varchar(10),
@param2 varchar(20))
as

begin
--diary note
begin tran loctran
insert into sometable( timestamp, params, .....) ;

-- earlier bad or incorrect records remove.
commit tran loctran
-- The database begin tranaction automatically in a transaction object, but when I testing, I'm in opened transaction now.
-- dbcc opentran sees this session to opened transaction

-- this part of sp collect the the results (its need twenty or thirty minutes)

select ... from a big select

select .... from a big another select --, etc

--Summarize.

begin tran loctran2

--save the results
commit tran loctran2
end

-- when the sp is end, and the controll take back to the Powerbuilder, and the opened transaction committed.
-- but when collecting the data, another sessions cannot read from database.
-- I want to read the committed records only from another sessions. Dirty Read not useable.

Comment

Load more comments

Géza Bartha
Saturday, 3 July 2021 20:45 PM UTC

I Think, you misunderstood. I think, This is a correct work-flow.

Session1. I open transction (and locking the tables), modify tables, and commit or rollback.

Session2> Read the committed (!!) datas from tables.

I think the reduce of table locking time is more important, than I can read WitH (NOLOCK) any committed or non-committed data from session2 under the long a long transaction.

The problem is an automatically transaction's start when calling sp, that is not controllable in the sp. I would like to control the transactions in the sp. When I not started the transaction or I committed the last transaction, the Session2 can be read without a NOLOCK hint from tables of Session1's stored precedure, until I start the next transaction in the session1's sp. I would like to write short time transactions between the long running select in the same sp.

I think there is no need the "with nolock" in the collecting select, because it is the same session which started the transaction, and the tables are readable the inner of transaction, The another session session cannot read tables until under the transaction. When I will committed, the another session can be read the tables, but the another session cannot read. Why??

there is no method to call commit from sp to a powerbuilder transaction object. This automatically started transaction will be finish, when finish the sp, and lock the tables until finish the sp. It is a long time transaction which is no need. The transaction needs only modify, when we only reading, or calculating in the long running part of stored procedure then transaction and table lock needless.

Regards,

Géza

Helpful 0

Miguel Leeuwe
Saturday, 3 July 2021 22:35 PM UTC

yes, your workflow seems correct to me.

The reason why I say you should use the "with(nolock)" hint is this:

I'm a bit rusty with sqlserver, but as far as I remember, if you have your locking set to read "only commited data", and then do a Select without doing the with(nolock), then that same SELECT puts a shared lock on the resultset of the table(s). The way to prevent that shared lock, is by using the with(nolock) hint.

Now if I understand you well, you are saying that you get a new transaction start right after the first commit before doing any Select? I don't think that would happen, unless you do a new "BEGIN TRAN or some update or delete or insert statement.

On this page https://www.mssqltips.com/sqlservertutorial/3305/what-does-begin-tran-rollback-tran-and-commit-tran-mean/ it explains that when you do a COMMIT, then your @@TRANCOUNT will be decrease by a value of 1. Maybe you should check in your code on how many open transactions you have. IF for example @@TRANCOUNT = 2 and you then do a COMMIT, @@TRANCOUNT would become 1, but you would still have that single transaction open.

Sorry, but I can't test anything as I don't have SQL Server installed.

regards.

Helpful 0

Miguel Leeuwe
Saturday, 3 July 2021 22:48 PM UTC

To be clear: the WITH(NOLOCK) does NOT mean that another user can read uncommited changes done by you. It simply means that the table won't be locked for him while you are running your SELECT.

I would say, just try adding the WITH(NOLOCK) and see if your problem goes away.

regards

Helpful 0

There are no comments made yet.

Miguel Leeuwe Accepted Answer Pending Moderation

Saturday, 3 July 2021 16:30 PM UTC
PowerBuilder
# 3

I'm not sure if I understand you question, some example code of what you are doing might be helpful for us to understand you better.

Could this be what you are looking for? Select * from ..... WITH (NOLOCK);

https://www.mssqltips.com/sqlservertip/2470/understanding-the-sql-server-nolock-hint/

https://www.sqlshack.com/understanding-impact-clr-strict-security-configuration-setting-sql-server-2017/

regards

Comment

Load more comments

Miguel Leeuwe
Saturday, 3 July 2021 18:13 PM UTC

True, if only we'd ever would have though of that option, life would have been so much easier !!!

Good tip!

Helpful 0

Chris Pollach @Appeon
Saturday, 3 July 2021 18:19 PM UTC

That's what I'm here for ... should have called me first - LOL! ;-)

Helpful 0

Miguel Leeuwe
Saturday, 3 July 2021 18:24 PM UTC

lol,

The thing is, when I started to work for that company their existing apps already had this with nolock everywhere in their code. So when writing a new select, it wasn't that much work either to add these few words and never stopped to think about a better way of doing it. (plus you would have to check on all overrides everywhere to make sure your dynamic change would be applied).

regards.

Helpful 0

There are no comments made yet.

Resolved Long running query in stored procedure and transaction