Re: Long Transaction

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Roy Alingcastre (RoyAlingcastre_at_discussions.microsoft.com)
Date: 06/12/04 

Date: Sat, 12 Jun 2004 15:31:01 -0700

Thanks Joel, I like your idea and I'll put it forward. Unfortunately, updates are done by our code, where the statement is dynamically constructed depending on which fields are changed. However if we append the condition you've suggest in the WHERE clause of the UPDATE statement and check for the affected record (should be equal to 1 if success) every update, then we can detect if the update has failed and rollback the transaction.

Roy

"Joel" wrote:

> If you have designed your app such that users can only perform updates by
> stored procs, then there is a farily easy solution.
>
> Create a table called CONFIG with two fields:
>
> CONFIG_ID Varchar(25)
> CONFIG_VALUE Varchar(200)
>
> This is just a generic way of doing the table so that you can store
> unrelated items. Put a record in there with ID='LOCKED' and Value='0'.
> Since it's a character field you can use YES/NO or whatever you want.
>
> At the beginning of your long process update that record to have a value of
> '-1'. At the end of that process change it back to '0'.
>
> In any stored proc that updates one of those tables, put a line like this at
> the top of the proc:
>
> If Exists (Select * from CONFIG Where CONFIG_ID='LOCKED' and
> CONFIG_VALUE='-1')
> Return(1)
>
> You can also use this trick if you have a customized front end. Have your
> edit screens check the value of this record before allowing users to update.
> If the value = locked then set the screen to read-only.
>
> "Roy Alingcastre" <RoyAlingcastre@discussions.microsoft.com> wrote in
> message news:66754594-FCFC-4E11-84BA-AB05B10AE8E4@microsoft.com...
> > Hi,
> >
> > I got a bad feeling about this.....
> >
> > Our application involves around 10 tables in sql server and records in
> them are all interrelated. The application goes through 2 phases, first
> phase is to validate the records in the database, and depending which record
> you start from you could end up going processing all the records stored in
> all the tables. After the first phase all records that have been processed
> should be locked for any updates until the second phase is complete (for big
> system, to complete the whole operation could take hours). However while the
> operation is in progress we still want other users to browse throught the
> records as long as no updates are made.
> >
> > Our brilliant Software Architect recommends to start a big transaction at
> the beginning of phase 1 with isolation level of repeatable read and commit
> at the end of phase2 to guarantee that no processed records are updated
> until the end of this "giant" operation. My concern is, all of the sql
> technical/best practices materials I've read so far discourages you to use
> long transactions and higher isolation level. But our guru said as long as
> we handle blocking and deadlocks gracefully in our application we'll be ok.
> But I still believe that this is a recipe for disaster. Can you suggest any
> other issues that we may not considering aside from blocking and deadlocks.
> > I got a feeling that the database is unusable to other users even just for
> browsing while this giant operation is happening.
> >
> > TIA.
> > Roy
>
>
>

Relevant Pages

  • Re: Hibernate not working
    ... Really Roy? ... Well it is true that I downloaded over 100 updates since SP3 was ... can tell you that security updates is the weakest of all security ... The one minor regret I may have, though, about going to SP3, is that I can't ...
    (microsoft.public.windowsxp.general)
  • Re: Long Transaction
    ... stored procs, then there is a farily easy solution. ... In any stored proc that updates one of those tables, put a line like this at ... long transactions and higher isolation level. ... browsing while this giant operation is happening. ...
    (microsoft.public.sqlserver.programming)
  • Re: Writing updates from a wide DataTable
    ... Don't do stored procs, just for the heck of writing a ... Now, if the number of structures the query might take i.e. it may have 2,4, ... >following the recommendations to use stored procedures to apply updates to ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Jagger Update
    ... > You got this from Googleguy, please tell me you don't believe ... Is that how you read it Roy? ... > If this is true why bother doing updates if all sites stay as they ... > When you check the SERPs you tend to find most SERPs are barely ...
    (alt.internet.search-engines)
  • Re: Jagger 3 (Another Update)
    ... > As with most of Google's updates they've kindly initiated it in the ... There?s still some minor flux on that data center, ... Roy ... Prev by Date: ...
    (alt.internet.search-engines)