Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

An added SELECT causes a program to get stuck

9 posts in General Discussion Last posting was on 2012-05-25 09:44:50.0Z
Kon T. Amusse Posted on 2012-05-24 17:16:54.0Z
From: "Kon T. Amusse" <kontamusse@invalid.invalid>
Newsgroups: sybase.public.ase.general
Subject: An added SELECT causes a program to get stuck
Lines: 1
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=original
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
Importance: Normal
X-Newsreader: Microsoft Windows Live Mail 15.4.3555.308
X-MimeOLE: Produced By Microsoft MimeOLE V15.4.3555.308
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4fbe6d06@forums-1-dub>
Date: 24 May 2012 10:16:54 -0700
X-Trace: forums-1-dub 1337879814 10.22.241.152 (24 May 2012 10:16:54 -0700)
X-Original-Trace: 24 May 2012 10:16:54 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31143
Article PK: 74032

There is a bug that I cannot fix.
I have a software that works. I add some lines inside a function and it does
not work anymore.

function MyFunction( int N )
{
Execute( "SELECT name FROM TableA WHERE TableA.code=N" ) ;
$Result = FetchAll() ;

// Begin of added code
Execute( "SELECT email FROM TableB,TableC WHERE TableB.key=TableC.key
AND TableC.code=N" ) ;
FetchAll() ; // Discard the output
// End of added code

RETURN $Result;
}

Without the added code, everything works ok.
With the added code, MyFunction() returns, but later on the program will be
stuck. This happens every single time.
When it gets stuck, I can see in the list of database locks that there are
two SPID running, both from my application. In fact, there is no other user
running on this database, no other application running on this database, and
I only launch an instance of my application.
By using "dbcc sqltext" I can see that the 2 SPIDs are running the following
queries:

SELECT email FROM TableB,TableC WHERE TableB.key=TableC.key AND
TableC.code=22
SELECT * FROM TableC WHERE TableC.Code=34

This really seems a problem of deadlock. But how can 2 SELECT statements
cause a deadlock?
In order to be sure, I made a further experiment. I created a database
called ALTDB, and I loaded there an identical copy of my database. Then I
changed my added SELECT statement to read:

SELECT email FROM ALTDB..TableB,ALTDB..TableC WHERE
ALTDB..TableB.key=ALTDB..TableC.key AND ALTDB..TableC.code=N

This causes the problem to disappear, and the application arrives happily to
the end.
Can you explain this without involving aliens?


Bret Halford Posted on 2012-05-24 17:38:39.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:12.0) Gecko/20120428 Thunderbird/12.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: An added SELECT causes a program to get stuck
References: <4fbe6d06@forums-1-dub>
In-Reply-To: <4fbe6d06@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4fbe721f@forums-1-dub>
Date: 24 May 2012 10:38:39 -0700
X-Trace: forums-1-dub 1337881119 10.22.241.152 (24 May 2012 10:38:39 -0700)
X-Original-Trace: 24 May 2012 10:38:39 -0700, vip152.sybase.com
Lines: 66
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31145
Article PK: 74033


