1

SQL Server 2016 SP2

This is the logic that I am trying to implement. (Please don't mind the incomplete syntax)

CREATE PROCEDURE ProcName

    BEGIN TRANSACTION [TRAN1]

        UPDATE tbl -- Update 1 - This takes around 30 seconds
        SET col1 = 1, col2 = b
        WHERE ...

        COMMIT TRANSACTION [TRAN1]

    BEGIN TRANSACTION [TRAN2]

        UPDATE tbl -- Update 2 - This takes around 2 minutes
        SET col3 = d, col4 = e
        WHERE ....

        COMMIT TRANSACTION [TRAN2]

Over here, my main aim is to get the col1 and col2 updated first, and then the SP can take its time to update col3 and col4.

However, I noticed that the stored procedure executes the entire script in 1 single block and data in all the 4 columns appear together after more than 2 minutes. I just keep refreshing the SELECT to check for the data while the stored procedure is executing.

When I use SELECT * FROM tbl with (nolock), the data in col1 and col2 appears in around 30 seconds as expected and then the data in col3 and col4 appear in about 2 minutes.

Looking at this, it seems like the data from the 1st UPDATE is not really committed when TRAN1 is committed.

We cannot use batch separators (GO) in a SP so I thought the transactions could work. I'm not sure why it's not committing the rows.

Any inputs on this would be appreciated.

NOTE: the stored procedure is called at the end of a Python script. I feel the only other way to achieve what I need if the transactions don't work would be to split the 2nd UPDATE in another stored procedure and call that stored procedure after the 1st stored procedure call from python.

Viraj A
  • 83
  • 6
  • 1
    Transactions aren't batches. `NOLOCK` means you code has bugs which you tried to cover up. `NOLOCK` doesn't mean `don't lock`, it means `read dirty and duplicate data, throw at random and take even more locks`. What is the *actual* problem and *actual* stored procedure code? Why update the *same* table multiple times? – Panagiotis Kanavos Mar 22 '21 at 17:58
  • 1
    BTW transactions work. T-SQL doesn't have nested transactions though, so if you execute this stored procedure under an external transaction, changes will appear only if the external transaction commits. Which is just fine - why would you want to see *incomplete* data after 30 seconds? – Panagiotis Kanavos Mar 22 '21 at 18:00
  • There are a couple of ways to separate a stored procedure into batches, 1) put the code you want in different batches into their own SPs and then call each of those child SPs from the original/parent SP. Or 2) use separate dynamic SQL Executions within the same SP. Nonetheless, as @PanagiotisKanavos noted, Transactions and batches are different things. – RBarryYoung Mar 22 '21 at 18:06
  • By default SQL Server uses implicit transactions, You have no error handling or rollback logic so you don't even need explicit transactions as in your example. – Stu Mar 22 '21 at 18:25
  • So I believe putting the 2 updates in 2 separate stored procedures is the best option here? I don't mind doing that. Just thought it would be nicer if they could go under a single SP. Also, I believe what I need is to separate them into batches, and transactions won't help irrespective of explicitly committing the transaction, and it would run all the code within the SP as a single batch, is that correct? if that's the case then I think I know what to do next.. – Viraj A Mar 22 '21 at 18:42
  • @PanagiotisKanavos .My point of mentioning nolock was that I wanted to see uncommitted rows for testing and it showed me that the 1st update statement updated the columns before the 2nd update statement kicked in and to me it seems that the rows are not committed until the entire stored procedure was executed.Please correct me if I'm wrong. Sorry for the wrong use of the word transaction. Update statement multiple times as 2nd one joins another table with a million rows and the 1st 2 columns are more important,so I don't want them delayed. Script runs every hour. Data Could be used in reports. – Viraj A Mar 22 '21 at 18:45
  • Completely confused: why not do the two statements as one `update`? If they cannot be combined then put the whole thing in one transaction if you need to, otherwise don't bother with a transaction at all: each statement anyway has its own transaction. What are you actaully trying to do? – Charlieface Mar 22 '21 at 19:13
  • Using the two stored procedures it would help troubleshooting. If you have them in one stored procedure you need a way to figure out which one erred and then have to dig more and SQL Server loves isolation. Also I would recommend using some kind of error checking in the stored proc by using the TRY...CATCH syntax. It can make you life easier if errs occur. – Wes Palmer Mar 22 '21 at 19:41
  • ok I put them in 2 separate SPs and the same thing is happening. I put a print statement in python, then called SP1, then again print and then SP2. Basically what is happening is col1 and col2 are updated and then the next SP runs and updates col3 and col4. The data in col1 and col2 is not displayed until col3 and col4 are also updated. This seems strange to me, looks like the table remains locked until col3 and col4 are updated. I would expect a normal behaviour to be that col1 and col2 data should be displayed (committed) and then the next update begins. – Viraj A Mar 22 '21 at 19:56
  • 2
    Got it sorted. Python was the culprit. So I found out that when I connect from python using pyodbc, there is a parameter called autocommit which is False by default. So the python connection acts as an external transaction and prevents UPDATE from COMMITting. I set autocommit=True in my python script in the connection and it works like a charm. @Charlieface True, didn't need to create transactions, it just sequentially runs the updates and commits them. The weird behaviour had got me confused, until I found out what Python is doing. – Viraj A Mar 22 '21 at 22:41
  • Does this answer your question? [In Python, Using pyodbc, How Do You Perform Transactions?](https://stackoverflow.com/questions/1063770/in-python-using-pyodbc-how-do-you-perform-transactions) *This is just so that it can be closed as duplicate, @PanagiotisKanavos please vote* – Charlieface Mar 23 '21 at 00:32
  • @Charlieface apologies for such a late response. Yes I believe they are related. Maybe I didn't find it initially as I didn't perform the correct search.. as I thought it had something to do with the stored procedure (instead of the python script). – Viraj A Aug 19 '22 at 19:58

0 Answers0