a. Considering the default model of working with database inserts - loop over a recordList and then call execSQL within :

    try (exit on failure) 
--  initialize 
--  other steps and invocations 
loop over recordList 
execSQL(s) 
catch 
error handling 

Does that mean execSQL is going to
Connect,
Insert record
Commit (AutoCommit in case of SQLServer)
Close
for each loop pass through?

If the above is true, then a better approach would be to use single database connection and loop through the recordList

    try (exit on failure) 
connect 
start transaction 
inner try (exit on failure) 
loop over recordList 
execSQL(s) 
inner catch 
error handling 
rollback  
clear transaction 
close 
catch  
error handling 

Wanted to know if the second approach is better than the first for performance and better eror handling?

b. Also how does one code this in flow

if transaction is not cleared 
cleartransaction 

if connection is open 
close

Lets talk about the Case1, as i am using that approach with some modification.

It looks like if you put a startTransaction, the tranactions are not commited (connection is not returned) till you invoke a commit service. And this works fine for me.

So, it looks like this:

try (exit on failure)
initialize
other steps and invocations
clearTransaction
startTransaction
loop over recordList
execSQL(s)
commit (outside the loop)
clearTransaction

catch (exit on DONE)
getLastError
rollback
sendMail/ other errorHandling stuff

Hope this helps!