On 5/24/2012 11:16 AM, Kon T. Amusse wrote:
> There is a bug that I cannot fix.
> I have a software that works. I add some lines inside a function and it
> does not work anymore.
>
> function MyFunction( int N )
> {
> Execute( "SELECT name FROM TableA WHERE TableA.code=N" ) ;
> $Result = FetchAll() ;
>
> // Begin of added code
> Execute( "SELECT email FROM TableB,TableC WHERE TableB.key=TableC.key
> AND TableC.code=N" ) ;
> FetchAll() ; // Discard the output
> // End of added code
>
> RETURN $Result;
> }
>
> Without the added code, everything works ok.
> With the added code, MyFunction() returns, but later on the program will
> be stuck. This happens every single time.
> When it gets stuck, I can see in the list of database locks that there
> are two SPID running, both from my application. In fact, there is no
> other user running on this database, no other application running on
> this database, and I only launch an instance of my application.
> By using "dbcc sqltext" I can see that the 2 SPIDs are running the
> following queries:
>
> SELECT email FROM TableB,TableC WHERE TableB.key=TableC.key AND
> TableC.code=22
> SELECT * FROM TableC WHERE TableC.Code=34
>
> This really seems a problem of deadlock. But how can 2 SELECT statements
> cause a deadlock?
> In order to be sure, I made a further experiment. I created a database
> called ALTDB, and I loaded there an identical copy of my database. Then
> I changed my added SELECT statement to read:
>
> SELECT email FROM ALTDB..TableB,ALTDB..TableC WHERE
> ALTDB..TableB.key=ALTDB..TableC.key AND ALTDB..TableC.code=N
>
> This causes the problem to disappear, and the application arrives
> happily to the end.
> Can you explain this without involving aliens?

What language/interface are you programming your client in?
What version of ASE are you running against?
Does sp_who show one blocking the other?
Are any other processes being blocked by either of these spids?
What locks are held by these two connections?
Run dbcc stacktrace(<spid>) against both spids, what are the outputs?

One select can appear to block (or even deadlock) with another
if there is a blocked non-shared lock request in the mix. ASE
will allow up to 3 shared lock requests to bypass the blocked
incompatible lock, but after that additional shared lock requests
start being blocked by the blocked non-shared lock. By default,
syslocks only shows granted locks, the result is that the granted
shared lock appears to be blocking shared lock requests that
are really being blocked by a blocked non-shared lock.

dbcc traceon(1202) causes blocked lock requests to show up in
syslocks, which can sometimes shine light on the issue.

-bret


Kon T. Amusse Posted on 2012-05-24 19:52:59.0Z
From: "Kon T. Amusse" <kontamusse@invalid.invalid>
Newsgroups: sybase.public.ase.general
References: <4fbe6d06@forums-1-dub> <4fbe721f@forums-1-dub>
In-Reply-To: <4fbe721f@forums-1-dub>
Subject: Re: An added SELECT causes a program to get stuck
Lines: 7
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
Importance: Normal
X-Newsreader: Microsoft Windows Live Mail 15.4.3555.308
X-MimeOLE: Produced By Microsoft MimeOLE V15.4.3555.308
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4fbe919b@forums-1-dub>
Date: 24 May 2012 12:52:59 -0700
X-Trace: forums-1-dub 1337889179 10.22.241.152 (24 May 2012 12:52:59 -0700)
X-Original-Trace: 24 May 2012 12:52:59 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31146
Article PK: 74035

The database is Adaptive Server Enterprise/15.0.3/EBF
16374/P/x86_64/Enterprise Linux/ase1503/2669/64-bit/FBO/Wed Nov 12 16:52:49
2008
Programming language PHP with Zend framework (I have 10+years of experience
with PHP and ASE, none with Zend)
sp_who

0 93 recv sleep webuser webuser
RHEL5-4-HA-eReta-VRS-MK2.unv.o 0 SIAMTEST2 tempdb2
AWAITING COMMAND 0
0 132 lock sleep webuser webuser
RHEL5-4-HA-eReta-VRS-MK2.unv.o 93 SIAMTEST2 tempdb SELECT
0

93 is SELECT email FROM TableB,TableC WHERE TableB.key=TableC.key AND
TableC.code=N
132 is SELECT * FROM TableC WHERE TableC.Code=34

Do I read correctly that the query I added (93) is blocking the other?

At the moment, I am running the program in a separate test environment,
where there are no other programs running.
Before realizing there was a problem, this program was running in
production, and whenever it was stuck all other programs accessing these
tables were queueing, so it basically destroyed the whole server
I have some difficulties following your explanation about blocks. But in my
simple mind, I cannot figure out how the query I added can block other
queries, after it is executed and the results are read...???

"Bret Halford" wrote in message news:4fbe721f@forums-1-dub...

On 5/24/2012 11:16 AM, Kon T. Amusse wrote:
> There is a bug that I cannot fix.
> I have a software that works. I add some lines inside a function and it
> does not work anymore.
>
> function MyFunction( int N )
> {
> Execute( "SELECT name FROM TableA WHERE TableA.code=N" ) ;
> $Result = FetchAll() ;
>
> // Begin of added code
> Execute( "SELECT email FROM TableB,TableC WHERE TableB.key=TableC.key
> AND TableC.code=N" ) ;
> FetchAll() ; // Discard the output
> // End of added code
>
> RETURN $Result;
> }
>
> Without the added code, everything works ok.
> With the added code, MyFunction() returns, but later on the program will
> be stuck. This happens every single time.
> When it gets stuck, I can see in the list of database locks that there
> are two SPID running, both from my application. In fact, there is no
> other user running on this database, no other application running on
> this database, and I only launch an instance of my application.
> By using "dbcc sqltext" I can see that the 2 SPIDs are running the
> following queries:
>
> SELECT email FROM TableB,TableC WHERE TableB.key=TableC.key AND
> TableC.code=22
> SELECT * FROM TableC WHERE TableC.Code=34
>
> This really seems a problem of deadlock. But how can 2 SELECT statements
> cause a deadlock?
> In order to be sure, I made a further experiment. I created a database
> called ALTDB, and I loaded there an identical copy of my database. Then
> I changed my added SELECT statement to read:
>
> SELECT email FROM ALTDB..TableB,ALTDB..TableC WHERE
> ALTDB..TableB.key=ALTDB..TableC.key AND ALTDB..TableC.code=N
>
> This causes the problem to disappear, and the application arrives
> happily to the end.
> Can you explain this without involving aliens?

What language/interface are you programming your client in?
What version of ASE are you running against?
Does sp_who show one blocking the other?
Are any other processes being blocked by either of these spids?
What locks are held by these two connections?
Run dbcc stacktrace(<spid>) against both spids, what are the outputs?

One select can appear to block (or even deadlock) with another
if there is a blocked non-shared lock request in the mix. ASE
will allow up to 3 shared lock requests to bypass the blocked
incompatible lock, but after that additional shared lock requests
start being blocked by the blocked non-shared lock. By default,
syslocks only shows granted locks, the result is that the granted
shared lock appears to be blocking shared lock requests that
are really being blocked by a blocked non-shared lock.

dbcc traceon(1202) causes blocked lock requests to show up in
syslocks, which can sometimes shine light on the issue.

-bret


"Mark A. Parsons" <iron_horse Posted on 2012-05-24 22:20:39.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: An added SELECT causes a program to get stuck
References: <4fbe6d06@forums-1-dub> <4fbe721f@forums-1-dub> <4fbe919b@forums-1-dub>
In-Reply-To: <4fbe919b@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4fbeb437$1@forums-1-dub>
Date: 24 May 2012 15:20:39 -0700
X-Trace: forums-1-dub 1337898039 10.22.241.152 (24 May 2012 15:20:39 -0700)
X-Original-Trace: 24 May 2012 15:20:39 -0700, vip152.sybase.com
Lines: 79
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31147
Article PK: 74036

I'm not a front-end programmer but fwiw ...

1 - is this function really suppose to open 2 separate connections into the dataserver? is there a reason why the 2
execute()'s aren't serialized over a single connection?

2 - if the function is capable of spawning 2 concurrent/parallel connections, could there be an issue with the
fetchall() being serialized (ie, you're deadlocking on the application side)?

3 - what isolation level are the function/execute() connections running under (select @@isolation)?

4 - a detailed list of the locks involved (see Bret's post) would also be helpful

On 05/24/2012 13:52, Kon T. Amusse wrote:
> The database is Adaptive Server Enterprise/15.0.3/EBF 16374/P/x86_64/Enterprise Linux/ase1503/2669/64-bit/FBO/Wed Nov 12
> 16:52:49 2008
> Programming language PHP with Zend framework (I have 10+years of experience with PHP and ASE, none with Zend)
> sp_who
>
> 0 93 recv sleep webuser webuser RHEL5-4-HA-eReta-VRS-MK2.unv.o 0 SIAMTEST2 tempdb2 AWAITING COMMAND 0
> 0 132 lock sleep webuser webuser RHEL5-4-HA-eReta-VRS-MK2.unv.o 93 SIAMTEST2 tempdb SELECT 0
>
> 93 is SELECT email FROM TableB,TableC WHERE TableB.key=TableC.key AND TableC.code=N
> 132 is SELECT * FROM TableC WHERE TableC.Code=34
>
> Do I read correctly that the query I added (93) is blocking the other?
>
> At the moment, I am running the program in a separate test environment, where there are no other programs running.
> Before realizing there was a problem, this program was running in production, and whenever it was stuck all other
> programs accessing these tables were queueing, so it basically destroyed the whole server
> I have some difficulties following your explanation about blocks. But in my simple mind, I cannot figure out how the
> query I added can block other queries, after it is executed and the results are read...???
>
> "Bret Halford" wrote in message news:4fbe721f@forums-1-dub...
>
> On 5/24/2012 11:16 AM, Kon T. Amusse wrote:
>> There is a bug that I cannot fix.
>> I have a software that works. I add some lines inside a function and it
>> does not work anymore.
>>
>> function MyFunction( int N )
>> {
>> Execute( "SELECT name FROM TableA WHERE TableA.code=N" ) ;
>> $Result = FetchAll() ;
>>
>> // Begin of added code
>> Execute( "SELECT email FROM TableB,TableC WHERE TableB.key=TableC.key
>> AND TableC.code=N" ) ;
>> FetchAll() ; // Discard the output
>> // End of added code
>>
>> RETURN $Result;
>> }
>>
>> Without the added code, everything works ok.
>> With the added code, MyFunction() returns, but later on the program will
>> be stuck. This happens every single time.
>> When it gets stuck, I can see in the list of database locks that there
>> are two SPID running, both from my application. In fact, there is no
>> other user running on this database, no other application running on
>> this database, and I only launch an instance of my application.
>> By using "dbcc sqltext" I can see that the 2 SPIDs are running the
>> following queries:
>>
>> SELECT email FROM TableB,TableC WHERE TableB.key=TableC.key AND
>> TableC.code=22
>> SELECT * FROM TableC WHERE TableC.Code=34
>>
>> This really seems a problem of deadlock. But how can 2 SELECT statements
>> cause a deadlock?
>> In order to be sure, I made a further experiment. I created a database
>> called ALTDB, and I loaded there an identical copy of my database. Then
>> I changed my added SELECT statement to read:
>>
>> SELECT email FROM ALTDB..TableB,ALTDB..TableC WHERE
>> ALTDB..TableB.key=ALTDB..TableC.key AND ALTDB..TableC.code=N
>>
>> This causes the problem to disappear, and the application arrives
>> happily to the end.
>> Can you explain this without involving aliens?


Bret Halford Posted on 2012-05-24 23:28:37.0Z
From: Bret Halford <bret@sybase.com>
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:12.0) Gecko/20120428 Thunderbird/12.0.1
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: An added SELECT causes a program to get stuck
References: <4fbe6d06@forums-1-dub> <4fbe721f@forums-1-dub> <4fbe919b@forums-1-dub> <4fbeb437$1@forums-1-dub>
In-Reply-To: <4fbeb437$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4fbec425$1@forums-1-dub>
Date: 24 May 2012 16:28:37 -0700
X-Trace: forums-1-dub 1337902117 10.22.241.152 (24 May 2012 16:28:37 -0700)
X-Original-Trace: 24 May 2012 16:28:37 -0700, vip152.sybase.com
Lines: 112
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31148
Article PK: 74037

I don't think these two spids are from the same execution
of the function - the two queries are showing different
values for "N", reflecting different executions of the function.

On the sp_who, you didn't give the full output. So just to verify,
there are no other spids showing as being blocked?

Regarding #4, the exact steps we would like you to take are
dbcc traceon(1202)
go
select * from master..syslocks
go
dbcc traceoff(1202)
go

On 5/24/2012 4:20 PM, Mark A. Parsons wrote:
> I'm not a front-end programmer but fwiw ...
>
> 1 - is this function really suppose to open 2 separate connections into
> the dataserver? is there a reason why the 2 execute()'s aren't
> serialized over a single connection?
>
> 2 - if the function is capable of spawning 2 concurrent/parallel
> connections, could there be an issue with the fetchall() being
> serialized (ie, you're deadlocking on the application side)?
>
> 3 - what isolation level are the function/execute() connections running
> under (select @@isolation)?
>
> 4 - a detailed list of the locks involved (see Bret's post) would also
> be helpful
>
> On 05/24/2012 13:52, Kon T. Amusse wrote:
>> The database is Adaptive Server Enterprise/15.0.3/EBF
>> 16374/P/x86_64/Enterprise Linux/ase1503/2669/64-bit/FBO/Wed Nov 12
>> 16:52:49 2008
>> Programming language PHP with Zend framework (I have 10+years of
>> experience with PHP and ASE, none with Zend)
>> sp_who
>>
>> 0 93 recv sleep webuser webuser RHEL5-4-HA-eReta-VRS-MK2.unv.o 0
>> SIAMTEST2 tempdb2 AWAITING COMMAND 0
>> 0 132 lock sleep webuser webuser RHEL5-4-HA-eReta-VRS-MK2.unv.o 93
>> SIAMTEST2 tempdb SELECT 0
>>
>> 93 is SELECT email FROM TableB,TableC WHERE TableB.key=TableC.key AND
>> TableC.code=N
>> 132 is SELECT * FROM TableC WHERE TableC.Code=34
>>
>> Do I read correctly that the query I added (93) is blocking the other?
>>
>> At the moment, I am running the program in a separate test
>> environment, where there are no other programs running.
>> Before realizing there was a problem, this program was running in
>> production, and whenever it was stuck all other
>> programs accessing these tables were queueing, so it basically
>> destroyed the whole server
>> I have some difficulties following your explanation about blocks. But
>> in my simple mind, I cannot figure out how the
>> query I added can block other queries, after it is executed and the
>> results are read...???
>>
>> "Bret Halford" wrote in message news:4fbe721f@forums-1-dub...
>>
>> On 5/24/2012 11:16 AM, Kon T. Amusse wrote:
>>> There is a bug that I cannot fix.
>>> I have a software that works. I add some lines inside a function and it
>>> does not work anymore.
>>>
>>> function MyFunction( int N )
>>> {
>>> Execute( "SELECT name FROM TableA WHERE TableA.code=N" ) ;
>>> $Result = FetchAll() ;
>>>
>>> // Begin of added code
>>> Execute( "SELECT email FROM TableB,TableC WHERE TableB.key=TableC.key
>>> AND TableC.code=N" ) ;
>>> FetchAll() ; // Discard the output
>>> // End of added code
>>>
>>> RETURN $Result;
>>> }
>>>
>>> Without the added code, everything works ok.
>>> With the added code, MyFunction() returns, but later on the program will
>>> be stuck. This happens every single time.
>>> When it gets stuck, I can see in the list of database locks that there
>>> are two SPID running, both from my application. In fact, there is no
>>> other user running on this database, no other application running on
>>> this database, and I only launch an instance of my application.
>>> By using "dbcc sqltext" I can see that the 2 SPIDs are running the
>>> following queries:
>>>
>>> SELECT email FROM TableB,TableC WHERE TableB.key=TableC.key AND
>>> TableC.code=22
>>> SELECT * FROM TableC WHERE TableC.Code=34
>>>
>>> This really seems a problem of deadlock. But how can 2 SELECT statements
>>> cause a deadlock?
>>> In order to be sure, I made a further experiment. I created a database
>>> called ALTDB, and I loaded there an identical copy of my database. Then
>>> I changed my added SELECT statement to read:
>>>
>>> SELECT email FROM ALTDB..TableB,ALTDB..TableC WHERE
>>> ALTDB..TableB.key=ALTDB..TableC.key AND ALTDB..TableC.code=N
>>>
>>> This causes the problem to disappear, and the application arrives
>>> happily to the end.
>>> Can you explain this without involving aliens?


"Mark A. Parsons" <iron_horse Posted on 2012-05-25 00:27:37.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 6.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: An added SELECT causes a program to get stuck
References: <4fbe6d06@forums-1-dub> <4fbe721f@forums-1-dub> <4fbe919b@forums-1-dub> <4fbeb437$1@forums-1-dub> <4fbec425$1@forums-1-dub>
In-Reply-To: <4fbec425$1@forums-1-dub>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4fbed1f9$1@forums-1-dub>
Date: 24 May 2012 17:27:37 -0700
X-Trace: forums-1-dub 1337905657 10.22.241.152 (24 May 2012 17:27:37 -0700)
X-Original-Trace: 24 May 2012 17:27:37 -0700, vip152.sybase.com
Lines: 117
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31149
Article PK: 74038

Looks like there may be a cut-n-paste issue.

While I agree N != 34, I would've expected to see an actual number instead of 'N'.

On 05/24/2012 17:28, Bret Halford wrote:
> I don't think these two spids are from the same execution
> of the function - the two queries are showing different
> values for "N", reflecting different executions of the function.
>
> On the sp_who, you didn't give the full output. So just to verify,
> there are no other spids showing as being blocked?
>
> Regarding #4, the exact steps we would like you to take are
> dbcc traceon(1202)
> go
> select * from master..syslocks
> go
> dbcc traceoff(1202)
> go
>
>
>
> On 5/24/2012 4:20 PM, Mark A. Parsons wrote:
>> I'm not a front-end programmer but fwiw ...
>>
>> 1 - is this function really suppose to open 2 separate connections into
>> the dataserver? is there a reason why the 2 execute()'s aren't
>> serialized over a single connection?
>>
>> 2 - if the function is capable of spawning 2 concurrent/parallel
>> connections, could there be an issue with the fetchall() being
>> serialized (ie, you're deadlocking on the application side)?
>>
>> 3 - what isolation level are the function/execute() connections running
>> under (select @@isolation)?
>>
>> 4 - a detailed list of the locks involved (see Bret's post) would also
>> be helpful
>>
>> On 05/24/2012 13:52, Kon T. Amusse wrote:
>>> The database is Adaptive Server Enterprise/15.0.3/EBF
>>> 16374/P/x86_64/Enterprise Linux/ase1503/2669/64-bit/FBO/Wed Nov 12
>>> 16:52:49 2008
>>> Programming language PHP with Zend framework (I have 10+years of
>>> experience with PHP and ASE, none with Zend)
>>> sp_who
>>>
>>> 0 93 recv sleep webuser webuser RHEL5-4-HA-eReta-VRS-MK2.unv.o 0
>>> SIAMTEST2 tempdb2 AWAITING COMMAND 0
>>> 0 132 lock sleep webuser webuser RHEL5-4-HA-eReta-VRS-MK2.unv.o 93
>>> SIAMTEST2 tempdb SELECT 0
>>>
>>> 93 is SELECT email FROM TableB,TableC WHERE TableB.key=TableC.key AND
>>> TableC.code=N
>>> 132 is SELECT * FROM TableC WHERE TableC.Code=34
>>>
>>> Do I read correctly that the query I added (93) is blocking the other?
>>>
>>> At the moment, I am running the program in a separate test
>>> environment, where there are no other programs running.
>>> Before realizing there was a problem, this program was running in
>>> production, and whenever it was stuck all other
>>> programs accessing these tables were queueing, so it basically
>>> destroyed the whole server
>>> I have some difficulties following your explanation about blocks. But
>>> in my simple mind, I cannot figure out how the
>>> query I added can block other queries, after it is executed and the
>>> results are read...???
>>>
>>> "Bret Halford" wrote in message news:4fbe721f@forums-1-dub...
>>>
>>> On 5/24/2012 11:16 AM, Kon T. Amusse wrote:
>>>> There is a bug that I cannot fix.
>>>> I have a software that works. I add some lines inside a function and it
>>>> does not work anymore.
>>>>
>>>> function MyFunction( int N )
>>>> {
>>>> Execute( "SELECT name FROM TableA WHERE TableA.code=N" ) ;
>>>> $Result = FetchAll() ;
>>>>
>>>> // Begin of added code
>>>> Execute( "SELECT email FROM TableB,TableC WHERE TableB.key=TableC.key
>>>> AND TableC.code=N" ) ;
>>>> FetchAll() ; // Discard the output
>>>> // End of added code
>>>>
>>>> RETURN $Result;
>>>> }
>>>>
>>>> Without the added code, everything works ok.
>>>> With the added code, MyFunction() returns, but later on the program will
>>>> be stuck. This happens every single time.
>>>> When it gets stuck, I can see in the list of database locks that there
>>>> are two SPID running, both from my application. In fact, there is no
>>>> other user running on this database, no other application running on
>>>> this database, and I only launch an instance of my application.
>>>> By using "dbcc sqltext" I can see that the 2 SPIDs are running the
>>>> following queries:
>>>>
>>>> SELECT email FROM TableB,TableC WHERE TableB.key=TableC.key AND
>>>> TableC.code=22
>>>> SELECT * FROM TableC WHERE TableC.Code=34
>>>>
>>>> This really seems a problem of deadlock. But how can 2 SELECT statements
>>>> cause a deadlock?
>>>> In order to be sure, I made a further experiment. I created a database
>>>> called ALTDB, and I loaded there an identical copy of my database. Then
>>>> I changed my added SELECT statement to read:
>>>>
>>>> SELECT email FROM ALTDB..TableB,ALTDB..TableC WHERE
>>>> ALTDB..TableB.key=ALTDB..TableC.key AND ALTDB..TableC.code=N
>>>>
>>>> This causes the problem to disappear, and the application arrives
>>>> happily to the end.
>>>> Can you explain this without involving aliens?
>


Kon T. Amusse Posted on 2012-05-25 09:44:50.0Z
From: "Kon T. Amusse" <kontamusse@invalid.invalid>
Newsgroups: sybase.public.ase.general
References: <4fbe6d06@forums-1-dub> <4fbe721f@forums-1-dub> <4fbe919b@forums-1-dub> <4fbeb437$1@forums-1-dub>
In-Reply-To: <4fbeb437$1@forums-1-dub>
Subject: Re: An added SELECT causes a program to get stuck
Lines: 7
MIME-Version: 1.0
Content-Type: text/plain; format=flowed; charset="iso-8859-1"; reply-type=response
Content-Transfer-Encoding: 7bit
X-Priority: 3
X-MSMail-Priority: Normal
Importance: Normal
X-Newsreader: Microsoft Windows Live Mail 15.4.3555.308
X-MimeOLE: Produced By Microsoft MimeOLE V15.4.3555.308
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4fbf5492$1@forums-1-dub>
Date: 25 May 2012 02:44:50 -0700
X-Trace: forums-1-dub 1337939090 10.22.241.152 (25 May 2012 02:44:50 -0700)
X-Original-Trace: 25 May 2012 02:44:50 -0700, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31151
Article PK: 74040

Why the 2 execute()'s are not serialized?
This sound like a key question, I will investigate

"Mark A. Parsons" wrote in message news:4fbeb437$1@forums-1-dub...

I'm not a front-end programmer but fwiw ...

1 - is this function really suppose to open 2 separate connections into the
dataserver? is there a reason why the 2
execute()'s aren't serialized over a single connection?

2 - if the function is capable of spawning 2 concurrent/parallel
connections, could there be an issue with the
fetchall() being serialized (ie, you're deadlocking on the application
side)?

3 - what isolation level are the function/execute() connections running
under (select @@isolation)?

4 - a detailed list of the locks involved (see Bret's post) would also be
helpful

On 05/24/2012 13:52, Kon T. Amusse wrote:
> The database is Adaptive Server Enterprise/15.0.3/EBF
> 16374/P/x86_64/Enterprise Linux/ase1503/2669/64-bit/FBO/Wed Nov 12
> 16:52:49 2008
> Programming language PHP with Zend framework (I have 10+years of
> experience with PHP and ASE, none with Zend)
> sp_who
>
> 0 93 recv sleep webuser webuser RHEL5-4-HA-eReta-VRS-MK2.unv.o 0 SIAMTEST2
> tempdb2 AWAITING COMMAND 0
> 0 132 lock sleep webuser webuser RHEL5-4-HA-eReta-VRS-MK2.unv.o 93
> SIAMTEST2 tempdb SELECT 0
>
> 93 is SELECT email FROM TableB,TableC WHERE TableB.key=TableC.key AND
> TableC.code=N
> 132 is SELECT * FROM TableC WHERE TableC.Code=34
>
> Do I read correctly that the query I added (93) is blocking the other?
>
> At the moment, I am running the program in a separate test environment,
> where there are no other programs running.
> Before realizing there was a problem, this program was running in
> production, and whenever it was stuck all other
> programs accessing these tables were queueing, so it basically destroyed
> the whole server
> I have some difficulties following your explanation about blocks. But in
> my simple mind, I cannot figure out how the
> query I added can block other queries, after it is executed and the
> results are read...???
>
> "Bret Halford" wrote in message news:4fbe721f@forums-1-dub...
>
> On 5/24/2012 11:16 AM, Kon T. Amusse wrote:
>> There is a bug that I cannot fix.
>> I have a software that works. I add some lines inside a function and it
>> does not work anymore.
>>
>> function MyFunction( int N )
>> {
>> Execute( "SELECT name FROM TableA WHERE TableA.code=N" ) ;
>> $Result = FetchAll() ;
>>
>> // Begin of added code
>> Execute( "SELECT email FROM TableB,TableC WHERE TableB.key=TableC.key
>> AND TableC.code=N" ) ;
>> FetchAll() ; // Discard the output
>> // End of added code
>>
>> RETURN $Result;
>> }
>>
>> Without the added code, everything works ok.
>> With the added code, MyFunction() returns, but later on the program will
>> be stuck. This happens every single time.
>> When it gets stuck, I can see in the list of database locks that there
>> are two SPID running, both from my application. In fact, there is no
>> other user running on this database, no other application running on
>> this database, and I only launch an instance of my application.
>> By using "dbcc sqltext" I can see that the 2 SPIDs are running the
>> following queries:
>>
>> SELECT email FROM TableB,TableC WHERE TableB.key=TableC.key AND
>> TableC.code=22
>> SELECT * FROM TableC WHERE TableC.Code=34
>>
>> This really seems a problem of deadlock. But how can 2 SELECT statements
>> cause a deadlock?
>> In order to be sure, I made a further experiment. I created a database
>> called ALTDB, and I loaded there an identical copy of my database. Then
>> I changed my added SELECT statement to read:
>>
>> SELECT email FROM ALTDB..TableB,ALTDB..TableC WHERE
>> ALTDB..TableB.key=ALTDB..TableC.key AND ALTDB..TableC.code=N
>>
>> This causes the problem to disappear, and the application arrives
>> happily to the end.
>> Can you explain this without involving aliens?


Manish Negandhi [TeamSybase] Posted on 2012-05-24 17:34:08.0Z
From: "Manish Negandhi [TeamSybase]" <nospam_negandhi.manish@gmail.com>
Newsgroups: sybase.public.ase.general
References: <4fbe6d06@forums-1-dub>
Subject: Re: An added SELECT causes a program to get stuck
Lines: 64
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.3664
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.3664
X-RFC2646: Format=Flowed; Response
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4fbe7110@forums-1-dub>
Date: 24 May 2012 10:34:08 -0700
X-Trace: forums-1-dub 1337880848 10.22.241.152 (24 May 2012 10:34:08 -0700)
X-Original-Trace: 24 May 2012 10:34:08 -0700, vip152.sybase.com
X-Authenticated-User: teamsybase
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31144
Article PK: 74034


"Kon T. Amusse" <kontamusse@invalid.invalid> wrote in message
news:4fbe6d06@forums-1-dub...
> There is a bug that I cannot fix.
> I have a software that works. I add some lines inside a function and it
> does not work anymore.
>
> function MyFunction( int N )
> {
> Execute( "SELECT name FROM TableA WHERE TableA.code=N" ) ;
> $Result = FetchAll() ;
>
> // Begin of added code
> Execute( "SELECT email FROM TableB,TableC WHERE TableB.key=TableC.key
> AND TableC.code=N" ) ;
> FetchAll() ; // Discard the output
> // End of added code
>
> RETURN $Result;
> }
>
> Without the added code, everything works ok.
> With the added code, MyFunction() returns, but later on the program will
> be stuck. This happens every single time.
> When it gets stuck, I can see in the list of database locks that there are
> two SPID running, both from my application. In fact, there is no other
> user running on this database, no other application running on this
> database, and I only launch an instance of my application.
> By using "dbcc sqltext" I can see that the 2 SPIDs are running the
> following queries:
>
> SELECT email FROM TableB,TableC WHERE TableB.key=TableC.key AND
> TableC.code=22
> SELECT * FROM TableC WHERE TableC.Code=34
>
> This really seems a problem of deadlock. But how can 2 SELECT statements
> cause a deadlock?
> In order to be sure, I made a further experiment. I created a database
> called ALTDB, and I loaded there an identical copy of my database. Then I
> changed my added SELECT statement to read:
>
> SELECT email FROM ALTDB..TableB,ALTDB..TableC WHERE
> ALTDB..TableB.key=ALTDB..TableC.key AND ALTDB..TableC.code=N
>
> This causes the problem to disappear, and the application arrives happily
> to the end.
> Can you explain this without involving aliens?

Can you directly connect to database using isql and run the first two select
statements you posted ?
Also open one more isql session and post results of below query

SELECT @@isolation
go
SELECT blocked,* from master..sysprocesses
go
SELECT * from master..syslocks
go

-HTH
Manish Negandhi
[TeamSybase]


Michael Peppler [Team Sybase] Posted on 2012-05-25 04:47:58.0Z
From: "Michael Peppler [Team Sybase]" <mpeppler@peppler.org>
Organization: Peppler Consulting SARL
Subject: Re: An added SELECT causes a program to get stuck
User-Agent: Pan/0.14.2 (This is not a psychotic episode. It's a cleansing moment of clarity.)
Message-ID: <pan.2012.05.25.04.49.08.273475@peppler.org>
Newsgroups: sybase.public.ase.general
References: <4fbe6d06@forums-1-dub> <4fbe7110@forums-1-dub>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Date: 24 May 2012 21:47:58 -0700
X-Trace: forums-1-dub 1337921278 10.22.241.152 (24 May 2012 21:47:58 -0700)
X-Original-Trace: 24 May 2012 21:47:58 -0700, vip152.sybase.com
Lines: 69
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:31150
Article PK: 74039

I'd also add a select * from syslogshold. I'm guessing this connection is
in "chained mode" (aka AutoCommit OFF), so holds transactions open...

Michael

On Thu, 24 May 2012 10:34:08 -0700, Manish Negandhi [TeamSybase] wrote:

>
> "Kon T. Amusse" <kontamusse@invalid.invalid> wrote in message
> news:4fbe6d06@forums-1-dub...
>> There is a bug that I cannot fix.
>> I have a software that works. I add some lines inside a function and it
>> does not work anymore.
>>
>> function MyFunction( int N )
>> {
>> Execute( "SELECT name FROM TableA WHERE TableA.code=N" ) ; $Result =
>> FetchAll() ;
>>
>> // Begin of added code
>> Execute( "SELECT email FROM TableB,TableC WHERE TableB.key=TableC.key
>> AND TableC.code=N" ) ;
>> FetchAll() ; // Discard the output
>> // End of added code
>>
>> RETURN $Result;
>> }
>> }
>> Without the added code, everything works ok. With the added code,
>> MyFunction() returns, but later on the program will be stuck. This
>> happens every single time. When it gets stuck, I can see in the list of
>> database locks that there are two SPID running, both from my
>> application. In fact, there is no other user running on this database,
>> no other application running on this database, and I only launch an
>> instance of my application. By using "dbcc sqltext" I can see that the 2
>> SPIDs are running the following queries:
>>
>> SELECT email FROM TableB,TableC WHERE TableB.key=TableC.key AND
>> TableC.code=22
>> SELECT * FROM TableC WHERE TableC.Code=34
>>
>> This really seems a problem of deadlock. But how can 2 SELECT statements
>> cause a deadlock?
>> In order to be sure, I made a further experiment. I created a database
>> called ALTDB, and I loaded there an identical copy of my database. Then
>> I changed my added SELECT statement to read:
>>
>> SELECT email FROM ALTDB..TableB,ALTDB..TableC WHERE
>> ALTDB..TableB.key=ALTDB..TableC.key AND ALTDB..TableC.code=N
>>
>> This causes the problem to disappear, and the application arrives
>> happily to the end.
>> Can you explain this without involving aliens?
>
> Can you directly connect to database using isql and run the first two
> select statements you posted ?
> Also open one more isql session and post results of below query
>
> SELECT @@isolation
> go
> SELECT blocked,* from master..sysprocesses go
> SELECT * from master..syslocks
> go
>
> -HTH
> Manish Negandhi
> [TeamSybase]