Locking Records - Need Help

[123jon]

I am using VB6's SELECT..LOCK IN SHARE MODE command. However, the entire table is being locked when i try to select and lock just 1 record.

I "solved" this problem by adding a primary key to 1 of the tables. Now the other table has a primary key and index but I am still having problems. The entire table is being lock.

What am I doing wrong?

MySQL version is 4.0.9-gamma-max-debug

-- sample of code ---
Dim rstCount As ADODB.Recordset
Set rstCount = New ADODB.Recordset

sSQL = "Select * From ItCustmr Where Trim(cCuCde)='" & dcCustomer.BoundText & "' LOCK IN SHARE MODE"

With rstCount
.Source = sSQL
.ActiveConnection = adoMyDatabase
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open
End With
----------------

cCuCde is a primary key in ItCustmr
cCuName is an index in ItCustmr

 

[QatQat]

What kind of tables are you using?

MyISAM is not good for your purpose.

Have you tried to convert tables to InnoDB?

Bye

Qatqat

The reason why my girlfriend can read my thoughts is because mine are properly written! (G.Lepore)

 

[123jon]

Thnx Qatqat. I am using InnoDB tables. But the strange thing is that the lock now works correctly once i remove the TRIM command from my SELECT statement.

 

[QatQat]

Very strange, I can't relate the two things.
If it works...


Bye


Qatqat


The reason why my girlfriend can read my thoughts is because mine are properly written! (G.Lepore)