Skip to Content
0

ASE157 - Changing table lock scheme leads to data inconsistency

May 05, 2017 at 05:45 PM

134

avatar image

Hi

After finding contention on tables and lots of deadlocks in our preprod ASE15.7 SP62 (Solaris10) database, I converted 10 tables from Allpages to Datarows.

Following this change the application process that merges user data to the same user application ID is failing. The application log files do not show much about what is failing. I only found a few errors about the stored procedure (SP) that merges the appID but there is no direct correlation between this SP failure and the visible errors (the timestamp and the number of errors do not match)

I digged into this SP (which calls many other SPs to merge the user ID and other related ID/data) and only found a "select with holdlock" directly related to locking.

1) What could be the rationale behind this problem?

2) The application team is thinking about a potential transaction failing then rollbacking. How could I monitor a failing transaction or rollbacking transaction in ASE (Mda tables?)?

3) After changing the lock scheme of these tables, I see less deadlocks. I have the feeling that the locking problems just moved to other tables and made somehow the application problems (previously reflected by the deadlocks) to go back up to the surface.

Any ideas how to approach this problem? It's in preprod so I can make changes.

Vincent

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Bret Halford
May 06, 2017 at 12:05 AM
0

I agree with Mark that we could use more details about the nature of the data inconsistency / application failure.

As you are switching to datarows, one thing that does come to mind as being possibly related is that the datarows lock scheme has an interesting property:

At the default isolation level (read committed), uncommitted inserts into datarows tables do not block readers.

http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc00938.1570/html/locking/locking35.htm

[infocenter has been up and down recently. The document linked to here is the 15.7 Performance and Tuning Guide: Locking and Concurrency Control, Chapter 1, section "Skipping uncommitted inserts during selects".]

This sometimes takes developers who are used to working with allpage lock scheme tables, where inserts do block, by surprise as it makes some queries more susceptible to read skew.

Show 2 Share
10 |10000 characters needed characters left characters exceeded

Thank you Bret!

This is indeed a big change that could modify developers' assumptions made during testing...I am going to share this with the developers.

0

I think the problem is due to what Bret was explaning about uncommitted inserts into datarows tables not blocking readers.

The app problem is detected when the same values are inserted into one table. This table has an unique clustered index on 3 columns: 2 IDs and a timestamp. The app developers told me that multiple processes can add entries in this table if they don't find the expected values. If multiple processes can interact with the tables within 1 second or 2, you end up with sort of duplicas with the same 2 IDs but a different timestamp....

I've converted this table from Datarows to Datapages (used to be Allpages). Since then I have not observed any duplicas.

My explanation is probably very unclear but to summarize the application logic should be reviewed to guarantee that multiple processes don't perform the same operation or more inteligently if we decide to use Datarows locking.

0
Mark A Parsons May 05, 2017 at 06:16 PM
0

Would need a lot more info/detail on what you mean by the merge process 'failing' ... generates errors? process silently dies? process generates incorrect results?

While you mentioned changing the locking scheme, you haven't mentioned any follow-on steps you may have performed, eg, recreating compiled objects that reference the tables that just had locking schemes changed ... from the commands reference manual entry for alter table, under the Usage section:

Drop, then re-create compiled objects after changing a table’s lock schema.

So, have you tried dropping/recreating all objects that reference the tables that underwent a change in locking scheme?

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

The only MDA table (that I'm aware of) that specifically tracks rollbacks is monProcessActivity (column = Rollbacks); but this is a rolling count at the process level (ie, no details whatsoever => likely useless for tracking down where a rollback could be occurring).

On the other hand, if you're seeing the monProcessActivity.Rollbacks counter increasing, and *assuming* a rollback is only occurring as the result of an error, you might be able to use monSysStatement.ErrorStatus (!= 0) to track down the error-generating statement(s) that could be causing a rollback.

And of course there's also monDeadLock, w/ the idea being to use the deadlock details to find the losing command who's parent transaction is getting rolled back.

Show 1 Share
10 |10000 characters needed characters left characters exceeded

Thank you Mark for your response.

I am trying to collect more information about the application process...not an easy thing :-(

My script to convert the lock scheme does:

- ddlgen triggers of the table

- sp_spaceused of table

- optdiag statistics $DatabaseName..$TableName ...

- alter table ${TableName} lock ${LockingScheme}

- recreate trigger from the DDLGen trigger definition taken earlier

- sp_spaceused of table

- update index statistics ${TableName} using 1000 values

- sp_recompile '${TableName}'

- sp_spaceused ${TableName}

- optdiag statistics $DatabaseName..$TableName ...

After the "alter table" I usually get:

Non-clustered index (index id = 2) is being rebuilt.

Non-clustered index (index id = 3) is being rebuilt.

Non-clustered index (index id = 4) is being rebuilt. N

Warning: The schema for table 'xxxx' has changed. Drop and re-create each trigger on this table that uses the 'if update(column-name)' clause.

And also:

Each stored procedure and trigger that uses table 'xx' will be recompiled the next time it is executed.

Then I see all the indexes and triggers as it was before.

I am going to check what I can do with the MDA tables.

0