Frequent lockups when sharing files between desktop and web

[peterH]

Hi all,

I've got a desktop app with a NTWS front end and using SQL2005.

Quite often (some days more, some days less) the desktop app freezes when the users open certain forms. These forms all use files that are being updated (or inserted) from the web app. One file is updated by the web app using plain template gen'd code, another is being updated using prop:sql.
 
I'm suspecting a lock somewhere and I'm wondering if there's something you can do in the web app to make sure recs/tables are properly released. There are no explicit locks being set anywhere in my code.

Any hints are most welcome!

Peter

[kevin plummer]

for starters look at this thread: http://www.nettalkcentral.com/index.php?option=com_smf&Itemid=36&topic=403.0

For me I have found the following works pretty well.

/MULTIPLERESULTSETS=TRUE /BUSYHANDLING=2

Generally you should see you app work a lot better on the web esp when you have multiple users logged in although this won't stop the locks.

To stop or reduce the locks, I use 'Set Lock_Timeout 10000' and  'Set Lock_Timeout -1' in strategic places.

If you look at SQL enterprise manager you can see the process's blocked and blocking to try to figure out where in your code the problems are.

You may also find moving some code to sprocs also helps.

In my case it was a lot of trial and error and and adding debug points using debug view to see where the problems where.

HTH's

Kevin

[peterH]

Hi Kevin,

Thanks for your reply.

I already had BUSYHANDLING in place so now I'm adding MARS to see if that eases the situation. Since the database is being accesses from both the web and desktop I assume I'll have to add this setting in both apps (I'm doing it in my code rather than in the dict).

That thing about Lock_Timeout: where does one find info about that? And what do you consider "strategic places"?

TIA
Peter

[kevin plummer]

Since the database is being accesses from both the web and desktop I assume I'll have to add this setting in both apps (I'm doing it in my code rather than in the dict).

If you use FM3 then there is a spot to stick the driver string in there as the driver string needs to be set on the first table the DB opens. I don't think it needs to be in the desktop app but as I have it set in FM3 I just leave it there.

Just Google Lock_Timeout but basically all it does is release a table if it can't get access within a certain time rather than hanging forever.

So I have something like the following:

        DUMMY{Prop:SQL} = 'Set Lock_Timeout 10000'      !wait 10 seconds
        If Errorcode()
          Message('PO Form Set Lock_Timeout 10000 = ' & DUMMY{Prop:SQL})
        End

        DUMMY{Prop:SQL} = 'Select Count(*) as DUM_Field01 From NOTIFY Where NOTI_UserName = <39>' & Clip(POT:CurrentApproval) & '<39>'
        Next(DUMMY)
        If Errorcode() = 90  !File is in use so just ignore
          Message('PO Browse=' & Clip(POT:CurrentApproval) & clip(errorcode() & ' ' & error()) & ' :: ' & fileerrorcode() & ' ' & clip(fileerror()))
          !
        Elsif DUM:Field01 = 0 or Errorcode()
         ! If Errorcode() ~= 10
            Message('PO Browse add Notify=' & Clip(POT:CurrentApproval) & clip(errorcode() & ' ' & error()) & ' :: ' & fileerrorcode() & ' ' & clip(fileerror()))
            NOTIFY{Prop:Sql} = 'Insert Into Notify ' &|
                               '(NOTI_UserName,NOTI_Orders,NOTI_UnapprovedOrders,NOTI_Time,NOTI_Date) '&|
                               'Values (<39>' &  Clip(POT:CurrentApproval) & '<39>,1,0,'&|
                               '<123>ts <39>1900-01-01 ' & Format(Clock(),@T04) & '<39><125>,' &|
                               '<123>ts <39>' & Format(Today(),@D010-) & ' 00:00:00<39><125>)'
            If Errorcode()
             ! SetClipboard(NOTIFY{Prop:Sql})
              Message('a PO Browse add Notify=' & Clip(POT:CurrentApproval) & clip(errorcode() & ' ' & error()) & ' :: ' & fileerrorcode() & ' ' & clip(fileerror()))
            End
         ! End
        Else

          NOTIFY{Prop:Sql} = 'Update Notify Set ' &|
                                 'NOTI_Orders = NOTI_Orders + 1 ' &|
                                 'Where NOTI_UserName = <39>' & Clip(POT:CurrentApproval) & '<39>'
          If Errorcode()
           ! SetClipboard(NOTIFY{Prop:Sql})
            Message('PO Browse update Notify=' & Clip(POT:CurrentApproval) & clip(errorcode() & ' ' & error()) & ' :: ' & fileerrorcode() & ' ' & clip(fileerror()))
          End
        End
        DUMMY{Prop:SQL} = 'Set Lock_Timeout -1'      !wait 10 seconds
        If Errorcode()
          Message('PO Browse Set Lock_Timeout -1 = ' & DUMMY{Prop:SQL})
        End

I use CSMessage to log the messages straight to disk.

As for strategic places, I use SQL Enterprise manager to monitor the locks. If you use Prop:SQL then you will see your statement that is hanging. So I modified my code to to do a select count(*) to detect if the table was in use before proceeding (as above). It just then became a process of elimination until I was rid of them all. If you don't use Prop:SQL then it will just show Cursor. I also used debug view to pinpoint some problems.

HTH's

Kevin

[peterH]

Hi Kevin,

Thanks a bunch for your elaborate response and example code. I thought Lock_Timeout was a hidden NT gem - thus my question 

Peter