Manipulate locking on Table Indexes
Sohaib Cheema
48,893
User Group Leader
Like
Share
ReportWhile you create a Table inside AOT it is synched in SQL Database. Further as you create some index on the table inside AOT, the index definition is synched from AOT to SQL server.
SQL server being a power tool, provides many options. One of the options allows you to enable/disable locking on indexes. This can be row or page locking. If you see an index in SQL Server, you would find that you can enable or disable the locking options as you may want to set those. Please have a look of next picture taken from SQL server (where table is originally created in AX and its definition was synched from AX to SQL Server)
As you can see that you can change these options on SQL server. If you are thinking about reasons why you need to enable/disable these options, please read SQL Server documentation on locking.
If you have a solution where your schema is created in SQL server, you don’t need to worry. But what if your database schema is not originally owned by SQL Server; rather the definition of SQL tables comes from AX. In this case there are two problems.
1) Every time when you create a table index in AX, you do not have any option to set locking options on index of AX Tables.
2) If you go directly on SQL server and change the locking options, you would face issues, on next database synchronization of AX Database
By default AX enables these locking options for every index that is created in AX. There may be situations when you want to turn off these locking options. If you are facing such situation, you may want to follow the same approach as author of AX is doing. Before I faced this situation, I was also thinking of many solutions, and found no documentation or blogposts on this topic from AX Perspective. As far as I know, majority of AX developers are unaware of locking options of SQL server indexes and its impacts on application.
Below is the piece of code that can be utilized to disable Locks on indexes.
public static server void disableLockEscalation(TableId _tableId)
{
UserConnection userConnection;
Statement statement;
str sqlQuery;
ResultSet resultSet;
int currentLockEscalationSetting;
SysDictTable sysDictTable;
container cacheResult;
SysGlobalObjectCache sgoc = new SysGlobalObjectCache();
#DEFINE.LockEscalationDisabled(1)
// Check cache to see if lock escalation is already disabled for this table
cacheResult = sgoc.find(funcName(), [_tableId]);
if (cacheResult == conNull())
{
sysDictTable = new SysDictTable(_tableId);
if (!sysDictTable
|| !sysDictTable.isSql()
|| sysDictTable.isTempDb())
{
throw error(Error::wrongUseOfFunction(funcname()));
}
// Nothing cached, check database and alter if necessary
userConnection = new UserConnection();
statement = userConnection.createStatement();
sqlQuery = strFmt('SELECT lock_escalation FROM sys.tables WHERE Name = \'%1\'', sysDictTable.name(DbBackend::Sql));
new SqlStatementExecutePermission(sqlQuery).assert();
resultSet = statement.executeQuery(sqlQuery);
if (resultSet.next())
{
currentLockEscalationSetting = resultSet.getInt(1);
}
CodeAccessPermission::revertAssert();
// If not already disabled, then explicitly disable
if (currentLockEscalationSetting != #LockEscalationDisabled)
{
// Disable table lock escalation
statement = userConnection.createStatement();
sqlQuery = strFmt('ALTER TABLE %1 SET (lock_escalation = DISABLE)', sysDictTable.name(DbBackend::Sql));
new SqlStatementExecutePermission(sqlQuery).assert();
statement.executeUpdate(sqlQuery);
CodeAccessPermission::revertAssert();
// Disallow page locks
statement = userConnection.createStatement();
sqlQuery = strFmt('ALTER INDEX ALL ON %1 SET (allow_page_locks = OFF)', sysDictTable.name(DbBackend::Sql));
new SqlStatementExecutePermission(sqlQuery).assert();
statement.executeUpdate(sqlQuery);
CodeAccessPermission::revertAssert();
}
// Cache the fact that lock escalation is now disabled for this table
sgoc.insert(funcName(), [_tableId], [true]);
}
}
This code exists in Dynamics AX in a class named SrsReportDataProviderPreProcess under a method named as disableLockEscalation
All you need to do is to pass you TableId to this method and it would manage to disable locks. To make it simple you can pass it as follows.
SrsReportDataProviderPreProcess::disableLockEscalation(tableNum(YourTableName));
Comments
-
getting deadlock error when concurrent users posting from other application to AX via web service : Posting of sales order line wise:
Cannot edit a record in Sales order line - update table (SalesParmLine). Line number: 0,0000000000.
Deadlock, where one or more users have simultaneously locked the whole table or part of it.
can i disable lock for salesparmline table?
*This post is locked for comments