>Hi all,
>
>I'm working on an app that using SQL as back-end database. I created several updatable views in FoxPro. For each transaction, my app updates several tables in SQL (updating views). Before I start updating, I try to lock all of the tables (file locking) that my app is going to update. I can do it very easy if the back-end database is VFP. I don't know how to do it if the back-end database is SQL. Please help.
>Thanks in advance.
>
>PhuTri

Hi PhuTri,


SQL Server can lock the following types of items:

Page
Extent
Table
Intent

Page lock is the most common type of lock. When you update or insert
new row, entire page will be locked. SQL Server automatically
generates page-level locks, when a query requests rows from a table.

Extent lock is only used for allocation. When it's used, entire extent
will be locked.

Table lock is used when a large percentage of the table's rows are
queried or updated. You can set this percentage with sp_configure
system stored procedure to the appropriate value. This is described
below in the Lock Escalation item.

Intent lock is a special type of table lock to indicate the type of
page locks currently placed on the table.

There is also insert row locking. The insert row locking can be set with
sp_tableoption system stored procedure and will be described below.

Lock Escalation
You can customize locking by setting Lock Escalation level. The Lock
Escalation level determines, when SQL Server applies table locks instead
of page locks, and it affects all users of SQL Server. So it's escalation
from the page's to the table's level locking.

There are three Lock Escalation options:

LE threshold maximum
LE threshold minimum
LE threshold percent

LE threshold maximum is the maximum number of page locks to hold
before escalating to a table lock. The default value is 200.

LE threshold minimum is the minimum number of page locks required
before escalating to a table lock. The default value is 20.

LE threshold percent is the percentage of page locks needed on a
table before escalating to a table lock. The default value is 0, it means
that a table lock will be occur only when the LE threshold maximum will
be exceeded.

You can configure Lock Escalation levels by using the sp_configure
system stored procedure.
This is the example to set LE threshold maximum to 250:

EXEC sp_configure 'LE threshold maximum'
GO
EXEC sp_configure 'LE threshold maximum', 250
GO
RECONFIGURE WITH OVERRIDE
GO
EXEC sp_configure 'LE threshold maximum'
GO


This is the results:

name                                minimum     maximum     config_value 
run_value
----------------------------------- ----------- ----------- ------------ 
-----------
LE threshold maximum                2           500000      200          200
Configuration option changed. Run the RECONFIGURE command to install.
name                                minimum     maximum     config_value 
run_value
----------------------------------- ----------- ----------- ------------ 
-----------
LE threshold maximum                2           500000      250          250
 

Setting insert row lock
You can set insert row lock option for the user-defined table/tables
with sp_tableoption system stored procedure.

This is the syntax from SQL Server Books Online:

sp_tableoption @TableNamePattern  [, '@OptionName'] [, '@OptionValue']

where

@TableNamePattern is a user-defined database table.
@OptionName is a option name.
There are insert row lock and pintable options.
@OptionValue is a option value. @OptionValue can be 'false' or 'true'.

The default value for insert row lock option is 'false'.

This is the example to set insert row lock option for authors table:

EXEC sp_tableoption 'authors', 'insert row lock', 'true'

There are several SQL Server manuals on line. I like to use
http://www.google.com to search for stuff.
JLK