Case study 37: Changing locking schema on table - it is not so simple as it looked like
Platforms: Solaris 10, et all
SAP ASE version: 15.0, et all.
Background story: We had a situation where some deadlock have been occuring from time to time. We investigate it and come to the conlustion that if we change table level locking from allpages to datarows, that deadlock will go. But, one thing is changing locking in development and another in production environment. In production environment we had full database recovery model with one full backup daily, and on every ten minutes backup of transaction log. Also, in production env we've got Replication Server. What are side efects of table locking level change? There are many!
Solution: If You want to run command for changing table locking level, You should know that this operation is non-logged operation, and that it would need that database option "select into/bulkcopy/pllsort" is turned on. Once You run this non logged operation, You will need to run full backup after that, since this non logged operation is breaking transaction log chain, and if You run following command, after locking schema change,
select tran_dumpable_status('mydb')
go
You will get an error 16, meaning "16 – an operation preventing a dump of the transaction log was performed."
Since You will need to do the full backap again, it would require that You have sufficient space for transaction log, since during full backup (on version 15.0 and some higher versions) You will not be able to create transaction log backup, and if full backup is taking lots of time (in my case about 10 hours), You will need transaction log big enough that will hold all transaction logs created during full backup. And to make things more interesting, next time when You run some statement or procedure against changed table, they will throw an error like this:
Could not execute query, reason: Schema for database object 'test' has changed since compilation of this query. Please re-execute query.
It means that if application which should execute a query against changed database(table) is not properlly designed, You will face an issue, if it does not rerun the query. You can try with stopping applications before locking schema changes.
Locking schema change (from allpages to datarows) is copying data and recreate indexes. So, during that time (I did not checked which lock is hold during locking schema change), You would probabbly need to do that during downtime.
And if we take into account Replication Server, things get more complicated since You can not just truncate transaction log (which is filling during full backup...).
Conclusion: Even "small" changes can have ripple efect, and these effects are even more important on production environment. So, plan and test properlly, and be ready for some suprise, since some components can exist on production which do not exist on NPE environments, and which can be also affected. I can not say that I am happy with these database features. More details (for version 16.0) can be found here
