ASE157 - Changing table lock scheme leads to data inconsistency

[Former Member]

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

[Former Member]

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.

[former_member188958 · Accepted solution]

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.ht...

[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.

[Former Member · Accepted solution]

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.

[Former Member · Accepted solution]

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.