Solved

Still locking problems when I use the option With (NOLOCK) in my select statement.

Posted on 2006-10-26
8
915 Views
Last Modified: 2012-05-05
A few months ago I tought someone answered my problem about 'Locks' that I had on a SQLAnywhere 9.0. (http://www.experts-exchange.com/Databases/Sybase/Q_21725790.html)

I created a table bot_articles to search very fast for words that a user inputs. A bit like google where each word get its weight (number of words in that article) and rating (points according to the postion in that article eg title=5 points, keyword=20 points, description = 2 points)
Create SQL's:
CREATE TABLE "DBA"."Bot_Article"
(
      "article_id"                integer NOT NULL ,
      "word_id"                   integer NOT NULL ,
      "scope"                          tinyint NOT NULL ,
      "type"                          smallint NOT NULL ,
      "weight"                    tinyint NOT NULL DEFAULT 1 ,
      "rating"                    tinyint NOT NULL DEFAULT 1 ,
       PRIMARY KEY ("article_id", "word_id", "rating"),
      
)
To speed up the search:
CREATE CLUSTERED INDEX "ixc_search xxxxv3_3" ON "DBA"."Bot_Article" ( word_id ,scope );

The select query is:
SELECT A0.article_id,avg(A0.scope),avg(A0.type),SUM((A0.weight*A0.rating)) as gewicht FROM Bot_Article as A0 WITH (NOLOCK) WHERE A0.word_id IN(941,......,37374) AND A0.scope in (1) GROUP BY A0.article_id,A0.scope,A0.type ORDER BY gewicht DESC FOR READ ONLY
Most of the time this is OK and doesn't produce any locks but when my Query is a bit longer (more complicated) eg when a user inputs two words:
SELECT A0.article_id,avg(A0.scope),avg(A0.type),SUM((A0.weight*A0.rating)+(A131.weight*A131.rating)) as gewicht FROM Bot_Article as A0 WITH (NOLOCK) INNER JOIN Bot_Article as A131 WITH (NOLOCK) ON (A0.article_id=A131.article_id) WHERE A0.word_id IN(941,.....,37374) AND A131.word_id IN(8546,25578) AND A0.scope in (1) GROUP BY A0.article_id,A0.scope,A0.type ORDER BY gewicht DESC FOR READ ONLY
Locks come up (only one per query).


Can anybody explain why there are locks even when I explicitly state 'With (NOLOCK)'.
- how can I log (see) where the lock is located, on what record.
And can someone give me a solution.

0
Comment
Question by:johanreynaert
  • 4
  • 4
8 Comments
 
LVL 19

Expert Comment

by:grant300
Comment Utility
If you SET SHOWPLAN ON you will see that the more complex query is undoubtedly creating a work table (internal temp table) and that it has issued some kind (probably a table lock) against that.  You may have trouble tracing it to a particular object ID because work tables, unlike #temp tables, do not get metadata entries in the system tables.

One other thing (not the issue here but something to be aware of), if you are inserting new rows in the table at the same time you are trying to query, the clustered index may get in your way, even with row level locking in place.  That's because the index is not a one-for-one mapping with a row but is a more complex structure the modification of which affects multiple rows.

Regards,
Bill
0
 

Author Comment

by:johanreynaert
Comment Utility
Bill,
I guess you're right. I saw the plan and 93% is used for the Group By. Probably it's making a temp table and that table doesn't use the Nolock option.
That brings us to the answer that I need so badly.
How can I solve this. How can I tell the databse server not to lock records (even for temp tables)?
Is there a way to control the temp tables?
Or is the only solution to delete the clustered index? (I will try this tommorow)
0
 
LVL 19

Expert Comment

by:grant300
Comment Utility
Why are you so stuck on the locks.  You said it is only creating one.

Is it blocking other queries?

If not, who cares?

Bill
0
 

Author Comment

by:johanreynaert
Comment Utility
Bill,

When there is a lock the database consumes a lot of CPU power. And it looks like the query waits untill the lock is gone and then continues to give a result.

It is not blocking other querys (transactions). but because the server is running at 100% it takes more time to give a result.
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:johanreynaert
Comment Utility
I found something that you need to know.
I scripted a page that makes 2 query and they are almost identical. Perhaps thats the problem.
I noticed this because a user submitted 2 times the same keyword on another page (that starts only one query) And that is the result:

 17:39:33  up  8:02,  2 users,  load average: 3.03, 2.68, 2.17
297 processes: 290 sleeping, 4 running, 3 zombie, 0 stopped
CPU states:  cpu    user    nice  system    irq  softirq  iowait    idle
           total   92.7%    0.0%    6.7%   0.0%     0.2%    0.0%    0.0%
           cpu00   90.8%    0.1%    8.3%   0.0%     0.3%    0.0%    0.1%
           cpu01   94.6%    0.0%    5.1%   0.0%     0.1%    0.0%    0.0%
Mem:  2055424k av, 2018252k used,   37172k free,       0k shrd,   65088k buff
                   1543772k actv,  294112k in_d,   34824k in_c
Swap: 2096472k av,  127552k used, 1968920k free                  920104k cached

  PID USER     PRI  NI  SIZE  RSS SHARE STAT %CPU %MEM   TIME CPU COMMAND
 7566 db           25   0  324M 324M  2008 R    29.7 16.1  21:04   1 dbsrv9
 7577 db           25   0  324M 324M  2008 R    29.7 16.1  21:27   0 dbsrv9
 7537 db           16   0  324M 324M  2008 S    11.9 16.1   0:17   0 dbsrv9
 7561 db           16   0  324M 324M  2008 S     6.5 16.1   0:15   0 dbsrv9
 7478 db           16   0  324M 324M  2008 S     5.1 16.1   0:17   0 dbsrv9
 7485 db           16   0  324M 324M  2008 S     4.9 16.1   0:32   0 dbsrv9
 7560 db           16   0  324M 324M  2008 S     3.0 16.1   0:25   0 dbsrv9
 9146 java        15   0  408M 407M 60968 S     1.5 20.2   1:02   0 java
 7503 db           15   0  324M 324M  2008 S     1.1 16.1   0:24   1 dbsrv9
 9023 java        15   0  408M 407M 60968 S     0.8 20.2   1:13   0 java
 7538 db           15   0  324M 324M  2008 S     0.7 16.1   0:22   0 dbsrv9
 9061 java        16   0  408M 407M 60968 S     0.5 20.2   1:11   1 java
0
 
LVL 19

Expert Comment

by:grant300
Comment Utility
The stray lock on the working table is not the problem; it is a leading indicator.

You should compare the plans for the simple and more complex queries and I bet you will find that the effort for the more complex query is substantially more.  There is some non-optimized operation taking place with the more complex queries (probably something dealing with the working table) which is eating the CPU time, not the lock.

Now that a take a closer look at the queries you are doing (boy, the formatting is really, really bad dude!) I see that you are doing a join between two data sets from the bot_article table with group bys and order bys so it is no wonder the query plan has gone way south.

What you need here is a summary table that you use to satisfy most of the queries.  You build the table once after your batch load of the base table and either refresh it or add to it (in the case of new articles) periodically.  If you were using v10 of ASA, you could simply define a materialized view and then tell it when you wanted it to refersh itself.  With v9, you have to do the work your self.

Create the table the first time with:

SELECT article_id, word_id,
            avg(scope) AS avg_scope, min(scope) AS min_scope, max(scope) as max_scope,
            avg(type) as avg_type, min(type) AS min_type, max(type) AS max_type,
            SUM((weight*rating)) as gewicht
   INTO bot_article_summary
  FROM Bot_Article WITH (NOLOCK)
GROUP BY article_id, word_id

Build your primary key on (gewicht desc, word_id, article_id)

Then build your clustered index on (word_id, avg_scope)

Finally, build your queries against bot_article_summary.

In looking at your queries, I noticed some strange constructs.  For instance, in the first one, you are returning the AVGerages of scope and type but then put them in the GROUP BY clause.  Either they don't belong int he group by or they don't belong inside the AVG functions, one or the other.  The answer to that will change the summary table construction somewhat.

Now, if you have descrete values of scope you are looking for and or want to summarize buy, you can break the data out into multiple columns so that you have the target information for each scope value in it's own column(s).

Overall, my guess is that the simple queries are working O.K. (though I am not sure they are really returning the correct results) primarily by brute force.  That falls down for the more complex queries which is where you are seeing the problems.  I would go back and re-examine the desired result from the most popular queries and design clean from there.  I think you are going to find that the current structure of your queries is more complex than they have to be, particularly if you build a summary table.

One more question, what tool are you using as a front end for forming these queries?  That can have a lot to do with issues you are seeing.  For instance, you never want to do this with MS Access as it will tend to form really screwy SQL.

Regards,
Bill
0
 

Author Comment

by:johanreynaert
Comment Utility
Bill,
1) I'm not using a design tool to make this query
2) that avg instruction for scope and type, you're right. I don't remember why I did this
3) You suggest to make another table. I will do this, because you're right. When i try the query with 7 words the load is to heavy an my server crashes.
4) My DB is 997MB so let's not talk or think about MSAccess ;)

Show Plan
SELECT

SELECT A0.article_id,A0.scope,A0.type,SUM((A0.weight*A0.rating)+(A1.weight*A1.rating)) as gewicht FROM Bot_Article as A0 WITH (NOLOCK)
INNER JOIN Bot_Article as A1 WITH (NOLOCK) ON (A0.article_id=A1.article_id)
WHERE A0.word_id IN(SELECT id FROM Bot_Word WITH (NOLOCK) WHERE word Like '%johan%' )
    AND A1.word_id IN(SELECT id FROM Bot_Word WITH (NOLOCK) WHERE word Like '%reynaert%' )
GROUP BY A0.article_id,A0.scope,A0.type ORDER BY gewicht DESC FOR READ ONLY

Node Statistics

 
                                      Estimates                              Description
 
RowsReturned                          2.8678e+008                            Number of rows returned
PercentTotalCost                      4.787                                  Run time as a percent of total query time
RunTime                               4808.3                                 Time to compute the results
CPUTime                               4808.3                                 Time required by CPU
DiskReadTime                          0                                      Time to perform reads from disk
DiskWriteTime                         0                                      Time to perform writes to disk
DiskRead                              0                                      Disk reads
DiskWrite                             0                                      Disk writes

Subtree Statistics
                                 Estimates                Description
