Post reply

Help with transactions...

  • March 26, 2008 at 8:46 am

    #69752

    Hi all. I'm developing an application where a stored procedure runs periodically and updates/inserts data from other datasources. The theory is that i don't want "partial updated data" to show up when i'm querying the tables being updated. To test this i wrote a simple sp like this:

    CREATE PROCEDURE dbo.ModifyTheData

    @text varchar(50)

    AS

    BEGIN

    DECLARE @TranName VARCHAR(20);

    SELECT @TranName = 'MyTransaction';

    BEGIN TRANSACTION @TranName

    INSERT INTO dbo.SPTEST (Text) VALUES(@text)

    --UPDATE dbo.SPTEST SET [Text] = @text WHERE ID = 1

    WAITFOR DELAY '00:00:10'

    COMMIT TRANSACTION @TranName

    END

    GO

    so this sp inserts a new record into dbo.SPTEST (with an identity column), and then just sits there for 10 seconds before committing the transaction.

    The problem is that if i try to do a SELECT * FROM dbo.SPTEST, it waits untill the transaction inside the sp has been committed before returning data, except in the read uncommitted isolation level, which is of course not what i want; it should only return committed data, without waiting for the transaction to commit (i think this is because of locks?)

    I tried with a simple update statement (currently commented) as well and had the same issue there.

    hope i'm clear in my explanation...

  • March 26, 2008 at 9:42 am

    #794152

    select * hits every row. If you have an update in a transaction locking those rows, which is what it does, then you can't read every row.

    Try reading specific rows, not all of them and you should be ok.

  • March 26, 2008 at 9:45 am

    #794156

    i tried doing SELECT * FROM dbo.SPTEST WHERE ID = 5 but it will still wait untill the sp finished though

  • March 28, 2008 at 8:05 am

    #795195

    noone any idea?...

  • March 29, 2008 at 1:12 pm

    #795624

    Maybe.

    How many rows are in the table? What are the indexes on the table?

    If there are no indexes, sql will do a table scan to find your rows. That means reading every row of the table event though you only want 1. It has to read the entire table to find the rows you're looking for. That, most likely, means a table lock. Since another session has an exclusive lock on one row, The table scan has to wait.

    The SELECT * may also force a table scan (even in the presence of indexes) because of the required lookup to the heap/cluster to fetch all the columns. Much better to select just the columns you want so that it's possible to design a covering index.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 29, 2008 at 1:38 pm

    #795625

    pho (3/28/2008)

    noone any idea?...

    Yes... take the delay out and remove the transaction. The data will be available instantaneously and everyone will be happy. 😛

    There's gotta be more to this than you're stating. There is almost never a need for a long winded RBAR transaction. Instead of showing us your experimental code, show us the real code that you're having a problem with.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 29, 2008 at 4:13 pm

    #795631

    there is no code i am having issues with at the moment.

    this is the situation the application is in currently:

    there are a number of tables used for reporting purposes. when reports need to be generated (initiated by a user), the following logic is executed:

    - check when the data was last "updated" (data in the tables is data from external applications). if this is > 15 minutes ago, the tables are updated during which the client waits. This can currently take up to 2 minutes.

    - after the data is correct, reports are generated

    however, the client would rather have a job update the data _every_ 15 minutes, regardless of whether a user is trying to generate reports or not, this in order to reduce the client waiting time.

    the issue that first crossed my mind was; what happens if the data is queried during this update? incomplete/mixed data is not what anyone would want... so i made this spike solution to make sure i could do the updates in a separate transaction, so that if you query the data, it either gets the "old", or the "new" data, but no mixture of the two.

    so then i made the test queries in the topic start.

    is there a better solution for my problem?

  • March 29, 2008 at 5:22 pm

    #795647

    Well, snapshot isolation is one way to go.

    Another is what we call the "Shell Game". Instead of one set of tables, you have two (or three, depending on your situation), a front set 'P' and a background set: 'P_maint'.

    Users generating reports use the 'P' set of tables. Meanwhile, at the 15-minute mark the P_maint tables are emptied and re-loaded. Once the re-load is done, start a transaction and rename the P table set to P_tmp, then rename P_maint to 'P' and commit the transaction. Then rename P_tmp to P_maint.

    Instead of renaming, this can also be done with redirecting views or (best of all) by forcing all reports through stored procedures that do the redirection themselves while still insuring that only a consistent table set is used throughout a single report run.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • March 29, 2008 at 6:38 pm

    #795653

    Pho...

    Sorry... I didn't understand your problem before.

    Barry is absolutely spot on in his post above... two sets of tables and a view that is repointed (very much like a synonym can be in 2k5) to the newly updated table(s) is likely the best, easiest, safest, and fastest thing to do. All keys and indexes stay in tact and the users will only have to wait about 1 or 2 milliseconds while the repointing of the view occurs.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • March 29, 2008 at 8:53 pm

    #795662

    thanks for the replies! both seem perfectly valid options! having read up on snapshot isolation, it seems like this will be the simplest and easiest-to-maintain solution for my problem.

    any apparent suggestions why NOT to do it and use the temp table approach instead?

  • March 29, 2008 at 10:05 pm

    #795669

    Snapshot Isolation does impose some overhead, both performance and diskspace, but it does offer a very simple way to present a transactionaly consistent view to your users.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply