[freetds] freebcp transaction isolation level when 'bcp out'

Ed Avis eda at waniasset.com
Tue Apr 28 05:14:01 EDT 2015

I run freebcp to make scheduled hourly backups of some tables.  These are not
a replacement for the normal database backup, but can be handy as a way to
quickly recover from minor screwups or as a way to grep the history of a table
over time.

The tables being dumped are usually small and append-only - so new rows are
added to them from time to time but rows are not updated and rarely deleted.

Sometimes I find that the freebcp process gets blocked by some other process
adding rows to one of these tables.  I would be quite happy to do

    set transaction isolation level read uncommitted

That would stop freebcp being blocked (or blocking anything else).  It means
that you can see rows which have been added by some other process and not yet
committed, but for this particular application this is not an issue.  I am
happy to make that tradeoff in order to make the hourly dumps not get in the
way of anything else.

So I would like a way to tell freebcp what transaction isolation level to use.
I see in bcp.c that it simply works by

    tds_submit_queryf(tds, "select * from %s", dbproc->bcpinfo->tablename)

in other words there is no special magic for 'bcp out' - really it is just
a convenience to dump the rows in the same format that can later be 'bcp in'.

Could freebcp have an option to run some extra SQL before the 'select'?

    freebcp database..table out outfile -c \
      -q 'set transaction isolation level read uncommitted'

I think I could hack this up; would the patch be accepted?

Ed Avis <eda at waniasset.com>

More information about the FreeTDS mailing list