DelphiFAQ Home Search:

Row-Level Locking in MSSQL 7

 

comments2 comments. Current rating: 5 stars (2 votes). Leave comments and/ or rate it.

Question:

I have a hard time trying to make my application acquire one of those neat row-level locks that were introduced in MSSQL 7. How do I do this?

Answer:

Row-level locking is definitely a powerful feature that was recently added to MSSQL server. Unfortunately, the current version of the BDE (5) does not support this functionality. The only way to get this out of your SQL Server is to bypass the BDE entirely with, for example, an ADO connection. In that situation, the lock is acquired by this simple SQL statement:

SELECT * FROM tablename WITH (UPDLOCK ROWLOCK) WHERE condition

tablename is the name of your table and condition is a conditional statement that should force only one record to be returned. The WITH clause will cause all rows returned to be locked with a row level lock, but if you need multiple rows locked you might want to look at another type of lock. This statement must be run in the context of a transaction, and this lock will persist until that transaction is committed or rolled back.

If you then run the MSSQL stored procedure sp_lock() during the timeframe of the transaction, you will see that your table has a lock of type RID, which denotes a row level lock. If you were to fire this exact SQL statement in a BDE application, sp_lock() would show a lock of either type TAB (table level) or PAG (page level).

Content-type: text/html

Comments:

2006-04-19, 00:34:50
premanjatingaleh@yahoo.com from Indonesia  
rating
how to connect database from ms access with ADO delphi?
2006-04-19, 00:41:48
R14N_Gun@yahoo.com from Indonesia  
rating
how to make a graphic with delphi use TChart component that is connected with database?

 

 

NEW: Optional: Register   Login
Email address (not necessary):

Rate as
Hide my email when showing my comment.
Please notify me once a day about new comments on this topic.
Please provide a valid email address if you select this option, or post under a registered account.
 

Show city and country
Show country only
Hide my location
You can mark text as 'quoted' by putting [quote] .. [/quote] around it.
Please type in the code:

Please do not post inappropriate pictures. Inappropriate pictures include pictures of minors and nudity.
The owner of this web site reserves the right to delete such material.

photo Add a picture: