• You are here:  
  • Home
  • Q&A
  • Q&A
  • PowerBuilder
  • Long running query in stored procedure and transaction

Subscribe via RSS
Toggle Submenu
  1. Categories
  2. Recent
  3. Tags
View Replies (3)

Resolved Long running query in stored procedure and transaction

  1. How-to
0
Votes
Undo
  1. Géza Bartha
    Géza Bartha
  2. PowerBuilder
  3. Saturday, 3 July 2021 11:00 AM UTC

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

 

 

UrlEncode how or for what to use it?
Alguien tiene un ejemplo de como exportar un dataw...
Responses (3)
John Fauss
John Fauss Accepted Answer Pending Moderation
  1. Monday, 5 July 2021 01:29 AM UTC
  2. PowerBuilder
  3. # 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
  1. Chris Pollach @Appeon
  2. Monday, 5 July 2021 02:11 AM UTC
Hi John;

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

Regards ... Chris
  1. Helpful
John Fauss
  1. John Fauss
  2. Monday, 5 July 2021 03:44 AM UTC
Thanks for the information, Chris!
  1. Helpful
John Fauss
  1. John Fauss
  2. 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.
  1. Helpful
There are no comments made yet.
Géza Bartha
Géza Bartha Accepted Answer Pending Moderation
  1. Saturday, 3 July 2021 17:35 PM UTC
  2. PowerBuilder
  3. # 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
  1. Géza Bartha
  2. 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
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. 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.



  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. 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
  1. Helpful
There are no comments made yet.
Miguel Leeuwe
Miguel Leeuwe Accepted Answer Pending Moderation
  1. Saturday, 3 July 2021 16:30 PM UTC
  2. PowerBuilder
  3. # 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
  1. Miguel Leeuwe
  2. 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!
  1. Helpful
Chris Pollach @Appeon
  1. Chris Pollach @Appeon
  2. Saturday, 3 July 2021 18:19 PM UTC
That's what I'm here for ... should have called me first - LOL! ;-)
  1. Helpful
Miguel Leeuwe
  1. Miguel Leeuwe
  2. 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.
  1. Helpful
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.