Having access to data that can be trusted is essential for effective decision making and, ultimately, for business success. But, just because a DBMS supports ACID does not guarantee that your data will be correct, cautions Craig S. Mullins,president and principal consultant, Mullins Consulting, Inc., who spoke at Data Summit 2017.
Data integrity in an operational DBMS that entails making sure that the data is always accurate and correct, according to Mullins, who is also a DBTA columnist and IBM Gold consultant.
With the advance of NoSQL database systems that may not support true ACID, DBAs must understand how their DBMS works, the locking and isolation options available, and how to appropriately code both the database access code and the application code to achieve proper data integrity. In his presentation, Mullins covered issues such as how relational and NoSQL differ in terms of consistency, the significance of ACID (atomicity, consistency, isolation, durability) compliance, how data can be compromised even with ACID compliance, the impact—and different varieties—of eventual consistency on data, and the implications of referential integrity.
There are many aspects to data integrity, such as making sure that data is correct in terms of internal DBMS functionality, correct in terms of business meaning, correct in terms of the actual values of data elements, correct in terms of the relationship between the data elements, and that changes are correctly applied when requested.
An essential point is that data integrity and consistency is not a simple all-or-nothing proposition, warned Mullins, who stressed that it is critical to identify what is supported and how, as well as to educate developers about using the proper parameters. A DBMS with ACID where all programs use uncommitted read is not likely to result in data integrity, and on the flip side of the coin, a DBMS with eventual consistency programmed appropriate can result in having reasonable data integrity. The key element is to understand the application/business needs and proceed accordingly.
Many conference presentations, including Mullins’ 37-page presentation on "Data Integrity in Operational Database Management Systems," have been made available by speakers at www.dbta.com/datasummit/2017/presentations.aspx.