RowsReturned                     2.8678e+008              Number of rows returned
PercentTotalCost                 100                      Run time as a percent of total query time
RunTime                          1.0044e+005              Time to compute the results
CPUTime                          27746                    Time required by CPU
DiskReadTime                     49935                    Time to perform reads from disk
DiskWriteTime                    22763                    Time to perform writes to disk
DiskRead                         2.522e+006               Disk reads
DiskWrite                        2.522e+006               Disk writes

Optimizer statistics
                              Value                Description
Costed subplans               2015                 Number of different enumeration strategies considered by the optimizer
Estimated cache pages         8164                 Estimated cache pages available for this statement
CurrentCacheSize              524032               Current cache size in kilobytes
Isolation_level               0                    Controls the locking isolation level
Optimization_goal             First-row            Optimize queries for first row or all rows
Optimization_level            9                    Reserved
Optimization_workload         Mixed                Controls whether optimizing for OLAP or mixed queries
ProductVersion                9.0.2.3221           Product version
User_estimates                Override-magic       Controls whether to respect user estimates

Select list
expr()      int
expr()      tinyint
expr()      smallint
expr()      numeric(28,0)

Work Table

Column list
expr()     int
expr()     tinyint
expr()     smallint
expr()     numeric(28,0)

Sort

Order-by
sum( expr() )  DESC

Column list
sum( expr() )  numeric(28,0)
expr()         int
expr()         tinyint
expr()         smallint

Node Statistics
                      Estimates      Description
RowsReturned          2.8678e+008    Number of rows returned
PercentTotalCost      36.784         Run time as a percent of total query time
RunTime               36947          Time to compute the results
CPUTime               16375          Time required by CPU
DiskReadTime          17356          Time to perform reads from disk
DiskWriteTime         3216.1         Time to perform writes to disk
DiskRead              8.7657e+005    Disk reads
DiskWrite             8.7657e+005    Disk writes
SortWorkTables        -              Number of work tables created for sorting
SortRowsMaterialized  -              Rate at which rows are written to sort work tables
SortSortedRuns        -              Number of sorted runs created during run formation
SortRunsWritten       -              Number of sorted runs written during sorting
SortMergePasses       -              Number of merge passes used during sorting

Subtree Statistics
                      Estimates      Description
RowsReturned          2.8678e+008    Number of rows returned
PercentTotalCost      95.213         Run time as a percent of total query time
RunTime               95636          Time to compute the results
CPUTime               22938          Time required by CPU
DiskReadTime          49935          Time to perform reads from disk
DiskWriteTime         22763          Time to perform writes to disk
DiskRead              2.522e+006     Disk reads
DiskWrite             2.522e+006     Disk writes
SortWorkTables        -              Number of work tables created for sorting
SortRowsMaterialized  -              Rate at which rows are written to sort work tables
SortSortedRuns        -              Number of sorted runs created during run formation
SortRunsWritten       -              Number of sorted runs written during sorting
SortMergePasses       -              Number of merge passes used during sorting

Hash Group By

