Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Detect uncommitted Insert

7 posts in General Discussion Last posting was on 2012-01-05 06:26:22.0Z
Bill Chie Posted on 2012-01-03 04:33:08.0Z
Sender: 7d51.4f0283bc.1804289383@sybase.com
From: Bill Chie
Newsgroups: sybase.public.ase.general
Subject: Detect uncommitted Insert
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f028504.7db3.1681692777@sybase.com>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 2 Jan 2012 20:33:08 -0800
X-Trace: forums-1-dub 1325565188 172.20.134.41 (2 Jan 2012 20:33:08 -0800)
X-Original-Trace: 2 Jan 2012 20:33:08 -0800, 172.20.134.41
Lines: 15
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30802
Article PK: 73692

Dear all,

We have a program to download updated data form Sybase ASE
DB to Sybase ASA DB, but we hit a problem that the select
statement will not block by an uncommitted insert and
causing missing data form time to time.

Because a dirty read is not a option in our environment, I
would like to know if there are any method to detect an
uncommitted insert exists.

Thanks in advance

Regards
Bill


Luc Van der Veurst Posted on 2012-01-03 09:12:06.0Z
From: "Luc Van der Veurst" <dba_azvub@hotmail.com>
Newsgroups: sybase.public.ase.general
References: <4f028504.7db3.1681692777@sybase.com>
Subject: Re: Detect uncommitted Insert
Lines: 31
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2900.5931
X-RFC2646: Format=Flowed; Original
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.6157
X-Forwarded: by - (DeleGate/5.8.7)
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4f02c666$1@forums-1-dub>
Date: 3 Jan 2012 01:12:06 -0800
X-Trace: forums-1-dub 1325581926 10.22.241.152 (3 Jan 2012 01:12:06 -0800)
X-Original-Trace: 3 Jan 2012 01:12:06 -0800, vip152.sybase.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30803
Article PK: 73693

Hi,

Can you give an esample with table and index descriptions, insert
and select statements ?

There are some things I don't understand. You say you don't want
dirty reads, but if you read uncommitted inserts, you are doing dirty
reads. And if there is an uncommitted insert in your deownloaded data, it
seems that you would have too much data in ASA and not missing
data.

Luc.

<Bill Chie> wrote in message news:4f028504.7db3.1681692777@sybase.com...
> Dear all,
>
> We have a program to download updated data form Sybase ASE
> DB to Sybase ASA DB, but we hit a problem that the select
> statement will not block by an uncommitted insert and
> causing missing data form time to time.
>
> Because a dirty read is not a option in our environment, I
> would like to know if there are any method to detect an
> uncommitted insert exists.
>
> Thanks in advance
>
> Regards
> Bill


Bill Chie Posted on 2012-01-05 06:18:09.0Z
Sender: 5b03.4f0432ac.1804289383@sybase.com
From: Bill Chie
Newsgroups: sybase.public.ase.general
Subject: Re: Detect uncommitted Insert
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f0540a1.15e2.1681692777@sybase.com>
References: <4f02c666$1@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 4 Jan 2012 22:18:09 -0800
X-Trace: forums-1-dub 1325744289 172.20.134.41 (4 Jan 2012 22:18:09 -0800)
X-Original-Trace: 4 Jan 2012 22:18:09 -0800, 172.20.134.41
Lines: 63
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30809
Article PK: 73699

Hi Luc,

Thanks for the reply.

The table can be in any structure, but all of them will:
1) Update the "update date field" in trigger when modify
2) Have a index on the "update date field"

And we will locate new rows by "SELECT * FROM [TABLE] WHERE
UPD_DT > [DATETIME VALUE]"

For example:
1) At 01:30> ROW INSERTED
2) At 02:00> SELECT SELECT * FROM [TABLE] WHERE UPD_DT >
"2011-11-11 01:00"
3) At 02:01> ROW COMMIT
4) At 03:00> SELECT SELECT * FROM [TABLE] WHERE UPD_DT >
"2011-11-11 02:00"

The UPD_DT will be 01:30 after commit, and both SQL in step
2 and 3 will not be able to select the row, but SQL in step
2 also will not block by the uncommitted INSERT, which
causing lost of data.

I am not trying to dirty, but want to detect if one exists,
because a uncommitted insert will not block my select
statement while the statement will read it out if it is
committed.

Regards
Bill

