avoid table lock

avoid table lock

Post by FR » Mon, 02 Apr 2001 06:21:33



there is this query that does an inner join with a table that locks the
whole table ? How can i do it in a better way ?
im using sql 7
 
 
 

avoid table lock

Post by BP Margoli » Mon, 02 Apr 2001 06:40:48


Floyd,

Is there proper indexing on the table?

If there is, then perhaps you can prove it by posting the table schemas,
along with the indexes, and some sample data. In other words, post a repro
script so that others have a better idea of what you are looking at. Without
a repro script, you'll (maybe) get a lot of suggestions, but they will all
be guesses (as is mine above) because we have no idea of what your tables,
data or query looks like.

If I might suggest: try ignoring the fact the you know your application,
tables and data, and read your post. If someone walked into your office,
asked you the question you posted (and remember that you have no knowledge
of the application, tables or data), how would you respond. Probably by
asking a host of questions before offering advice. Well, perhaps you can
post the answers to all those questions, along with the problem.

------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


Quote:> there is this query that does an inner join with a table that locks the
> whole table ? How can i do it in a better way ?
> im using sql 7


 
 
 

avoid table lock

Post by FR » Mon, 02 Apr 2001 08:02:37


this is the query

SELECT col1,col2, max(col3) as col3 FROM OPENQUERY(server1, 'select
col1,col2 from table1 where substr(col1,1,2)<> '''' and col4= ''" &
UCASE(strLotNo) & "''') as ABC inner join dbo.table2 as XYZ on (XYZ.field1 =
ABC.col1) where field2 = 'P' and Field11 in ('AB_','P6_','P7_')GROUP BY
col1,col2  order by col2

Server1 is a linked server to AS400  . Table 2 is the one that gets locked ?
what indexes do I need based on the above query ?

Floyd


> Floyd,

> Is there proper indexing on the table?

> If there is, then perhaps you can prove it by posting the table schemas,
> along with the indexes, and some sample data. In other words, post a repro
> script so that others have a better idea of what you are looking at.
Without
> a repro script, you'll (maybe) get a lot of suggestions, but they will all
> be guesses (as is mine above) because we have no idea of what your tables,
> data or query looks like.

> If I might suggest: try ignoring the fact the you know your application,
> tables and data, and read your post. If someone walked into your office,
> asked you the question you posted (and remember that you have no knowledge
> of the application, tables or data), how would you respond. Probably by
> asking a host of questions before offering advice. Well, perhaps you can
> post the answers to all those questions, along with the problem.

> ------------------------------------------
> BP Margolin
> Please reply only to the newsgroups.
> When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
> can be cut and pasted into Query Analyzer is appreciated.



> > there is this query that does an inner join with a table that locks the
> > whole table ? How can i do it in a better way ?
> > im using sql 7

 
 
 

avoid table lock

Post by BP Margoli » Mon, 02 Apr 2001 09:26:43


Floyd,

Well you really haven't provided a repro script, although with a linker
server to an AS400 I can understand how it might be a bit difficult to post
one. So all I can suggest is that you create indexes on the columns on the
SQL Server table referenced ... which if I read the code correctly are:
field1, field2 and Field11.

------------------------------------------
BP Margolin
Please reply only to the newsgroups.
When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.) which
can be cut and pasted into Query Analyzer is appreciated.


> this is the query

> SELECT col1,col2, max(col3) as col3 FROM OPENQUERY(server1, 'select
> col1,col2 from table1 where substr(col1,1,2)<> '''' and col4= ''" &
> UCASE(strLotNo) & "''') as ABC inner join dbo.table2 as XYZ on (XYZ.field1
=
> ABC.col1) where field2 = 'P' and Field11 in ('AB_','P6_','P7_')GROUP BY
> col1,col2  order by col2

> Server1 is a linked server to AS400  . Table 2 is the one that gets locked
?
> what indexes do I need based on the above query ?

> Floyd



> > Floyd,

> > Is there proper indexing on the table?

> > If there is, then perhaps you can prove it by posting the table schemas,
> > along with the indexes, and some sample data. In other words, post a
repro
> > script so that others have a better idea of what you are looking at.
> Without
> > a repro script, you'll (maybe) get a lot of suggestions, but they will
all
> > be guesses (as is mine above) because we have no idea of what your
tables,
> > data or query looks like.

> > If I might suggest: try ignoring the fact the you know your application,
> > tables and data, and read your post. If someone walked into your office,
> > asked you the question you posted (and remember that you have no
knowledge
> > of the application, tables or data), how would you respond. Probably by
> > asking a host of questions before offering advice. Well, perhaps you can
> > post the answers to all those questions, along with the problem.

> > ------------------------------------------
> > BP Margolin
> > Please reply only to the newsgroups.
> > When posting, inclusion of SQL (CREATE TABLE ..., INSERT ..., etc.)
which
> > can be cut and pasted into Query Analyzer is appreciated.



> > > there is this query that does an inner join with a table that locks
the
> > > whole table ? How can i do it in a better way ?
> > > im using sql 7

 
 
 

avoid table lock

Post by Erland Sommarsk » Tue, 03 Apr 2001 00:03:50



>this is the query

