How to Implement Pessimistic Locking on SQL Server

• Microsoft FoxPro for Windows, versions 2.5, 2.5a, 2.5b, 2.6
• Microsoft FoxPro Connectivity Kit, versions 2.5, 2.6

SUMMARY

This article describes how to use the FoxPro Connectivity Kit to prevent other users from updating a table on which you have executed an SQL SELECT statement before you UPDATE the table with any needed changes. This locking strategy is known as "pessimistic locking."

MORE INFORMATION

In Transact-SQL in SQL Server for Windows NT, the SELECT statement can use the HOLDLOCK keyword to hold a shared lock that it has set until a transaction has been completed instead of releasing the lock as soon as the required table is no longer needed.

You can accomplish this with the Connectivity Kit by using a routine similar to the one shown below.

   * CKPLOCK.PRG
   * Pessimistic Locking on SQL Server with the CK
   * Assumes pass of a valid connection handle or connection handle = 1
   * Uses 'pubs' database

   PARAMETERS dbHdle

   IF TYPE('dbHdle') = "L"
      STORE 1 TO dbHdle
   ENDIF

   STORE 0 TO lnResult

   *---- Use pubs database
   lnResult = DBExec(dbHdle, "use pubs")

   *---- Set Transaction Mode to manual (required for this to work)
   lnResult = DBSetOpt(dbHdle, "Transact", 2)

   *---- SELECT a rowset to update and lock the table until transaction
   *---- is completed. Note that SELECT without HOLDLOCK will not normally
   *---- cause the table to be locked.
   lnResult = DBExec(;
     dbHdle, "SELECT * FROM sales HOLDLOCK WHERE sales.stor_id = '7131' ")

   *---- FoxPro program would normally perform any necessary data changes
   *---- locally at this point.
   WAIT WINDOW "Table is now locked on server ... " TIMEOUT 5

   *---- Write changed data back
   lnResult = DBExec(;
     dbHdle,"UPDATE sales SET sales.qty =25 WHERE sales.stor_id='7131' ;
      AND sales.ord_num = 'P3087a' ")

   *---- Commit results
   lnResult = DBTransact(dbHdle, "Commit")

   *---- Set Transaction Mode back to Automatic
   lnResult = DBSetOpt(dbHdle, "Transact", 1)

   WAIT WINDOW ;
     "Table is now available for updating by another user on server ... " ;
     TIMEOUT 2

REFERENCES

"Transact-SQL Reference" for Microsoft SQL Server for Windows NT, pages 403- 405