I am encountering an interesting phenomenon when I update a row in a linked (ODBC MSDASQL/VFP) server table. First I open up the test table in VFP and lock a record by editing.
Then I run the following script:
WHILE (@@ROWCOUNT = 0) -- I think this is wrong/needs scope
BEGIN
UPDATE OPENQUERY(
SBTSODATA,
'SELECT * FROM slsgrid')
SET type = 'D'
WHERE type = 'X'
END
GO
Query Analyzer spins its little globe icon for about 3 secs and then returns an ODBC error (VFP specific) that says the record is not locked.
I am interpreting this to mean that the process was unable to get a lock on the record because I have already set an explicit lock on it in VFP.
Now if I remove my lock and run the script again, not only does it work, but when I lock the record again and run the script again it will spin that globe interminably (I assume until the querytimeout setting is exceeded) until I release my lock at which time it performs as desired.
The only way I can get it to fail again is if I unlock and re-lock the record again in VFP.
What I am trying to figure out by experimentation is 'What negative affects could be caused by inserting and updating data in my accounting package (SBT)?'
Additionally, VFP provides a function, RLOCK(), that attempts to lock a record and returns true if successful and false if unsuccessful. When executed in VFP, the lock created this way is persistent until UNLOCK (which is a command and not a function ) is issued or until the workspace is closed.
I have tested the following:
DECLARE @IsLocked bit
SET @IsLocked = 1
WHILE @IsLocked = 1
BEGIN
SELECT @IsLocked=IsLocked
FROM
OPENQUERY(
SBTSODATA,
'SELECT type, NOT RLOCK("slsgrid"
AS IsLocked FROM slsgrid')
WHERE type = 'X'
END
UPDATE OPENQUERY(
SBTSODATA,
'SELECT * FROM slsgrid')
SET type = 'D'
WHERE type = 'X'
GO
This appears to work. But it's interesting to note that the lock made by the RLOCK() in the script is not persistent (I imagine because the ODBC cursor is closed at completion) and so begs the question 'Can some other process lock the row in that blink of an eye between when I verify the record is not locked and when I fire my update (or insert)?'. Alternatively is there some fundamental concept in ODBC record locks i.e. they are nearly instantaneous and pose zero threat of locking conflict, that I need to discover?
Then I run the following script:
WHILE (@@ROWCOUNT = 0) -- I think this is wrong/needs scope
BEGIN
UPDATE OPENQUERY(
SBTSODATA,
'SELECT * FROM slsgrid')
SET type = 'D'
WHERE type = 'X'
END
GO
Query Analyzer spins its little globe icon for about 3 secs and then returns an ODBC error (VFP specific) that says the record is not locked.
I am interpreting this to mean that the process was unable to get a lock on the record because I have already set an explicit lock on it in VFP.
Now if I remove my lock and run the script again, not only does it work, but when I lock the record again and run the script again it will spin that globe interminably (I assume until the querytimeout setting is exceeded) until I release my lock at which time it performs as desired.
The only way I can get it to fail again is if I unlock and re-lock the record again in VFP.
What I am trying to figure out by experimentation is 'What negative affects could be caused by inserting and updating data in my accounting package (SBT)?'
Additionally, VFP provides a function, RLOCK(), that attempts to lock a record and returns true if successful and false if unsuccessful. When executed in VFP, the lock created this way is persistent until UNLOCK (which is a command and not a function ) is issued or until the workspace is closed.
I have tested the following:
DECLARE @IsLocked bit
SET @IsLocked = 1
WHILE @IsLocked = 1
BEGIN
SELECT @IsLocked=IsLocked
FROM
OPENQUERY(
SBTSODATA,
'SELECT type, NOT RLOCK("slsgrid"
AS IsLocked FROM slsgrid')WHERE type = 'X'
END
UPDATE OPENQUERY(
SBTSODATA,
'SELECT * FROM slsgrid')
SET type = 'D'
WHERE type = 'X'
GO
This appears to work. But it's interesting to note that the lock made by the RLOCK() in the script is not persistent (I imagine because the ODBC cursor is closed at completion) and so begs the question 'Can some other process lock the row in that blink of an eye between when I verify the record is not locked and when I fire my update (or insert)?'. Alternatively is there some fundamental concept in ODBC record locks i.e. they are nearly instantaneous and pose zero threat of locking conflict, that I need to discover?