>SELECT col1,col2, max(col3) as col3 FROM OPENQUERY(server1, 'select
>col1,col2 from table1 where substr(col1,1,2)<> '''' and col4= ''" &
>UCASE(strLotNo) & "''') as ABC inner join dbo.table2 as XYZ on (XYZ.field1 =
>ABC.col1) where field2 = 'P' and Field11 in ('AB_','P6_','P7_')GROUP BY
>col1,col2  order by col2

>Server1 is a linked server to AS400  . Table 2 is the one that gets locked ?
>what indexes do I need based on the above query ?

This is not easy to say, as we don't know the tables, and we don't
know the amount of data. But I am not surprised that XYZ gets
locked. The optimizer has very little information about that remote
table.

But it might be an idea to first make the SELECT against the AS400
only, and save that data in a temp table, and then join the temp
table with the local table. It may well take longer time that the
single query with a join, but you may be relieved from the lock
on the local table. Presuming, that is, you have an index on
table2.field1.

But it would make things easier of you posted the table and index
definitions for the local table. And I would encourage you to use
the actual tables, not "table2", "field1" etc, because that makes
the discussion easier to follow.

Oh, by the way. Since you join ABC.col1 with XYZ.field1 and have
a restriction on field1 with the IN clause, you should apply that
one on ABC as well, so that less data comes over the wire.

--

 
 
 

avoid table lock

Post by Lillian Che » Tue, 03 Apr 2001 09:03:42


Define a composite index on dbo.table2 sorted in the following order:
field2, Field11, field1.  Basically, you want to minimize the number of
locks (be it row, page or extent locks) required on table2 during execution
of the query.  If you can minimize this number to be below the lock
escalation threshold for your sql server, then the query will not cause a
table lock on table2.

I defined the composite index in the above order because looking at your
query, it seems that you're selecting rows with only a single value of
field2, 3 values of Field11, and potentially a whole bunch of values for
field1 (depending on the join with ABC.col1).  So I think the most ideal way
of grouping table2 to have those rows in as few subsets as possible is to
define the composite index to sort on field2 first, then Field11, then
field1.

Also, make sure you're not running low on memory in your machine hosting sql
server.  In general, sql server tries to be a nice guy in the sense that if
allocating more locks (thus consuming more memory) will cause a page fault
on the OS level, then it'll try to minimize the number of locks (thus
conserving memory) by using table locks instead of finer granularity locks.
Say for example if you have another process running on your machine that has
a memory leak, then after a while, you'll see sql server starting to yield
memory to the OS and you'll start getting a lot of table locks for queries
that normally will only require row or page locks.  For more info on this
topic, take a look at:

http://msdn.microsoft.com/library/psdk/sql/ad_config_9sfi.htm

Good luck,

- LC



> >this is the query

> >SELECT col1,col2, max(col3) as col3 FROM OPENQUERY(server1, 'select
> >col1,col2 from table1 where substr(col1,1,2)<> '''' and col4= ''" &
> >UCASE(strLotNo) & "''') as ABC inner join dbo.table2 as XYZ on
(XYZ.field1 =
> >ABC.col1) where field2 = 'P' and Field11 in ('AB_','P6_','P7_')GROUP BY
> >col1,col2  order by col2

> >Server1 is a linked server to AS400  . Table 2 is the one that gets
locked ?
> >what indexes do I need based on the above query ?

> This is not easy to say, as we don't know the tables, and we don't
> know the amount of data. But I am not surprised that XYZ gets
> locked. The optimizer has very little information about that remote
> table.

> But it might be an idea to first make the SELECT against the AS400
> only, and save that data in a temp table, and then join the temp
> table with the local table. It may well take longer time that the
> single query with a join, but you may be relieved from the lock
> on the local table. Presuming, that is, you have an index on
> table2.field1.

> But it would make things easier of you posted the table and index
> definitions for the local table. And I would encourage you to use
> the actual tables, not "table2", "field1" etc, because that makes
> the discussion easier to follow.

> Oh, by the way. Since you join ABC.col1 with XYZ.field1 and have
> a restriction on field1 with the IN clause, you should apply that
> one on ABC as well, so that less data comes over the wire.

> --


 
 
 

1. How to avoid Lock Table ?

Hello guys... I'm still without a solution...
I was unhappy in last description of my problem.

Lets try again :
 I'm building a stored procedure that will need to insert and delete some
temporarily rows in a table based on a transaction.

so I'm looking for a way to do that without lock the table...

something like that :

Insert into MyTable with (NOLOCK) (...)
delete from MyTable with (NOLOCK) (...)
[that obviously don't work...]

The question is not read a lock table, it's about no lock the table.

Any sugestion ?

Thank's

2. Setup SQL Server 2000 to use Microsoft Clustering Service

3. How to lock a table of Interbase to avoid that others modify it

4. How to unselect a listbox

5. SQL Server: avoiding shared _table_ locks

6. Migrating to a new server

7. How to avoid annoying company wide table locking?

8. Merge Replication with ftp fails after adding A New Merge Article

9. Table is locked, workstation coughs, table stays locked.

10. How to Avoid Table Qualifications For Transferred Tables?

11. how to avoid running out of locks?