bcp problem in ASE 15.03 latest EBF on SUn SOLARIS

Discussion:

(too old to reply)

unknown

2009-12-18 17:02:29 UTC

We have the following situation:
while delete insert(DML) is going on on our tables, we bcp
out data from them. Unfortuantely, the bcp reads the dirty
pages and both inserted and deleted records are read.

The isolation level on the server is default(1).
select @@isolation also return 1.
Why such a odd behavior in ASE 15? has anyone else had a
similar situation?
Is there a way to set the isolation level in bcp.

By the way, we did not have a similar situation when the
data server was version 12.5; it happens after the upgrade
to ASE 15.
Your help is appreciated.

tartampion

Rob V [ Sybase ]

2009-12-18 17:09:05 UTC

Permalink

This sounds very odd. BCP runs at isolation level 1 so this should not be
possible (unless you'd use the --initstring argument and set the isolation
level to 0)
Are you 100% sure that the rows extracted by BCP are coming from uncommited
transaction?

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

mailto:***@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., Amersfoort, The Netherlands
Chamber of Commerce 27138666
-----------------------------------------------------------------

Post by unknown
while delete insert(DML) is going on on our tables, we bcp
out data from them. Unfortuantely, the bcp reads the dirty
pages and both inserted and deleted records are read.
The isolation level on the server is default(1).
Why such a odd behavior in ASE 15? has anyone else had a
similar situation?
Is there a way to set the isolation level in bcp.
By the way, we did not have a similar situation when the
data server was version 12.5; it happens after the upgrade
to ASE 15.
Your help is appreciated.
tartampion

unknown

2009-12-18 17:24:02 UTC

Permalink

Hi Rob,
I am 100% sure that the isolation level is 1 in the DML
statement, to be on the safe side I have added the caption
of isolation level to each step.
--------------------------------------------------------------------------
-----------
The isolation level
is..................................................:
1
delete statement here
(1 row affected)

--------------------------------------------------------------------------
-----------
The isolation level
is..................................................:
1
insert statement for the same record here

after insert:
--------------------------------------------------------------------------
-----------
The isolation level
is..................................................:
1

Also I am 100% sure that the rows extracted by the bcp comes
from dirty read as I have duplicates in bcp results and no
duplicates in the tables.
I love ASE 15 but it, but hate its odd behavior.

Tartampion.

