Wanted to see if anyone had any experience using transactions without table locking. I'm using basic transaction processing below and the issue I am having is that all of the tables that I am inserting records into get locked for the entire transaction and all other SLX Clients lock up when they query those same tables. Is there a way to force record/row locking instead of table locking? Thanks in advance for any assistance!

SalesLogix LAN v7.5.2 with SQL 2008

dim objConn
Set objConn = Application.GetNewConnection
objConn.CursorLocation = adUseClient
objConn.BeginTrans

<... do a bunch of recordset select/updates ...>

objConn.CommitTrans

Don't put a trans on the Connection, put it on the Record Set that you are trying to update\insert. EACH transaction is ONE row of

<... do a bunch of recordset select/updates ...>

Also we do read only non SLX OLE DB Provider native connection reads of the database WITH NOLOCK hints.....then turn around and store the inserts and updates using ADO record sets with the SLX OLE DB Provider.

Don't EVER forget that SLX wraps its reads for Users in a transaction....row by row.....that's why a user's select statement for x,000 rows takes so looooooonnnnnnnggggggg.

RJ thanks for the feedback. I'm not sure I am following your suggestion. It may be that my code was non specific enough so I have been much more specific below. Basically, this would copy the fields from TABLE1 into TABLE2 and TABLE3 but the two recordsets that are writing data are opened against the transaction object. That way, if for whatever reason we fail on the second insert we can roll the whole transaction back. Obviously in my case this is on a much larger scale with thousands of records being inserted. If I put the transaction on each recordset, I would have to perform an objConn.RollBackTrans on EACH of those transactions rather than just rolling back the one large transaction. Maybe I am not understanding your suggestion? Thanks so much for the assistance.

dim objConn, strSQL, objRS, objRS2, objRS3, bOK

bOK = True

Set objConn = Application.GetNewConnection
objConn.CursorLocation = adUseClient
objConn.BeginTrans

set objRS = objSLXDB.GetNewRecordSet
strSQL = "Select FIELD1, FIELD2 from TABLE1"
objRS.Open strSQL, objSLXDB.Connection

set objRS2 = objSLXDB.GetNewRecordset
strSQL = "Select * from TABLE2 where 1=2"
objRS2.Open strSQL, objConn 

while not (objRS.BOF or objRS.EOF)
objRS2.AddNew
objRS2.Fields("FIELD1").Value
objRS2.Fields("FIELD2").Value
objRS2.Update

objRS.Movenext
wend

objRS.MoveFirst

set objRS3 = objSLXDB.GetNewRecordset
strSQL = "Select * from TABLE3 where 1=2"
objRS3.Open strSQL, objConn 

while not (objRS.BOF or objRS.EOF)
objRS3.AddNew
objRS3.Fields("FIELD1").Value
objRS3.Fields("FIELD2").Value
objRS3.Update

objRS.Movenext
wend

if LogixErrors then bOK = false

if bOK = True then
objConn.CommitTrans
else
objConn.RollbackTrans
end if 

You don't lock the CONNECTION, Begin Trans the objRS for each .AddNew and Update 'section of code then have your Commit Trans.

you use objConn.BeginTrans at the start of your millions of transactions.....then one objConn.CommitTrans at the end of your millions of AddNews and Updates......

You want to BeginTrans Commit Trans for EACH objRS.Update statement..... in between your While and Wend statements.....

Better yet, don't use transaction locking at all and let saleslogix OLE DB Provider and it's BatchOptimistic Executed SQL do its job.

Ok, but how do I roll back ALL of the AddNews and Updates if I have already committed them individually as transactions? I may find an exception at record 9,999 of 10,000 and need to rollback everything.