Group-by list
expr()    int
expr()    tinyint
expr()    smallint

Aggregates
sum( expr() )   numeric(28,0)

Node Statistics
                    Estimates      Description
RowsReturned        2.8678e+008    Number of rows returned
PercentTotalCost    56.497         Run time as a percent of total query time
RunTime             56748          Time to compute the results
CPUTime             4622.5         Time required by CPU
DiskReadTime        32579          Time to perform reads from disk
DiskWriteTime       19547          Time to perform writes to disk
DiskRead            1.6454e+006    Disk reads
DiskWrite           1.6454e+006    Disk writes
HashWorkTables      -              Number of work tables created for hash-based operations
HashRowsPartitioned -              Rate at which rows are written to hash work tables
HashRowsFiltered    -              Rate at which probe rows are rejected by bit-vector filters

Subtree Statistics
                        Estimates          Description
RowsReturned            2.8678e+008        Number of rows returned
PercentTotalCost        58.429             Run time as a percent of total query time
RunTime                 58689              Time to compute the results
CPUTime                 6563               Time required by CPU
DiskReadTime            32579              Time to perform reads from disk
DiskWriteTime           19547              Time to perform writes to disk
DiskRead                1.6454e+006        Disk reads
DiskWrite               1.6454e+006        Disk writes
HashWorkTables          -                  Number of work tables created for hash-based operations
HashRowsPartitioned     -                  Rate at which rows are written to hash work tables
HashRowsFiltered        -                  Rate at which probe rows are rejected by bit-vector filters

Exists Hash Join (inner join)

Predicate
A1.word_id = Bot_Word.id

Alternative Nested Loop Join Execution

Using Index                             None
Maximum Input Size for JNL Processing   0

Node Statistics
                        Estimates       Description
RowsReturned            2.8678e+008     Number of rows returned
PercentTotalCost        1.2134          Run time as a percent of total query time
RunTime                 1218.8          Time to compute the results
CPUTime                 1218.8          Time required by CPU
DiskReadTime            0               Time to perform reads from disk
DiskWriteTime           0               Time to perform writes to disk
DiskRead                0               Disk reads
DiskWrite               0               Disk writes
HashWorkTables          -               Number of work tables created for hash-based operations
HashRowsPartitioned     -               Rate at which rows are written to hash work tables
HashRowsFiltered        -               Rate at which probe rows are rejected by bit-vector filters

Subtree Statistics
                            Estimates         Description
RowsReturned                2.8678e+008       Number of rows returned
PercentTotalCost            1.9319            Run time as a percent of total query time
RunTime                     1940.5            Time to compute the results
CPUTime                     1940.5            Time required by CPU
DiskReadTime                0                 Time to perform reads from disk
DiskWriteTime               0                 Time to perform writes to disk
DiskRead                    0                 Disk reads
DiskWrite                   0                 Disk writes
HashWorkTables              -                 Number of work tables created for hash-based operations
HashRowsPartitioned         -                 Rate at which rows are written to hash work tables
HashRowsFiltered            -                 Rate at which probe rows are rejected by bit-vector filters

Hash Join (inner join)

Predicate
A0.article_id = A1.article_id

Alternative Nested Loop Join Execution

Using Index                            Bot_Article on table A1
Maximum Input Size for JNL Processing  1.4406e+005

Node Statistics
                      Estimates          Description
RowsReturned          2.8678e+008        Number of rows returned
PercentTotalCost      0.71578            Run time as a percent of total query time
RunTime               718.96             Time to compute the results
CPUTime               718.96             Time required by CPU
....
0
 
LVL 19

Accepted Solution

by:
grant300 earned 500 total points
Comment Utility
Johan,

I only mentioned ACCESS because some people give it to end-users to use as a query tool via ODBC to a real database like ASA.  I thought that might be what was going on here.  I would, of course, never recommend ACCESS as a database for anything serious like this.

Boy, that query plan is really a mess.  One of the scary things (Haloween is right around the corner:-) is the "RowsRetruned" statistic in several spots that looks like it is estimating 286 million rows!!!

As I said before, I would backup up to 10,000 feet, do some analysis of the 2 to 5 most frequently executed types or classes of queries, and look at reworking things to satisfy those in the most effective manor I can.  I think you will wind up with a better product when you are done.

Regards,
Bill
0

Featured Post

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In this article, I will show you HOW TO: Create your first Windows Virtual Machine on a VMware vSphere Hypervisor 6.5 (ESXi 6.5) Host Server, the Windows OS we will install is Windows Server 2016.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now