Post by Rob V [ Sybase ]
This sounds very odd. BCP runs at isolation level 1 so
this should not be possible (unless you'd use the
--initstring argument and set the isolation level to 0)
Are you 100% sure that the rows extracted by BCP are
coming from uncommited transaction?
HTH,
Rob V.
----------------------------------------------------------
------- Rob Verschoor
Certified Sybase Professional DBA for ASE
15.0/12.5/12.0/11.5/11.0 and Replication Server
15.0.1/12.5 // TeamSybase
Author of Sybase books (order online at
www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
ASE" (ASE 15 edition) "The Complete Sybase ASE Quick
Reference Guide" "The Complete Sybase Replication Server
Quick Reference Guide"
http://www.sypron.nl
Sypron B.V., Amersfoort, The Netherlands
Chamber of Commerce 27138666
----------------------------------------------------------
-------

following situation: while delete insert(DML) is going
on on our tables, we bcp out data from them.
Unfortuantely, the bcp reads the dirty pages and both

inserted and deleted records are read. >

The isolation level on the server is default(1).
Why such a odd behavior in ASE 15? has anyone else had a
similar situation?
Is there a way to set the isolation level in bcp.
By the way, we did not have a similar situation when the
data server was version 12.5; it happens after the
upgrade to ASE 15.
Your help is appreciated.
tartampion

Rob V [ Sybase ]

2009-12-18 18:14:50 UTC

Permalink

It sure sounds odd.... I'm not aware of BCP behaving oddly in ASE 15. The
only thing I'd recoemmend here is to contact Sybase TechSupport and work on
a reproduction of your problem -- unless the problem is already known to
them.

HTH,

Rob V.
-----------------------------------------------------------------
Rob Verschoor

Certified Sybase Professional DBA for ASE 15.0/12.5/12.0/11.5/11.0
and Replication Server 15.0.1/12.5 // TeamSybase

Author of Sybase books (order online at www.sypron.nl/shop):
"Tips, Tricks & Recipes for Sybase ASE" (ASE 15 edition)
"The Complete Sybase ASE Quick Reference Guide"
"The Complete Sybase Replication Server Quick Reference Guide"

mailto:***@YOUR.SPAM.sypron.nl.NOT.FOR.ME
http://www.sypron.nl
Sypron B.V., Amersfoort, The Netherlands
Chamber of Commerce 27138666
-----------------------------------------------------------------

Post by unknown
Hi Rob,
I am 100% sure that the isolation level is 1 in the DML
statement, to be on the safe side I have added the caption
of isolation level to each step.
--------------------------------------------------------------------------
-----------
The isolation level
1
delete statement here
(1 row affected)
--------------------------------------------------------------------------
-----------
The isolation level
1
insert statement for the same record here
--------------------------------------------------------------------------
-----------
The isolation level
1
Also I am 100% sure that the rows extracted by the bcp comes
from dirty read as I have duplicates in bcp results and no
duplicates in the tables.
I love ASE 15 but it, but hate its odd behavior.
Tartampion.

following situation: while delete insert(DML) is going
on on our tables, we bcp out data from them.
Unfortuantely, the bcp reads the dirty pages and both

inserted and deleted records are read. >

unknown

2009-12-18 17:39:41 UTC

Permalink

Rob,
My bcp is a normal bcp I do not use --initstring in it.
It worth mentioning that my bcp is version:
D:\WSC\bcpdata>bcp -v
Sybase CTBCP Utility/12.5.1/P-EBF13692 ESD #15/PC
Intel/BUILD1251-047/OPT/Thu Aug 17 19:25:05 2006

Tartampion

following situation: while delete insert(DML) is going
on on our tables, we bcp out data from them.
Unfortuantely, the bcp reads the dirty pages and both

inserted and deleted records are read. >

unknown

2009-12-18 21:52:03 UTC

Permalink

it seems to be a simple test to reproduce!

So you tested by having 1 session do
begin tran
delete all rows
..keep the transaction open

issue bcp OUT
(in this scenerio, bcp i not blocked?)
does it bcp out rows?
<<

In above scenerio bcp would be blocked. Now commit the first
session(there will be 0 rows as all rows are deleted and
commited), the block on bcp will be released, and it should
bcp out 0 rows. What do u see in this scenerio?

Luc Van der Veurst

2009-12-22 11:36:03 UTC

Permalink

If you want to have a consistent bcp output, you'll have to lock the
table during the bcp process with an initstring.

If you do a delete of a record that is already processed by the bcp
process and then you insert a record with the same primary key,
and the insert happens on a page that hasn't been read by the
bcp process, you'll have the same primary key twice in your output file.

Is that what you are experiencing ?

That also happens in 12.5. It all depends on where the insert takes
place. It was easy to reproduce this in 12.5. A table with 6 records,
deleting one record and inserting one with the same key in one
transaction, so the count is 6 at all times, but the output file contains
7 records.

Luc.

Eugene

2009-12-23 17:05:40 UTC

Permalink

Luc,

Post by Luc Van der Veurst
If you want to have a consistent bcp output, you'll have to lock the

Post by Luc Van der Veurst
table during the bcp process with an initstring.

I do not see any parameters in bcp which allow to do it. Or
it suppose to be isql command like 'set transaction isolation level 3'
(I think it should be by default (point in time consistency, but
unfortunately still no snapshot isolation in ASE, so other users would
be suffering during table(s) locking).

Regards,
Eugene

Post by Luc Van der Veurst
If you want to have a consistent bcp output, you'll have to lock the
table during the bcp process with an initstring.
If you do a delete of a record that is already processed by the bcp
process and then you insert a record with the same primary key,
and the insert happens on a page that hasn't been read by the
bcp process, you'll have the same primary key twice in your output file.
Is that what you are experiencing ?
That also happens in 12.5. It all depends on where the insert takes
place. It was easy to reproduce this in 12.5. A table with 6 records,
deleting one record and inserting one with the same key in one
transaction, so the count is 6 at all times, but the output file contains
7 records.
Luc.

Post by Luc Van der Veurst
while delete insert(DML) is going on on our tables, we bcp
out data from them. Unfortuantely, the bcp reads the dirty
pages and both inserted and deleted records are read.
The isolation level on the server is default(1).
Why such a odd behavior in ASE 15? has anyone else had a
similar situation?
Is there a way to set the isolation level in bcp.
By the way, we did not have a similar situation when the
data server was version 12.5; it happens after the upgrade
to ASE 15.
Your help is appreciated.
tartampion

unknown

2009-12-28 17:40:19 UTC

Permalink

Thanks Luc, I understand very well what you say, indeed what
you say it is exactly what happens to me.
We have found a work around, we disable the MQ process which
does the delete insert during the bcp process.
Unfortunately, I do not see how Sybase can resolve this
problem, perhaps bcp should be blocked if DML is going on.

We see that even the simple task can face difficulties.
Thanks Again
tartampion.

Post by Luc Van der Veurst
If you want to have a consistent bcp output, you'll have
to lock the table during the bcp process with an
initstring.
If you do a delete of a record that is already processed
by the bcp process and then you insert a record with the
same primary key, and the insert happens on a page that
hasn't been read by the bcp process, you'll have the same
primary key twice in your output file.
Is that what you are experiencing ?
That also happens in 12.5. It all depends on where the
insert takes place. It was easy to reproduce this in 12.5.
A table with 6 records, deleting one record and inserting
one with the same key in one transaction, so the count is
6 at all times, but the output file contains 7 records.
Luc.

following situation: while delete insert(DML) is going
on on our tables, we bcp out data from them.
Unfortuantely, the bcp reads the dirty pages and both

inserted and deleted records are read. >

Mark A. Parsons

2009-12-28 18:30:26 UTC

Permalink

Post by unknown
Unfortunately, I do not see how Sybase can resolve this
problem, perhaps bcp should be blocked if DML is going on.

Sybase *has* resolved this problem by allowing the user to decide how s/he wants to proceed.

As Luc's already mentioned, you can use the --initstring option to implement the behaviour you want.

A couple ideas come to mind ...

1: bcp ..... --initstring 'begin tran lock table <table_name> in exclusive mode'

or

2: bcp ..... --initstring 'set transaction isolation level 3'

------------------------

Unfortunately Sybase's documentation of the '--initstring' option is spotty/inconsistent:

ASE 15.0.2 Utility manual - shows '--initstring' in the bcp syntax block
ASE 15.5 Utility manual - does *not* show '--initstring' in the the bcp syntax block

NOTE: There are other issues with what's included/left-out of the bcp syntax block for the 2 manuals.

ASE 15.0.2/15.5 Utility manuals - have an incorrect example that uses a single hyphen (-initstring == WRONG;
--initstring == CORRECT)

'bcp -help' - does not show the '--initstring' option

Mark A. Parsons

2009-12-28 18:42:31 UTC

Permalink

Just went back through this thread and noticed that the OP is using bcp 12.5.1.

To use the --initstring option it will be necessary to upgrade to a 15.x version of bcp.

I know --initstring became available with SDK 15, but I'm not sure which EBF. The following is the (ASE 15.0.3) bcp -v
output:

Sybase CTBCP Utility/15.0/P-EBF16299 ESD #16/PC Intel/BUILD1500-112/OPT/Sun Oct 12 23:01:12 2008

The bcp version that comes with ASE 15.0.2, 15.0.3 and 15.5 should suffice. You should also be set if you pull a recent
version of SDK 15. ("Duh, Mark!" ?)

Post by Mark A. Parsons

Post by unknown
Unfortunately, I do not see how Sybase can resolve this
problem, perhaps bcp should be blocked if DML is going on.

Sybase *has* resolved this problem by allowing the user to decide how
s/he wants to proceed.
As Luc's already mentioned, you can use the --initstring option to
implement the behaviour you want.
A couple ideas come to mind ...
1: bcp ..... --initstring 'begin tran lock table <table_name> in exclusive mode'
or
2: bcp ..... --initstring 'set transaction isolation level 3'
------------------------
ASE 15.0.2 Utility manual - shows '--initstring' in the bcp syntax block
ASE 15.5 Utility manual - does *not* show '--initstring' in the the bcp syntax block
NOTE: There are other issues with what's included/left-out of the bcp
syntax block for the 2 manuals.
ASE 15.0.2/15.5 Utility manuals - have an incorrect example that uses a
single hyphen (-initstring == WRONG; --initstring == CORRECT)
'bcp -help' - does not show the '--initstring' option

unknown

2009-12-28 19:09:08 UTC

Permalink

Mark,
1- I am afraid what you say as far as locking the table by
bcp is not a solution, how can one do that while
applications are accessing the database? My bcp process has
no right to block the tables in multi-users environment.
2- I do not think that isolation level 3 is going help me
either as the delete/insert by MQ is not in a transaction,
as a result when I bcp out; a row can already be in the bcp
out file and it might be deleted and re-inserted and
thereofre read again.

To me there is no solution but the use some sort of
workaround.

Post by Mark A. Parsons

Post by unknown
Unfortunately, I do not see how Sybase can resolve this
problem, perhaps bcp should be blocked if DML is going

on.
Sybase *has* resolved this problem by allowing the user to
decide how s/he wants to proceed.
As Luc's already mentioned, you can use the --initstring
option to implement the behaviour you want.
A couple ideas come to mind ...
1: bcp ..... --initstring 'begin tran lock table
<table_name> in exclusive mode'
or
2: bcp ..... --initstring 'set transaction isolation level
3'
------------------------
Unfortunately Sybase's documentation of the '--initstring'
ASE 15.0.2 Utility manual - shows '--initstring' in the
bcp syntax block ASE 15.5 Utility manual - does *not* show
'--initstring' in the the bcp syntax block
NOTE: There are other issues with what's
included/left-out of the bcp syntax block for the 2
manuals.
ASE 15.0.2/15.5 Utility manuals - have an incorrect
example that uses a single hyphen (-initstring == WRONG;
--initstring == CORRECT)
'bcp -help' - does not show the '--initstring' option

Mark A. Parsons

2009-12-28 20:17:15 UTC

Permalink

Post by unknown
Mark,
1- I am afraid what you say as far as locking the table by
bcp is not a solution, how can one do that while
applications are accessing the database? My bcp process has
no right to block the tables in multi-users environment.
2- I do not think that isolation level 3 is going help me
either as the delete/insert by MQ is not in a transaction,
as a result when I bcp out; a row can already be in the bcp
out file and it might be deleted and re-inserted and
thereofre read again.

For the isolation=3 example I left off the 'begin tran', so it should have read ...

bcp .... --initstring 'begin tran set transaction isolation level 3'
or
bcp .... --initstring 'set transaction isolation level 3 begin tran'

The net result is that the bcp would obtain&hold shared row/page/table locks until it completes. The shared locks would
in turn block all writes by other processes.

-------------------------

So what you want is to take a snapshot-in-time picture of your table, but you have other processes constantly writing to
(ie, modifying) the table?

What happens to the validity of your bcp data if a) the bcp completes during no writes to the table and b) the MQ
process deletes/inserts a record 1 second after the bcp completes? Doesn't this 'invalidate' your bcp data?

