Considerations regarding an unusual cause of database concurrency exception.

Filip, Ioan ; Robu, Raul ; Szeidert, Iosif 等

1. INTRODUCTION

ADO.NET is a technology providing two strategies for data access. The main strategy intended primarily for disconnected data access, and the second--for connected data access (Esposito, 2002). In traditional connected data access strategy, a connection to the database server is done and then interacts with it through SQL queries using the connection. The application stays connected to the database server even when it is not using database services. This strategy assumes operations directly against the database, using a data command object. In this case the connection with database must be explicitly open, execute the command to perform the operation, and the connection is closed. The connected data access strategy is used by many other technologies (Petzold, 2002). However, the main benefit of ADO.NET is provided by the disconnected strategy, using an intrinsic DataSet object. The disconnected data access involves the store of data in a DataSet object, which is an in memory-cache set of records that can be accessed disconnected from the data source (usually a database). A DataAdapter object is used to fill the DataSet with records from data source. Also the same DataAdapter updates the information to data source.

Therefore, the user modifies data from DataSet (disconnected from data source) and then save this changes to data source (commit). The DataAdapter object open or close the connection with data source automatically (without an explicitly open or close command). The application automatically connects to the database server when it needs to pass some query and then disconnects immediately after getting the result back and storing it in DataSet. The figure 1 presents the two data access strategies (connected access--based on DataReader object, and disconnected access--based on DataAdapter and DataSet object).

Despite of many DataSet benefits, this strategy generates a few unexpected problems, related to database concurrency exceptions. The cause of these problems is due by non data-synchronizations between DataSet and data source.

[FIGURE 1 OMITTED]

The cause of these problems is due by non data-synchronizations between DataSet and data source (usually a table stored on database server). Usually, data concurrency exceptions occur when multiple users have access to the same data and any single user can update the data without the other users' knowledge. However, this problem can occur even in the case of a single user access, when the application uses triggers fired on the SQL database server side. In the technical literature there are mentioned many solutions that solve the issue in the common case of multiple user access. Those solutions are based on the principles "if a user updated a database table record, another user must refresh the memory-cache information stored in this record according to the new changes and only afterwards it can also update them" or "overwrite all the previously changes" (Esposito, 2002), (Microsoft, 2003), (Papa, 2004).