> Hi,
>
> Can you give an esample with table and index descriptions,
> insert and select statements ?
>
> There are some things I don't understand. You say you
> don't want dirty reads, but if you read uncommitted
> inserts, you are doing dirty reads. And if there is an
> uncommitted insert in your deownloaded data, it seems that
> you would have too much data in ASA and not missing data.
>
> Luc.
>
> <Bill Chie> wrote in message
> > news:4f028504.7db3.1681692777@sybase.com... Dear all,
> >
> > We have a program to download updated data form Sybase
> > ASE DB to Sybase ASA DB, but we hit a problem that the
> > select statement will not block by an uncommitted insert
> > and causing missing data form time to time.
> >
> > Because a dirty read is not a option in our environment,
> > I would like to know if there are any method to detect
> > an uncommitted insert exists.
> >
> > Thanks in advance
> >
> > Regards
> > Bill
>
>


Rob V Posted on 2012-01-03 15:47:24.0Z
From: Rob V <rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY>
Reply-To: rob@DO.NOT.SPAM.sypron.nl.REMOVE.THIS.DECOY
Organization: Sypron BV
User-Agent: Mozilla/5.0 (Windows NT 5.1; rv:8.0) Gecko/20111105 Thunderbird/8.0
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Detect uncommitted Insert
References: <4f028504.7db3.1681692777@sybase.com>
In-Reply-To: <4f028504.7db3.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4f03230c@forums-1-dub>
Date: 3 Jan 2012 07:47:24 -0800
X-Trace: forums-1-dub 1325605644 10.22.241.152 (3 Jan 2012 07:47:24 -0800)
X-Original-Trace: 3 Jan 2012 07:47:24 -0800, vip152.sybase.com
Lines: 40
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30805
Article PK: 73695


On 03-Jan-2012 05:33, Bill Chie wrote:
> Dear all,
>
> We have a program to download updated data form Sybase ASE
> DB to Sybase ASA DB, but we hit a problem that the select
> statement will not block by an uncommitted insert and
> causing missing data form time to time.
>
> Because a dirty read is not a option in our environment, I
> would like to know if there are any method to detect an
> uncommitted insert exists.
>
> Thanks in advance
>
> Regards
>

Assuming we're talking about uncomitted inserts into
datartows/datapages-locked tables: by enabling traceflag 693, these
uncommitted inserts become blocking again. TF 693 turns off this
concurrency optimization (as well as some others) for DOL tables.
Apart from this traceflag, there is no other workaround I am aware of.

HTH,

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

Certified Professional DBA for Sybase ASE, IQ, Replication Server

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

rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter: @rob_verschoor
Sypron B.V., The Netherlands | Chamber of Commerce 27138666
-----------------------------------------------------------------


Bill Chie Posted on 2012-01-05 06:21:53.0Z
Sender: 5b03.4f0432ac.1804289383@sybase.com
From: Bill Chie
Newsgroups: sybase.public.ase.general
Subject: Re: Detect uncommitted Insert
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f054181.1629.1681692777@sybase.com>
References: <4f03230c@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 4 Jan 2012 22:21:53 -0800
X-Trace: forums-1-dub 1325744513 172.20.134.41 (4 Jan 2012 22:21:53 -0800)
X-Original-Trace: 4 Jan 2012 22:21:53 -0800, 172.20.134.41
Lines: 52
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30810
Article PK: 73697

Hi Rob,

Thanks! Will definitely give it a try.

Regards
Bill

> On 03-Jan-2012 05:33, Bill Chie wrote:
> > Dear all,
> >
> > We have a program to download updated data form Sybase
> > ASE DB to Sybase ASA DB, but we hit a problem that the
> > select statement will not block by an uncommitted insert
> > and causing missing data form time to time.
> >
> > Because a dirty read is not a option in our environment,
> > I would like to know if there are any method to detect
> > an uncommitted insert exists.
> >
> > Thanks in advance
> >
> > Regards
> >
>
> Assuming we're talking about uncomitted inserts into
> datartows/datapages-locked tables: by enabling traceflag
> 693, these uncommitted inserts become blocking again. TF
> 693 turns off this concurrency optimization (as well as
> some others) for DOL tables. Apart from this traceflag,
> there is no other workaround I am aware of.
>
> HTH,
>
> Rob V.
> ----------------------------------------------------------
> ------- Rob Verschoor
>
> Certified Professional DBA for Sybase ASE, IQ, Replication
> Server
>
> Author of Sybase books (order online at
> www.sypron.nl/shop): "Tips, Tricks & Recipes for Sybase
> ASE" "The Complete Sybase IQ Quick Reference Guide" (new!)
> "The Complete Sybase ASE Quick Reference Guide"
> "The Complete Sybase Replication Server Quick Reference
> Guide"
>
> rob@NO.SPAM.sypron.nl | www.sypron.nl | Twitter:
> @rob_verschoor Sypron B.V., The Netherlands | Chamber of
> Commerce 27138666
> ----------------------------------------------------------
> -------