How do you run SELECTs against this table *AND* know for a fact that your result set has not been invalidated due to an
intermittent write?

Short of implementing an Oracle-like/SybaseIQ-like versioning scheme (*yuck*), how does anyone address this issue in an
active database, eg, as soon as a SELECT (or bcp) completes the results are automatically invalidated/out-of-date due to
the constantly changing data?

I'm not sure this is a bcp 'problem' but rather an issue with how a client handles SELECT results when the underlying
data is in constant flux ... ???

-------------------------

Post by unknown
To me there is no solution but the use some sort of workaround.

What kind of workaround(s) do you have in mind?

-------------------------

I've read the previous posts about how this was not a problem with ASE 12.x, but I'm wondering if a) this was a problem
in ASE 12.x but no one noticed or b) something has changed (eg, locking/partitioning/clustered-index scheme, increase in
table size) with the ASE 15.x instance that has increased the chance of 'phantom' rows showing up?

unknown

2009-12-28 21:23:49 UTC

Permalink

Mark,
Agreed, the only solution that I have found is to put on ICE
the DML related process while bcp goes on. I do not block
the others and have my data integrity during my bcp work.

As for version 12.5, I guess that it has been a chance that
we have not observe the same problem. This problem is
realted to nature of work:
snap shot on a moving table, which is not always possible.

