Re: freebcp transaction isolation level when 'bcp out'

2015-04-28 10:14 GMT+01:00 Ed Avis <[email protected]>:
> 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?
>

Looks like is easy, useful and back compatible so I don't see any
reason to not accept any such patch. I would also test snapshot
isolation level (I don't know how does it works).

Frediano