Discussing p_web.SqlSync

[Poul]

hmmm,

i have just noticed that this flag is used to wrap most sql access with

Code: [Select]

p_web.requestdata.webserver.wait
 ! sql things ....
p_web.requestdata.webserver.release
i don't recall seeing it in earlier builds (4.3x) i just updated recently.
it smells important, should i be wrapping my own embed code in a similar fashion?  where i am opening/closing files for validation/updating running stored procedures etc.

[Bruce]

Hi Poul,

No, I don't think it's a big deal. At one stage there were reports that MsSql was having problems under load, but this didn't seem to make much difference (and can probably be removed.) The solution was more in the driver settings, documented in the "Share Knowledge" forum on this site.

cheers
Bruce

[Poul]

With MSSQL2005 i was having some blocking issues and IE timeouts,
with a webserver that shares some tables with an ERP system(that i have less control over).

Today,I have isolated one set of problems that stem from, the SQL default lock_timeout for a new connection is -1, which means wait forever until the lock is released, Nettalk's default isolation level for save is Serializable.  So if a resource is locked for any length of time before/during a netwebform save - bad things can happen. after a minute,  the browser may timeout - or the user may become impatient and click some more things, but worse there can be impacts on both the webserver and the ERP system - regardless of who started the problem .
This is even worse then a deadlock/deadly embrace because sql does  not detect and resolve.

I think that the Nettalk threads may end but the original connections/requests to sql dangle - as i see many processess in sql activity monitor that appear blocked - and they do not seem to clear/recover without killing things (screaming users prevent me from waiting more than 10/15 minutes)

The solution, for each sql connection, is SET LOCK_TIMEOUT 30000; which will now allow Nettalk to throw an error (after 30 seconds), p_web.failed can be trapped in POSTUPDATE and a retry message can be sent back to the user!

[kevin plummer]

Hi Poul,

Do you have the driver string set to /BUSYHANDLING=2 /MULTIPLEACTIVERESULTSETS=TRUE

I have seen what you are talking about even with the above driver string set.

I found if all my users used FireFox it happened a lot less - although still occasionally happened.

That project has finished so I have not had the same intensity on my app now running ntw5 and jquery.

[Poul]

Hi Kevin,
the introduction of MARS solves lots of issues - it made nettalk viable on sql for me. from help -

When MARS is set to ON, the BusyHandling switch is still active, but should not be needed.  The driver's default behavior is to retry whenever the busy message is encountered, but a busy message should never happen with MULTIPLEACTIVERESULTSETS on (or active).

When MARS is configured BUSYERRRORS are rare (gone)-  I think i found setting BUSYHANDLING could even interfere!? (4.1x days)

I could be wrong but i thought BUSY was the connection was BUSY (handling another thread.request) not BLOCKED by sql server - (could be this connection, or some unrelated one). Are you saying it would through an error if BLOCKED?

With both  the driver pooling connections and MARS handling Muliple request to SQL- makes this trickier to debug - with something like sql profiler. 

Out of the BOX - a Nettalk server might be set to Run ALL  SQL requests thru A Single SQL connection - So with MARS on, tight code and very light sql updates(ie: readonly), this is probably fine, however if something goes awry and you have 20 user + Several Updates all at once. its very difficult to see what started it as the SPID is the same for everyone. - you have to go a level deeper in sql to sort thru what is happening - out of my comfort zone), MARS can be a limitation...

TIP:  So in Development and Deploying i have taken to adding the SesssionID, User, and sometimes the formname to the APP of the connections owner - which causes a separate connection to SQL - easily visible in PROFILER and SQL Activity Monitor.  Filtering out noise is a breeze - It potentially makes many, more connections - but much,much easier to trace. (indespensable IMO), and compartmentalizes the connections - removing some benefit of mars but even less likely for contention/busy issues, which are harder to see with mars.

Regardless, I still think if a connection is BLOCKED by another SPID - the default is to wait forever. And the situation is easy to encounter if you use normal clarion thinking (browse/Update form) with nettalk default i-o.

[kevin plummer]

Hi Poul,

Thanks for your feedback suggestions.

Here are some of my obsevations:

1) I could not get my app to behave properly without the BUSYHANDLYING and MARS settings. This was just when I was testing, so not even in a multi-user environment. The main prob was if you had a parent and child browse it would not display the right records in the child if you kept clicking different parent records. Once the driver setting was set these probs went away along with probs in background code I was running. If only one or the other were set I was still having probs.

2) I'm not sure if Bruce changed something in-between Nettalk versions or I did something or the user did not refresh the cache but I had my app running for months without any locks. I put on a new version and had server locking but once all users moved to Firefox it was only occasional. This may have been incidental to the browser downloading all the scripts etc again. It may be fine again in NT5 I just haven't had a project to test on.

3) Your TIP is an interesting approach. I'm not sure it would work for me as I saw probs in testing without MARS and BUSYHANLING when I was the only user logged on. I also run a separate port for each user at the moment as I run some code from my windows app that contains Queues and I haven't got around to converting to memory tables yet. So each port/program should have its own connection. Maybe this contributes to my prob? We also run a backend windows app that hammers the SQL DB at the same time our users are connected via the web.

[Poul]

For me with Nettalk/SQL there have been 2 issues:  Busy connections VS BLOCKING -- different issues. Its so easy with nettalk's many threads to send a LOT of request thru to a single connection in short order - regardless of MARS a connection can only do one thing at a time. (MARS just juggles better).   From MSDN this statement got me thinking how a nettalk web server would behave

As an example, imagine a batch that submits a long running DML statement; say an UPDATE statement that will affect several hundred thousand records. If, while this statement is executing, a second batch is submitted, its execution will get serialized until after the UPDATE statement completes.

in this scenario -  Now depending on your design even a singleuser page could have a few nettalkbrowses and in my case some embed code to help with updates and validation etc. When a user presses SAVE - you are going to see lots of SELECTS - at least one UPDATE followed by many selects (including populating the return browse).  So with no other user you could still get BUSY waiting for the UPDATE.  When you add more Users (on the same connection) you increase the odds of busy.  And now increase the ODDS of BLOCKING.
And if you add another application (3rdparty) you really increase the odds of BLOCKING.  MARS and RETRYING wil solve most BUSY issues (by virtue of it just trying again - its just not as fast as a separate connection) .

i use 3 or 4 memory tables - (more to prove i could rather than i needed too) - i had to embed a couple of places to make sure it was synced. (I run 2 nettalk servers against the same database) works well for lookup type data.

Separate port for login, interesting, does that mean your Server is running a separate handler for EACH?  hmmm by itself, that would not guarantee separate SQL connection, would it?  When you run Activity monitor how many connections are associated with your Nettalk server?

[kevin plummer]

I actually run separate exe for each port - this is just a workaround until I can replace my queues. Not ideal having all these exe's running on my server but it was just quick fix.