Regards.
tartampion.

Post by Mark A. Parsons

Post by unknown
Mark,
1- I am afraid what you say as far as locking the table
by bcp is not a solution, how can one do that while
applications are accessing the database? My bcp process
has no right to block the tables in multi-users
environment. 2- I do not think that isolation level 3 is
going help me either as the delete/insert by MQ is not
in a transaction, as a result when I bcp out; a row can
already be in the bcp out file and it might be deleted
and re-inserted and thereofre read again.

For the isolation=3 example I left off the 'begin tran',
so it should have read ...
bcp .... --initstring 'begin tran set transaction
isolation level 3' or
bcp .... --initstring 'set transaction isolation level
3 begin tran'
The net result is that the bcp would obtain&hold shared
row/page/table locks until it completes. The shared locks
would in turn block all writes by other processes.
-------------------------
So what you want is to take a snapshot-in-time picture of
your table, but you have other processes constantly
writing to (ie, modifying) the table?
What happens to the validity of your bcp data if a) the
bcp completes during no writes to the table and b) the MQ
process deletes/inserts a record 1 second after the bcp
completes? Doesn't this 'invalidate' your bcp data?
How do you run SELECTs against this table *AND* know for a
fact that your result set has not been invalidated due to
an intermittent write?
Short of implementing an Oracle-like/SybaseIQ-like
versioning scheme (*yuck*), how does anyone address this
issue in an active database, eg, as soon as a SELECT (or
bcp) completes the results are automatically
invalidated/out-of-date due to the constantly changing
data?
I'm not sure this is a bcp 'problem' but rather an issue
with how a client handles SELECT results when the
underlying data is in constant flux ... ???
-------------------------

Post by unknown
To me there is no solution but the use some sort of

workaround.
What kind of workaround(s) do you have in mind?
-------------------------
I've read the previous posts about how this was not a
problem with ASE 12.x, but I'm wondering if a) this was a
problem in ASE 12.x but no one noticed or b) something
has changed (eg, locking/partitioning/clustered-index
scheme, increase in table size) with the ASE 15.x
instance that has increased the chance of 'phantom' rows
showing up?