The main problem is to solve the unusual concurrency exception that occurs in the case of a single disconnected data access (for update/delete one or more rows a of database's table). In this paper will be proved that the Visual Studio tool auto-generated code prohibits the usage of the database trigger based programming. Therefore, a solution must be finding to permit the SQL server side programming based on fired triggers, which represent a useful and powerful programming technique.

2. DATA CONCURRENCY EXCEPTION--STUDY CASE

In order to present a solution to solve the problem of a data concurrency exception occurred for a single data access which fired a database trigger, a study case is presented. There is considered a database table (TEST) with two columns: a first column SSN (Social Security Number) as a primary key (PK), and a second column Birth_Day. The values from second column will be automated updated by a trigger, based on the data from SSN (which include de birth day). When a new row is inserted (needing only the value of SSN column), a trigger is fired, extracting the information from SSN and updating the Birth_Day column. The following UPDATE auto-generated command selects a row (using a WHERE clause) uniquely identified by the values of all columns:

this.adapter.UpdateCommand.CommandText = "UPDATE STUDENT.TEST SET SSN= ?, BIRTH DAY = ? WHERE ((SSN = ?) AND ((? = 1 AND BIRTH DAY IS NULL) OR (BIRTHDAY = ?)))";

[FIGURE 2 OMITTED]

When the client (through a user interface), insert a new row into the table (both the DataSet table and data source table--see INSERT operation (1) from figure 2), on the database server side a trigger is fired, and subsequently, for the inserted row, the value of Birth_Day column is updated (UPDATE operation (1) ). But this update is done only to the data source table (stored on server side). The DataSet table row isn't update in concordance with the real value from data source. Then, if the client tries to change this row (updating or deleting--operation (2)--see the figure 2), the self-generated SQL command take into consideration (to select the row) all the columns values of the selected row. These columns values are read from DataSet table, but they are different from the real values of table stored in data source. So, the command can't identify a row that can be modified (update or delete) and the fallowing error message occurs: "Concurrency violation: the UpdateCommand affected 0 of the expected 1 records". Why? Because no row (record) can be found!

However why concurrency exception? Because the trigger "plays the role of a second virtual user" accessing in write mode the same table row, before the real user, so incoming data changes weren't yet detected by it.

The each row can be also uniquely identified by the value of primary key column. As already stated, in order to allow auto-generate UPDATE and DELETE commands assigned to a DataReader object, the data source table must have a column as primary key. Therefore, a table's row can be uniquely identified (selected) by using only this value. For the considered example, the UPDATE command can be modified based on this consideration, as is presented below:

this.adapter.UpdateCommand.CommandText = "UPDATE STUDENT.TEST SET SSN= ?, BIRTH DAY = ? WHERE (SSN = ?)";

Therefore, it doesn't matter if another column value was changed (into data source on SQL server side), because this value doesn't appear in filtering condition (WHERE clause).

Even if the comments included by Microsoft into the auto-generated file source warn the programmer about a possible incorrect behaviour if the code is changed, the solution works perfectly. Off course, the number of command's parameters must be reduced (therefore, some auto generated code line must be removed). Related to disconnected data access, another small problem can be mentioned: the disconnected data access (using a DataSet object) excludes the usage of the default values for the SQL CREATE TABLE command. Suppose that BIRTH DAY column have as a default value the current date. Therefore, on client side (through a user interface--see figure 3), if a TextBox object or a cell of a DataGridView object (both bound to the BIRTH DAY column of table) are left empty, this means that a NULL value will be inserted in this table cell.

[FIGURE 3 OMITTED]

Therefore, the default values will be ignored! However, to assign a default value to a table column, some code must be written on the client side (for example, assign a value to the properties Text of the TextBox object).

3. CONCLUSION

As part of .NET Microsoft technology, ADO.NET is a set of components representing a new approach of dynamically retrieved data from a data source, offering several advantages over previous versions of ADO (Lendvai & Shi 2007)), especially due to disconnected data access (Zhang & Hu, 2004). However, the disconnected data access strategy has some disadvantages, generating sometimes "inexplicable" errors. Such error, database concurrency exception, and also a possible solution to solve the problem, are exemplified in this paper.

The proposed solution, in order to avoid this exception generated by a trigger's action updating data on a data source (but not updating data into DataSet memory-cache), consist in the change of the auto-generated code. The originality of this approach is the fact that the condition from the "where" clause, of SQL command, which selects the rows updated by the trigger, is more simplified, taking into consideration only the value of the primary key (which uniquely identifies a table row). Moreover, this solution can be used even in some cases of a multiple concurrency data access (when the previously updated row is overwritten by a following update operation). Also, the proposed method, involving the change of auto-generated code, is always valid for all SQL Server (chosen as data source), which allows trigger programming technique. Regarding the problem of a default value specified for a column when a new table is created, the conclusion is that the ADO.NET shifts the programming pole from server side towards client side.

4. REFERENCES

Esposito, D. (2002). Building Web Solution with ASP.NET and ADO.NET. Microsoft Press, ISBN: 9780735615786, Redmond, Washington, USA

Lendvai, A. J., Shi, H. (2007). ADO and ADO.NET Object Model Comparisons: A Relational Perspective, IJCSNS International Journal of Computer Science and Network Security, Vol.7 No.1, January 2007, pp.330-337, ISSN: 1738-7906

Microsoft. (2003). Tackle Data Concurrency Exceptions Using the DataSet Object. MSDN Magazine. April 2003, Available from: http://msdn.microsoft.com/en-us/ magazine/cc188748.asp Accessed: 2008-05-30

Papa, J. (2004). Handling Data Concurrency Using ADO.NET. MSDN Magazine. September 2004, Available from: http://msdn.microsoft.com/en-us/magazine/cc163924.aspx Accessed: 2008-05-30

Petzold, C. (2002). Programming Microsoft Windows with C#, Microsoft Press, ISBN: 9780735613706, Redmond, Washington, USA

Zhang, D., Hu, G.H. (2004). Data sharing over the Internet and Web under ADO.NET, Proceedings of 19th International Conference on Computers and Their Applications, pp. 286-289, ISBN: 1-880843-50-1, Seattle, WA, Mar. 19-20, 2004