Sqlcommit locking table 1

[robsuttonjr]

When I use sqlexec to insert into a MS Sql server table use manual transactions, my table is locked until I use sqlcommit. I can't have the table being locked like this, any thoughts how to fix this? I am using the sample code found in FoxPro 7 help for SQLCOMMIT().

= SQLSETPROP(gnConnHandle, 'Transactions', 2) && Manual transactions
= SQLEXEC(gnConnHandle, "INSERT INTO authors (au_id, au_lname);
VALUES ('aupoe', 'Poe')&quot && Modify the authors table
= SQLCOMMIT(gnConnHandle) && Commit the changes

 

[normanlaw]

Rob,

I have no experience in transaction as MySQL (what I am using) does not support. But as a generic concept of SQL, the SQL server will LOCK a table automatically when a query is executing. This is needed to prevent the intervention between clients. A transaction virtually packs all the queries into one single query. It is natural that SQL will LOCK the table and wait until you COMMIT or ROLLBACK. Anyway to bypass? I don't know. Once you start a transaction, common sense is to LOCK the tables so that everybody will not get a wrong data before the transaction is finished.

Best regards,
Norman

 

[robsuttonjr]

Thanks, that truly makes sense. I found that because I was using the manual transaction that the client was controlling the interaction with the table and consequently the table was being locked until I issued a commit statement. (Just as you indicated.) Totally makes sense. Thanks