-307 All threads are blocked

[Former Member]

We are new to sybase16 (upgraded from sybase7).  We have a few places in our powerbuilder application where the datawindow has a select stmt with multiple UNIONs.  If the separate stmts read from the same table, we are getting -307 All threads are blocked.  This only happens on some new servers with multiple CPUs.  It seems that sybase is splitting up the entire stmt amongst the cpus, and they somehow conflict and cause the error.  Even though we are only reading rows.  To solve this we were told to add this to the bottom:

OPTION(max_query_tasks=1)

This is working, because it is preventing sybase from splitting up the stmt amongst cpus.

I am being asked what the rule is, so that we can determine ahead of time which stmts will succeed and which will not.

Example:

SELECT * FROM table_a WHERE column_a = 'A'

UNION

SELECT * FROM table_a WHERE column_a = 'B'

Can you explain?

Thank you

[jeff_albion]

Hi Rita,

You should upgrade your SQL Anywhere 16 installation to the latest support package as this is a known and fixed issue (CR #733781 in 16.0.0.1453). While the OPTION() is a good work-around for individual queries, it isn't a great resolution as you point out, since it's hard to know which queries will fail at runtime in production.

You have two options for resolution - one option is to disable intra-query parallelism everywhere, at the cost of performance:

   SET OPTION PUBLIC.max_query_tasks = 1

This will take effect on all new connections, or after a database restart.

Otherwise, you can upgrade the Support Package for SQL Anywhere: ( http://scn.sap.com/docs/DOC-54706 )