Locking done by UpdateBatch method?

[mtl777]

Quick question:

For an ADODB record set, when you use the UpdateBatch method, does it attempt to lock the entire table or only the records in the record set? (Imagine you're doing a loop on the record set, updating records one by one, and when the loop ends you call the UpdateBatch method.)

Thanks!

[dilettante]

I think it uses something like exclusive locking.

After the call, any failed updates can be found by inspecting the Recordset for the failed updates. You do that by setting .Filter = adFilterConflictingRecords and walking the Recordset for the failed records.

As it says in the Fine Manual:

If the attempt to transmit changes fails for any or all records because of a conflict with the underlying data (for example, a record has already been deleted by another user), the provider returns warnings to the Errors collection and a run-time error occurs. Use the Filter property (adFilterAffectedRecords) and the Status property to locate records with conflicts.

[wqweto]

Which RDBMS do you issue this UpdateBatch against?

Using MSSQL OLEDB provider there is no explicit locking specified in the generated T-SQL so locking depends only on current isolation level that the current outstanding transaction is at.

This includes whether the DB is configured to use "snapshot isolation on (default) read commited" level. This setting turns read-commited isolation to snapshot isolation. Snapshot isolation prevents locks from happening at all i.e. readers keep on reading on last version of the data (snapshot) while the writers are updating outstanding uncommited transactions isolated in separate snapshots which get merged on COMMIT.

Btw, this complete isolation can be problematic when two writers need to block each other so that their access to a table is sequential.

Btw, snapshot isolation means that ADODB.Connection's Commit method might fail already on snapshot merge which has never happened before snapshot isolations were introduced.

cheers,
</wqw>