Q: Padding pages and page locking

Q: Padding pages and page locking

Post by Lee Mill » Fri, 10 Mar 1995 09:13:47




>In an effort to supply artificial keys to a client application I've ran
>across some interesting locking. To avoid lock contention on a small
>table I padded each record to be 1962 bytes (max allowed) using series
of
>char fields. When I go to UPDATE one record one page gets locked, it
>works fine.  Although, when I INSERT a new record multiple pages are
>locked including those pages with pre-existing records.  Why would the
>pre-existing pages get locked at INSERT time?  Is using char fields to
>pad the best solution to avoid contention? HOLDLOCK is out of the
>question due to DEADLOCK potential, IDENTITY is out of the question
>because I need the key upfront before modification time. My transactions
>are very short and concurency is a major concern. Thanks for any input!

Do you have any indexes defined on this table?
 
 
 

Q: Padding pages and page locking

Post by Andy Geor » Fri, 10 Mar 1995 02:33:48


In an effort to supply artificial keys to a client application I've ran
across some interesting locking. To avoid lock contention on a small
table I padded each record to be 1962 bytes (max allowed) using series of
char fields. When I go to UPDATE one record one page gets locked, it
works fine.  Although, when I INSERT a new record multiple pages are
locked including those pages with pre-existing records.  Why would the
pre-existing pages get locked at INSERT time?  Is using char fields to
pad the best solution to avoid contention? HOLDLOCK is out of the
question due to DEADLOCK potential, IDENTITY is out of the question
because I need the key upfront before modification time. My transactions
are very short and concurency is a major concern. Thanks for any input!

 
 
 

Q: Padding pages and page locking

Post by Teresa A Lars » Tue, 14 Mar 1995 23:42:14


|> In an effort to supply artificial keys to a client application I've ran
|> across some interesting locking. To avoid lock contention on a small
|> table I padded each record to be 1962 bytes (max allowed) using series of
|> char fields. When I go to UPDATE one record one page gets locked, it
|> works fine.  Although, when I INSERT a new record multiple pages are
|> locked including those pages with pre-existing records.  Why would the
|> pre-existing pages get locked at INSERT time?  Is using char fields to
|> pad the best solution to avoid contention? HOLDLOCK is out of the
|> question due to DEADLOCK potential, IDENTITY is out of the question
|> because I need the key upfront before modification time. My transactions
|> are very short and concurency is a major concern. Thanks for any input!

Do you have a clustered index on the table?  If yes, then inserting
new records could be having the affect of reorganizing the data to
"make room" for the new record.  

You might want to revisit the decision to have a clustered index on
the table.  They are usually most beneficial for queries that do range
searches (because they keep the data physically together).  It sounds
like your use of the table would grab one record at a time; maybe a
non-clustered index would be better.

Of course, if you don't have a clustered index, then ignore the above.
:-)

                                Teresa Larson

+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+
| Teresa A. Larson - Hughes STX Corporation                            |
| NASA/GSFC Code 933.0                        voice:  (301) 286-7867   |
| Greenbelt, Maryland  20771                  fax:    (301) 286-1777   |

+~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~+
                        Standard disclaimer ...

 
 
 

Q: Padding pages and page locking

Post by Cael » Wed, 15 Mar 1995 15:03:09



: >
: >In an effort to supply artificial keys to a client application I've ran

: >across some interesting locking. To avoid lock contention on a small
: >table I padded each record to be 1962 bytes (max allowed) using series
: of
: >char fields. When I go to UPDATE one record one page gets locked, it
: >works fine.  Although, when I INSERT a new record multiple pages are
: >locked including those pages with pre-existing records.  Why would the
: >pre-existing pages get locked at INSERT time?  Is using char fields to
: >pad the best solution to avoid contention? HOLDLOCK is out of the
: >question due to DEADLOCK potential, IDENTITY is out of the question
: >because I need the key upfront before modification time. My transactions

: >are very short and concurency is a major concern. Thanks for any input!

: Do you have any indexes defined on this table?

Yep, sounds like an index page locking problem ...


Caelus, Inc.

 
 
 

Q: Padding pages and page locking