have one connection for the Reads (doesn't have to be through the provider, can be a faster native connection right?).....and one connection for the writes.....less tables locked up. You only want to lock up the being written to tables....

I'll assume this is only being done late at night\off hours.

I'll assume you have some valid business reason for committing and rolling back 10,000 row transactions in a CRM system....this isn't financial records, Sarbanes-Oxley, keys to the corporate treasury kinds of stuff right? If it's 10,000 leads there truly isn't a way to commit the 9,999 good leads and kick back the ONE transaction row that didn't have a valid email address?

You could do it on a parallel database and then take the entire committed transaction as a stored to hard drive recordset and slam it into a table......

Same with Business Intelligence cubes, etc. Why does this data have to be in the SLX database for example.....reports, grids, dashboards, etc. can always point at a different database.....and there's no meaningful way to synchronize this to remotes....

I'd do this in off hours, but then, that's me..... and I can probably do a better job of explaining what CRM is all about, or isn't.....

Quote: Originally posted by willis Yes there is a valid business reason and no this would not occur off-hours which is why the table locking is proving to be such an annoyance. I have also tried using a native ADODB connection and I am only using the transaction objects for writes. Any other ideas on how to accomplish this? Thanks.

Is this some sort of live validation?

Couldn't you load the Data into a Staging Table(s) first and do your validation while hitting those Staging tables?

If you get an error, roll back the Staging tables.

If no Errors, then push the Data into SLX (no further validation at this point, so no need to lock production tables).

Again, there may be other ways to do this, but without a good understanding of what you are trying to do it is hard to offer you specific ideas.

A lot depends on what tables are being locked, the volume of data you are pushing (and your average transaction duration).

Additional thinks to ponder:

What kind of Locking are you using on your Recordsets?

Are you just reading the Affected records or the whole table?

For example, if you are loading a Recordset as "SELECT * FROM TABLE", you may want to do something like "SELECT * FROM TABLE WHERE IDS IN XXXXX"  (Assuming that you could pinpoint the records affected).

This may make a difference between a full table lock to a Record or Page Lock.

Ok, so I figured out a way to do this. I appreciate all of the input. Figured I would put my solution here in case anyone else needs this type of functionality.

Basically, what I did was initiate all of my ADO recordset objects as global variables using rs.LockType = adLockBatchOptimistic in a new "GetBatchRecordset" public function, then I populated those objects as I iterated through all of my data with the hundreds of .AddNew recordsets. Using the adLockBatchOptimistic lock type means that the table lock only occurs when you execute the .UpdateBatch which I saved for the end of the process immediately prior to my .CommitTrans so the the locks are nearly instantanious! Thanks for all of the help!

dim objRS2, objRS3  'Global declaration

Public Function GetBatchRecordset()
         Set GetBatchRecordset = CreateObject("ADODB.Recordset")
         GetBatchRecordset.CursorLocation = adUseClient
         GetBatchRecordset.CursorType = adOpenStatic
         GetBatchRecordset.LockType = adLockBatchOptimistic
End Function

dim objConn, strSQL, objRS, bOK

bOK = True

Set objConn = Application.GetNewConnection
objConn.CursorLocation = adUseClient
objConn.BeginTrans

set objRS = objSLXDB.GetNewRecordSet
strSQL = "Select FIELD1, FIELD2 from TABLE1"
objRS.Open strSQL, objSLXDB.Connection

set objRS2 = objSLXDB.GetBatchRecordset
strSQL = "Select * from TABLE2 where 1=2"
objRS2.Open strSQL, objConn

while not (objRS.BOF or objRS.EOF)
     objRS2.AddNew
     objRS2.Fields("FIELD1").Value
     objRS2.Fields("FIELD2").Value
     objRS.Movenext
wend

objRS.MoveFirst

set objRS3 = objSLXDB.GetBatchRecordset
strSQL = "Select * from TABLE3 where 1=2"
objRS3.Open strSQL, objConn

while not (objRS.BOF or objRS.EOF)
     objRS3.AddNew
     objRS3.Fields("FIELD1").Value
     objRS3.Fields("FIELD2").Value
     objRS.Movenext
wend

if LogixErrors then bOK = false

if bOK = True then
     objRS2.UpdateBatch
     objRS3.UpdateBatch
     objConn.CommitTrans
else
     objConn.RollbackTrans
end if