"Mark A. Parsons" <iron_horse Posted on 2012-01-03 12:24:59.0Z
From: "Mark A. Parsons" <iron_horse@no_spamola.compuserve.com>
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.2.13) Gecko/20101207 Lightning/1.0b2 Thunderbird/3.1.7
MIME-Version: 1.0
Newsgroups: sybase.public.ase.general
Subject: Re: Detect uncommitted Insert
References: <4f028504.7db3.1681692777@sybase.com>
In-Reply-To: <4f028504.7db3.1681692777@sybase.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: vip152.sybase.com
X-Original-NNTP-Posting-Host: vip152.sybase.com
Message-ID: <4f02f39b@forums-1-dub>
Date: 3 Jan 2012 04:24:59 -0800
X-Trace: forums-1-dub 1325593499 10.22.241.152 (3 Jan 2012 04:24:59 -0800)
X-Original-Trace: 3 Jan 2012 04:24:59 -0800, vip152.sybase.com
Lines: 86
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30804
Article PK: 73696

From a strictly technical point of view ...

1 - Look for exclusive page/row locks in syslocks (or monLocks); though I'm not sure you'll be able to distinguish
between a lock for a DELETE vs a lock for an UPDATE vs a lock for an INSERT vs a lock for a combo DML operation (eg,
INSERT+UPDATE).

2 - Look for a non-zero transaction id in systransactions; though this is a session level indicator which means you
probably won't be able to tell how many DML commands are wrapped up in that single transaction (unless you can glean
some info from the session's login and/or transaction name); also realize that systransactions isn't always up to date,
eg, if a trigger hasn't fired and/or a checkpoint hasn't been issued it's possible for the transaction id to be 0x0 even
though there's an open transaction.

----------

I'm in the same boat with Luc ... I'm not sure I understand your issue.

When you say data is missing ... how are you determining this? I'm assuming your ASE has a constant flow of
transactions so any snapshot of data you take (for copying into ASA) will quickly become old/stale (ie, the data in your
ASE and ASA instances will rarely, if ever, be in sync).

Obviously (?) the following leads to 'missing data', but only because the comparison is done with 2 different snapshots
of data:

========= ASE
insert t1 values (1,2)
========= ASA
insert t1 values (1,2)
========= ASE
insert t1 values (3,4)
========= ASE vs ASA comparison
** ASE.t1 has 2 rows
** ASA.t1 has 1 row
=========

So comparisons of data between ASE and ASA have to be done at a time when a) there's no new DML activity in ASE and b)
ASA has just been synced up with a data movement from ASE ... so that you are comparing the same snapshots in both
databases.

----------

How are you downloading your data from ASE to ASA? How do you handle the synchronization of data if/when you have a
transaction that spans multiple tables? I could see how you could end up with 'missing data' in a scenario like the
following:

======== ASA
insert all records from ASE's t1 table
======== ASE
begin tran
insert t1 values (1,2,3)
select @t1_ident=@@identity -- assume @@identity=100
insert s1 values (@t1_ident,4,5,6)
commit tran
======== ASA
insert all records from ASE's s1 table
======== ASE vs ASA comparison
** ASA's s1 table has a record for @t1_ident=100,
** but ASA's t1 table is missing the matching record
========

In this scenario I'm assuming the data movement script moves data one table at a time from ASE to ASA. In between the 2
data movements into ASA, you have a transaction that inserts new records into ASE's t1/s1 tables. Without a means of
insuring transactional consistency in ASA you end up with a row in s1 for @t1_ident=100 without a matching row in t1
(ie, data missing from t1).

Obviously (?) in this type of setup the data movement script has to be smart enough to know how to insure transactional
consistency (eg, delete s1 where there's no matching row in t1; have RI - or triggers - in place to maintain
transactional consistency => the insert of the orphaned s1 row into ASA would get rolled back due to not having a
matching row in t1; etc).

On 01/02/2012 23:33, Bill Chie wrote:
> Dear all,
>
> We have a program to download updated data form Sybase ASE
> DB to Sybase ASA DB, but we hit a problem that the select
> statement will not block by an uncommitted insert and
> causing missing data form time to time.
>
> Because a dirty read is not a option in our environment, I
> would like to know if there are any method to detect an
> uncommitted insert exists.
>
> Thanks in advance
>
> Regards
> Bill