Post by Gary Mey » Fri, 17 Mar 1995 10:23:00




: : Do you have any indexes defined on this table?
: Yep, sounds like an index page locking problem ...

If you really want to know if it is a index page locking problem or a
data page locking problem.  Take the page number as seen in syslocks
and immediately feed it into dbcc page.  This will tell about everything
you need to know about the page for concurrency analysis such as the
index id and level of the page in the index.  Additionally it can be
quite useful to compare the page number against sysindexes.last.  If
the pages are the same you, are blocking because of insertion of keys
in a monotonic series.

-- Gary Meyer

--

 
 
 

Q: Padding pages and page locking

Post by td.. » Sat, 18 Mar 1995 17:52:00



>Newsgroups: comp.databases.sybase
>Path:
>maple.nis.net!news.sprintlink.net!howland.reston.ans.net!ix.netcom.com!netcom.co
>m!meyer

>Subject: Re: Q: Padding pages and page locking

>Organization: NETCOM On-line Communication Services (408 261-4700 guest)
>X-Newsreader: TIN [version 1.2 PL1]

>Date: Thu, 16 Mar 1995 01:23:00 GMT
>Lines: 18




>: : Do you have any indexes defined on this table?
>: Yep, sounds like an index page locking problem ...
>If you really want to know if it is a index page locking problem or a
>data page locking problem.  Take the page number as seen in syslocks
>and immediately feed it into dbcc page.  This will tell about everything
>you need to know about the page for concurrency analysis such as the
>index id and level of the page in the index.  Additionally it can be
>quite useful to compare the page number against sysindexes.last.  If
>the pages are the same you, are blocking because of insertion of keys
>in a monotonic series.
>-- Gary Meyer
>--

For your information.

FOR IMMEDIATE RELEASE!

     Sql Inspector & DeadLock Predictor available for SQL Server

Upper Saddle River, NJ - February 14, 1995.  

The Development Group for Advanced Technology, Inc. is pleased to announce that
the Sql Inspector & DeadLock Predictor,  a Sql Power Tools product is now
available for SQL Server.

Sql Inspector & DeadLock Predictor:
---------------------------------------------------------

The Sql Inspector & DeadLock Predictor provides improved system availability
to client server applications.  The product predicts the occurrence of
positional based deadlocks in dynamic SQL, stored procedures and triggers.  
The data base transactions and SQL statements involved in a predicted
deadlock scenario are presented in a Sql Deadlock Browser with the associated
SQL statements.   Dependency Graphs of the transactions are displayed in
Graphical Browsers.  

Deadlocks in SQL code may be easily identified during application development
as the stored procedures and triggers, are developed or prior to
application deployment!  Deadlock prediction may be performed on a
batch or interactive basis.  

The entire data base or any subset of dynamic SQL, stored procedures and
triggers may be analyzed in minutes.  Simply select all data base objects or any
subset you wish to analyze from the GUI.  You do not have to waste time manually
selecting SQL text or SQL statements, etc. in order to perform the deadlock analysis.

The Sql Inspector & DeadLock Predictor will pinpoint performance problems
before they occur.  

The Development Group for Advanced Technology may be reached at 201.825.9511,  

 
 
 

1. Page lock to simulate row level lock

 You can't - unless you mean setting IRL for a table - but this only works on
the last page for inserts.

 > Hey, does any one remember how to set SQL 6.5 page level lock to achieve a

 Neil Pike MVP/MCSE.  Protech Computing Ltd
 (Please reply only to newsgroups)
 SQL FAQ (428 entries) see
 forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps (sqlfaq.zip - L7
- SQL Public)
 or www.ntfaq.com/sql.html
 or www.sql-server.co.uk
 or www.mssqlserver.com/faq

2. *.mb File

3. page lock or table lock

4. SQLNET 1 with a 32bits app. on Win 95

5. ORACLE record locking acting like page locking

6. Can a DTS package do this?

7. Row locking versus page locking

8. MSG: 8120

9. SYBASE page locks outperform ORACLE row locks

10. Row-locking instead of page-locking ?

11. Paradox 5.0: Row level locking or page locking?

12. Statistics Page or Distribution Page for MS SQLSERVER