Default Table Lock-mode for DB-LIB, OLE DB Connections 
Author Message
 Default Table Lock-mode for DB-LIB, OLE DB Connections
Hi,

Does anyone know the default lock-mode for DB-Lib and OLE DB connections?
Table-level?  Row-level?  This would be in the isolation level, READ
COMMITTED.

TIA



Sun, 09 Jan 2005 09:38:21 GMT
 Default Table Lock-mode for DB-LIB, OLE DB Connections

Dear David,

The lock mode is not related directly to the DB library or OLE DB
connection. The SQL Server itself use different lock modes (shared locks,
exclusive locks, update locks ?-) automatically based on the different
query.
SQL Server locks are applied at various levels of granularity in the
database. Locks can be acquired on rows, pages, keys, ranges of keys,
indexes, tables, or databases. SQL Server dynamically determines the
appropriate level at which to place locks for each Transact-SQL statement.
The level at which locks are acquired can vary for different objects
referenced by the same query.

The level at which locks are applied does not have to be specified by users
and needs no configuration by administrators.

Please refer to the SQL Server online books about the topic 'locking
architecture'.

Sincerely,

Eric Yang [MS]
Microsoft Online Support Engineer

Please do not send email directly to this alias.  This is our online
account name for newsgroup participation only.
This posting is provided 'AS IS' with no warranties, and confers no rights.
Get Secure! - www.microsoft.com/security



Sun, 09 Jan 2005 11:54:54 GMT
 Default Table Lock-mode for DB-LIB, OLE DB Connections
Thanks, Eric.  Would you be able to answer the following?  It's a question
about OLE DB peroformance.  I have an application that can switch between
DB-Library and OLE DB (via the SQLOLEDB Provider).  In the DB-Lib version, a
batch of 100 UPDATE statements execute in about 5 seconds.  If I switch to
OLE DB, the statement runs in about 40 seconds.  The statements look like
the following (they're very simplified to reproduce the behavior):

UPDATE TG_UPDT SET Julian_Day = 2452474 WHERE Julian_Day = 2452474
UPDATE TG_UPDT SET Julian_Day = 2452475 WHERE Julian_Day = 2452475
UPDATE TG_UPDT SET Julian_Day = 2452476 WHERE Julian_Day = 2452476
.
.
.

Using Query Analyzer that ships with SQL Server 2000 (8.00.532), I run the
statement above and it executes in about 38 seconds.  I believe it uses OLE
DB via ODBC?  I have a simple DB-Library program (based on BOL help) and it
executes the statement in about 8 seconds.  I monitored the execution in
Profiler and one difference between the DB-Lib and OLE DB executions is that
in DB-Lib, the exeuction tree for every statement (the statements get parsed
into individual statements in SQL Server) is:

Execution Tree
--------------
Table Update(OBJECT:([repo].[dbo].[TG_UPDT]),

  |--Top(ROWCOUNT est 0)
       |--Table Scan(OBJECT:([repo].[dbo].[TG_UPDT]),


Using OLE DB, the execution tree is:

Execution Tree
--------------
Table Update(OBJECT:([repo].[dbo].[TG_UPDT]),
SET:([TG_UPDT].[Julian_Day]=2452474))
  |--Top(ROWCOUNT est 0)
       |--Table Scan(OBJECT:([repo].[dbo].[TG_UPDT]),
WHERE:([TG_UPDT].[Julian_Day]=2452474) ORDERED)

The real value for Julian_Day is there:  2452474.

Do you have any idea why DB-Lib is executing so much faster than OLE DB?

TIA,
Dave

Quote:

>Dear David,

>The lock mode is not related directly to the DB library or OLE DB
>connection. The SQL Server itself use different lock modes (shared locks,
>exclusive locks, update locks ?-) automatically based on the different
>query.
>SQL Server locks are applied at various levels of granularity in the
>database. Locks can be acquired on rows, pages, keys, ranges of keys,
>indexes, tables, or databases. SQL Server dynamically determines the
>appropriate level at which to place locks for each Transact-SQL statement.
>The level at which locks are acquired can vary for different objects
>referenced by the same query.

>The level at which locks are applied does not have to be specified by users
>and needs no configuration by administrators.

>Please refer to the SQL Server online books about the topic 'locking
>architecture'.

>Sincerely,

>Eric Yang [MS]
>Microsoft Online Support Engineer

>Please do not send email directly to this alias.  This is our online
>account name for newsgroup participation only.
>This posting is provided 'AS IS' with no warranties, and confers no rights.
>Get Secure! - www.microsoft.com/security



Sun, 09 Jan 2005 12:46:52 GMT
 Default Table Lock-mode for DB-LIB, OLE DB Connections
Dear David,

Thanks for you reply.
First, if you use the query analyzer to connect the SQL Server, it uses the
ODBC.
Secondly, the SQL Server only deals with the submitted statement no matter
the DB library or the ODBC.  In your last post, you said that you run the
statements in the query analyzer for 38 seconds, where did you see 38
seconds? In the SQL profiler? If you just see it in the query analyzer
right down pane. The 38 seconds include the time the network package
transformed and other time, not only the statement execution time.

In addition, if you want to test the two provide performance, when you
finished the ODBC, you'd better run the 'DBCC FREEPROCCACHE' to remove all
elements from the procedure cache.
Because you test the ODBC first, then you should free the cache, and then
test the DB library. I think it is fair. :)

Sincerely,

Eric Yang [MS]
Microsoft Online Support Engineer

Please do not send email directly to this alias.  This is our online
account name for newsgroup participation only.
This posting is provided 'AS IS' with no warranties, and confers no rights.
Get Secure! - www.microsoft.com/security



Mon, 10 Jan 2005 21:15:34 GMT
 
 [ 4 post ] 

 Relevant Pages 

1. Default Table Lock-mode for DB-LIB, OLE DB Connections

2. DB-Lib versus OLE DB Performance Question

3. Locking db-lib vs: ct-lib

4. testing db-lib and ct-lib connections

5. OLE DB Close DB Connection w/MSSQL 6.5

6. DSN Default dB being overriden by Login's default dB

7. Difference between OLE DB 8.0 and OLE DB

8. sybase db-lib browse mode programming

9. Losing a db connection while table is locked

10. How to check lock in DB-LIB

11. Detect Single-User mode in OLE/DB ?

12. using DB-Lib and trusted connection


 
Powered by phpBB® Forum Software