#1  
Old 3rd May 2006, 12:05 AM
hhlee
 
Posts: n/a
Default 2nd sql Keep waiting for 1st sql to complete.

Hi All,

Run a 20 second sql in 1st EM, and follow with simple 2nd sql in 2nd EM. The
2nd EM sql will keep waiting until 1st EM sql complete or timeout before
show the result.

The scenario above cause a sql program hang while waiting for another time
comsuming sql to finish. However if using different transport, then both sql
can be independent and performance is great as no waiting time needed.

Please help me as a long sql reporting, will jam up another user with simple
sql. I don't have delphi hence not able to add another 10 tcp/ip transport
in nxserver, I prefer to use original nxserver.

bcb6, em1.08 em2.0505

have a nice day.

Thks & Rgds...HHLee


  #2  
Old 3rd May 2006, 12:46 AM
Eivind Bakkestuen [NDD]
 
Posts: n/a
Default Re: 2nd sql Keep waiting for 1st sql to complete.

> Run a 20 second sql in 1st EM, and follow with simple 2nd sql in 2nd
> EM. The 2nd EM sql will keep waiting until 1st EM sql complete or
> timeout before show the result.


I just tried it with winsock transport in two instances of EM 2.05 against
nxServer, and it doesnt happen for me. I started a long-running query first,
then a 2nd expected to return quickly, and it did.

--

Eivind Bakkestuen
Nexus Database Systems


  #3  
Old 3rd May 2006, 02:15 PM
hhlee
 
Posts: n/a
Default Re: 2nd sql Keep waiting for 1st sql to complete.

Hi Eivind,

> I just tried it with winsock transport in two instances of EM 2.05 against
> nxServer, and it doesnt happen for me. I started a long-running query
> first,
> then a 2nd expected to return quickly, and it did.


Sorry for not being specific, the 1st sql has hold up when at update
statement UPDATE #tmp1 set .....

The 1st sql also run 40 seconds slower using 2.0505 compare to 1.0802.

I would like to send you 2 tables with the 1st sql to support from
huathinglee(at)yahoodotcom.

TIA. Have a nice day.

Thks & Rgds...HH Lee


  #4  
Old 3rd May 2006, 02:22 PM
Surjanto
 
Posts: n/a
Default Re: 2nd sql Keep waiting for 1st sql to complete.

Eivind,

This problem also happened to me, but you said that updating or inserting to
##Temp is not a snapshot transaction thus it block the other transaction.

It means that for report that needs long sql calculation (insert and update
memory table), this process will surely lock other user from running even a
sql select.

please correct me if I am wrong.


Surjanto


  #5  
Old 3rd May 2006, 03:17 PM
Thorsten Engler [NDA]
 
Posts: n/a
Default Re: 2nd sql Keep waiting for 1st sql to complete.

>
> This problem also happened to me, but you said that updating or inserting to
> ##Temp is not a snapshot transaction thus it block the other transaction.
>
> It means that for report that needs long sql calculation (insert and update
> memory table), this process will surely lock other user from running even a
> sql select.

That's not correct. A transaction lock (in that case there will be a
shared transaction log on the source table and an exclusive lock on the
temp table) does not prevent reading from the table outside the context
of a transaction or with a snapshot transaction active. Nor will it
block normal transactions that just need a shared lock on the source
table (only reading from it). But it WILL block transactions that need
an exclusive lock on that table. (e.g. update/insert/delete statements
executed against that table).

Cheers,
Thorsten
  #6  
Old 3rd May 2006, 05:23 PM
hhlee
 
Posts: n/a
Default Re: 2nd sql Keep waiting for 1st sql to complete.

Hi Thorsten,

> That's not correct. A transaction lock (in that case there will be a
> shared transaction log on the source table and an exclusive lock on the
> temp table) does not prevent reading from the table outside the context
> of a transaction or with a snapshot transaction active. Nor will it
> block normal transactions that just need a shared lock on the source
> table (only reading from it). But it WILL block transactions that need
> an exclusive lock on that table. (e.g. update/insert/delete statements
> executed against that table).


Please have a look on the sample sql and tables that I have sent to
support(at)nexusdb.com. The 1st sql which has a long time on update temp
table held up 2nd sql (even a simple select current_date from #dummy)

Honestly, I not sure is it due to transaction or not, but using different
Transport for 2nd sql does not has the problem at all. I can live with it if
official NxServer has 10 cpTCP_IP Transports for my application. However
getting to the root cause is much more better.

TIA. Have a nice day.

Thks & Rgds...HH Lee


  #7  
Old 3rd May 2006, 07:34 PM
Surjanto
 
Posts: n/a
Default Re: 2nd sql Keep waiting for 1st sql to complete.

Thorsten,

Just try this problem again with 2.05 and it worked correctly now !

not worked with 2.04 though (some kind of deadlock)


Regards,

Surjanto


  #8  
Old 4th May 2006, 10:34 AM
Eivind Bakkestuen [NDD]
 
Posts: n/a
Default Re: 2nd sql Keep waiting for 1st sql to complete.

> Honestly, I not sure is it due to transaction or not, but using
> different Transport for 2nd sql does not has the problem at all. I
> can live with it if official NxServer has 10 cpTCP_IP Transports for
> my application. However getting to the root cause is much more better.


What OS are you testing on? Maybe its an OS limit/problem with tcp/ip.

--

Eivind Bakkestuen
Nexus Database Systems


  #9  
Old 4th May 2006, 11:53 AM
hhlee
 
Posts: n/a
Default Re: 2nd sql Keep waiting for 1st sql to complete.

Hi Eivind,
> What OS are you testing on? Maybe its an OS limit/problem with tcp/ip.


I am on XP2 Home, my cusotmers is on XP Prof and NT.

Do you see the same problem using the tables and sql that I have sent to
support(at)nexusdbdotcom. If not, then I have to think a walkaround way to
settle as majority of my customer is on XP pro, and the are more and more
repoting with sql using DDL and temp tables. I also notice the 1st sql will
also cause the respond slow for normal direct table datagrid browse and
navigate.

Have a nice day. I have a off day seminar and will check back 10 hrs later.

Thanks & Rgds...HHLee


  #10  
Old 5th May 2006, 12:58 AM
hhlee
 
Posts: n/a
Default Re: 2nd sql Keep waiting for 1st sql to complete.

Hi Eivind,

> What OS are you testing on? Maybe its an OS limit/problem with tcp/ip.


I tested 1st sql via normal tcp/ip and 2nd sql via blowfish tcp/ip, the
problem Does Not happened, 2nd sql respond straight away.

I have to change my reporting application to use blowfish tcp/ip and normal
tcp/ip for normal sql since this is only happened on my situation only.

The hang up at my customer production system does not enlighten at all.

Thanks for your time anyway.

Rgds....HHLee




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
CB2007 Code Complete Chris F nexusdb.public.discussions 11 6th December 2009 02:26 PM
Enterprisemgr sources complete? Kees de Boer nexusdb.public.discussions 3 12th April 2009 12:08 PM
Complete InnoSetup script for Server Scott Martin nexusdb.public.support 0 8th April 2006 04:04 AM
Fulltext (complete version) chuck nexusdb.public.discussions 27 1st February 2006 03:43 AM
Waiting for 2.04 Ole Willy Tuv nexusdb.public.support 1 30th October 2005 02:22 PM


All times are GMT +11. The time now is 04:32 AM.


Powered by vBulletin® Version 3.6.8
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.