Bill Chie Posted on 2012-01-05 06:26:22.0Z
Sender: 5b03.4f0432ac.1804289383@sybase.com
From: Bill Chie
Newsgroups: sybase.public.ase.general
Subject: Re: Detect uncommitted Insert
X-Mailer: WebNews to Mail Gateway v1.1t
Message-ID: <4f05428e.1653.1681692777@sybase.com>
References: <4f02f39b@forums-1-dub>
NNTP-Posting-Host: 172.20.134.41
X-Original-NNTP-Posting-Host: 172.20.134.41
Date: 4 Jan 2012 22:26:22 -0800
X-Trace: forums-1-dub 1325744782 172.20.134.41 (4 Jan 2012 22:26:22 -0800)
X-Original-Trace: 4 Jan 2012 22:26:22 -0800, 172.20.134.41
Lines: 123
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub sybase.public.ase.general:30811
Article PK: 73704

Hi Mark,

Thanks for the suggestion, we also tried to use locks, but
as you say, we are not able to distinguish between
INSERT/UPDATE/DELETE etc...

I said missing data before we download data by update date,
for example, if I download the data until 2011-11-11 11:00,
but I find ASA (i.e. the target DB) do not have a record at
2011-11-11 10:00, then I call it missing data.

Regards
Bill

> From a strictly technical point of view ...
>
> 1 - Look for exclusive page/row locks in syslocks (or
> monLocks); though I'm not sure you'll be able to
> distinguish between a lock for a DELETE vs a lock for an
> UPDATE vs a lock for an INSERT vs a lock for a combo DML
> operation (eg, INSERT+UPDATE).
>
> 2 - Look for a non-zero transaction id in systransactions;
> though this is a session level indicator which means you
> probably won't be able to tell how many DML commands are
> wrapped up in that single transaction (unless you can
> glean some info from the session's login and/or
> transaction name); also realize that systransactions isn't
> always up to date, eg, if a trigger hasn't fired and/or a
> checkpoint hasn't been issued it's possible for the
> transaction id to be 0x0 even though there's an open
> transaction.
>
> ----------
>
> I'm in the same boat with Luc ... I'm not sure I
> understand your issue.
>
> When you say data is missing ... how are you determining
> this? I'm assuming your ASE has a constant flow of
> transactions so any snapshot of data you take (for copying
> into ASA) will quickly become old/stale (ie, the data in
> your ASE and ASA instances will rarely, if ever, be in
> sync).
>
> Obviously (?) the following leads to 'missing data', but
> only because the comparison is done with 2 different
> snapshots of data:
>
> ========= ASE
> insert t1 values (1,2)
> ========= ASA
> insert t1 values (1,2)
> ========= ASE
> insert t1 values (3,4)
> ========= ASE vs ASA comparison
> ** ASE.t1 has 2 rows
> ** ASA.t1 has 1 row
> =========
>
> So comparisons of data between ASE and ASA have to be done
> at a time when a) there's no new DML activity in ASE and
> b) ASA has just been synced up with a data movement from
> ASE ... so that you are comparing the same snapshots in
> both databases.
>
> ----------
>
> How are you downloading your data from ASE to ASA? How do
> you handle the synchronization of data if/when you have a
> transaction that spans multiple tables? I could see how
> you could end up with 'missing data' in a scenario like
> the following:
>
> ======== ASA
> insert all records from ASE's t1 table
> ======== ASE
> begin tran
> insert t1 values (1,2,3)
> select @t1_ident=@@identity -- assume @@identity=100
> insert s1 values (@t1_ident,4,5,6)
> commit tran
> ======== ASA
> insert all records from ASE's s1 table
> ======== ASE vs ASA comparison
> ** ASA's s1 table has a record for @t1_ident=100,
> ** but ASA's t1 table is missing the matching record
> ========
>
> In this scenario I'm assuming the data movement script
> moves data one table at a time from ASE to ASA. In
> between the 2 data movements into ASA, you have a
> transaction that inserts new records into ASE's t1/s1
> tables. Without a means of insuring transactional
> consistency in ASA you end up with a row in s1 for
> @t1_ident=100 without a matching row in t1 (ie, data
> missing from t1).
>
> Obviously (?) in this type of setup the data movement
> script has to be smart enough to know how to insure
> transactional consistency (eg, delete s1 where there's no
> matching row in t1; have RI - or triggers - in place to
> maintain transactional consistency => the insert of the
> orphaned s1 row into ASA would get rolled back due to not
> having a matching row in t1; etc).
>
>
> On 01/02/2012 23:33, Bill Chie wrote:
> > Dear all,
> >
> > We have a program to download updated data form Sybase
> > ASE DB to Sybase ASA DB, but we hit a problem that the
> > select statement will not block by an uncommitted insert
> > and causing missing data form time to time.
> >
> > Because a dirty read is not a option in our environment,
> > I would like to know if there are any method to detect
> > an uncommitted insert exists.
> >
> > Thanks in advance
> >
> > Regards
> > Bill