|
|
You're in the
dotnet ado.net
group:Guarantee read lock in transaction?
dotnet ado.net:
I have a SQL table where the PrimaryKey column ID is a long. There is a
ForeignKey relation from column ParentID back to the same table to track relation. Default for new records is always to relate back to itself. I don't really care what value is used for ID on new records, but as I need to have the same value on ParentID as in ID I have so far created the value manually based on the current used max value of all records. Problem is I would like to guarantee the same value is not used for two different inserts. Is it possible to guarantee read lock in a transaction? I am using LINQ-to-SQL and planned to use TransactionScope mechanism. But then it struck me that this solution might not be safe for reads. I would like to avoid two subsequent inserts to read the same current-max-used-ID of my table which would mean they calculated the same new ID for the PrimaryKey.
I am familiar with GUID and you are probably right that it is a good
alternative. The problem is I have a production database with 5 years data and 1 million records, and the table we are talking of is the main table with all other tables related to it. It will be a lot of work to change all relations to GUID and rewrite all stored procedures .... I would like to avoid that track
Jakob,
For this is the GUID (Global Unique IDentifier) created have a look at it, it is simple to use, however it is not an autoidentifier, you have to create it in your datarows by a simple ID = New GUID or the as forever more characters needed C# way ID = new GUID(); Cor "Jakob Lithner" <jaklithn@noemail.noemail> schreef in bericht news:86A39F16-955C-417D-852F-D281AA3E09F6@microsoft.com... [quoted text, click to view] >I have a SQL table where the PrimaryKey column ID is a long. There is a
> ForeignKey relation from column ParentID back to the same table to track > relation. Default for new records is always to relate back to itself. > > I don't really care what value is used for ID on new records, but as I > need > to have the same value on ParentID as in ID I have so far created the > value > manually based on the current used max value of all records. > > Problem is I would like to guarantee the same value is not used for two > different inserts. > > Is it possible to guarantee read lock in a transaction? > I am using LINQ-to-SQL and planned to use TransactionScope mechanism. > But then it struck me that this solution might not be safe for reads. > I would like to avoid two subsequent inserts to read the same > current-max-used-ID of my table which would mean they calculated the same > new > ID for the PrimaryKey. > > Is there a better way to accomplish the same thing?
Thank you Wen for your answer.
Correct. For new records ParentID should be same as PrimaryKey ID column. Currently I am not using Identity on PrimaryKey ID column. The reason is I thought it was impossible to set ParentID then. ParentID is defined as NOT NULL. Is it safe to use IDENT_CURRENT? Is there no possibility of another call coming in between and mixing the values? Where should I call IDENT_CURRENT? It looks like a candidate for a trigger. Right now I am trying to move all logic from stored prcedures to C# using LINQ-to-SQL for the data mapping. One way is probably to allow one small stored procedure that calls IDENT_CURRENT on the table and drag this procedure into my database mapper? But it gives an odd setup. If ID is generated by Identity property I need to set ParentID to IDENT_CURRENT+1 "hoping" this will result in the same value for ID and ParentID. Or I need to allow ParentID to be NULL and set its value on a subsequent update call. IsolationLevel is the other solution track. I mentioned I tried TransactionScope object as I assumed this is more lightweight than Transation object. But I guess I need to switch to Transaction object to use the IsolationLevel? I have a function that reads the current MAX ID value. What IsolationLevel would be appropriate to guarantee no other call will return the same result? MAX is a bit tricky for in a way the scope of it is the whole table ....
Hello Jaklithn,
I understood the default ParentID for new record should be the same value as ID, correct? If I misunderstood anything here, please don't hesitate to correct me. Could you let me know how did you define the primaryKey? Is it an identity column? If true, I don't think we need to calculate the id by ourselves. Have you tried with IDENT_CURRENT function? It returns the last identity value generated for a specified table. For example: insert table_1 value (IDENT_CURRENT("table_1")) http://msdn2.microsoft.com/en-us/library/ms175098.aspx [IDENT_CURRENT (Transact-SQL)] [quoted text, click to view] >Is it possible to guarantee read lock in a transaction? You can Specifies the isolation level in IsolationLevel. For more detailed information, you may refer to http://msdn2.microsoft.com/en-us/library/system.transactions.isolationlevel. aspx [IsolationLevel Enumeration] Hope this helps. Please feel free to let me know if you have any more concern. We are glad to assist you. Have a great day, Best regards, Wen Yuan Microsoft Online Community Support Delighting our customers is our #1 priority. We welcome your comments and suggestions about how we can improve the support we provide to you. Please feel free to let my manager know what you think of the level of service provided. You can send feedback directly to my manager at: msdnmg@microsoft.com. ================================================== Get notification to my posts through email? Please refer to http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif ications. Note: The MSDN Managed Newsgroup support offering is for non-urgent issues where an initial response from the community or a Microsoft Support Engineer within 1 business day is acceptable. Please note that each follow up response may take approximately 2 business days as the support professional working with you may need further investigation to reach the most efficient resolution. The offering is not appropriate for situations that require urgent, real-time or phone-based interactions or complex project analysis and dump analysis issues. Issues of this nature are best handled working with a dedicated Microsoft Support Engineer by contacting Microsoft Customer Support Services (CSS) at http://msdn.microsoft.com/subscriptions/support/default.aspx. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights.
Hello Jakob,
[quoted text, click to view] >Is it safe to use IDENT_CURRENT? Is there no possibility of another call coming in between and mixing the values?You can use it as "insert table_1 value (IDENT_CURRENT("table_1"))". It will be excited with insert command. Thereby, it won't return the same value for two different commands. But, please notice: IDENT_CURRENT doesn't help if ID column doesn't use Identity. [quoted text, click to view] >Right now I am trying to move all logic from stored prcedures to C# using LINQ-to-SQL for the data mapping.Could you let me know why did you decide to move logic from stored procedure into Linq? In my opinion, the insert/update task should be defined in stored procedure, due to security issue. [quoted text, click to view] >Or I need to allow ParentID to be NULL and set its value on a subsequent update call.That's a great idea to allow ParentID to be NULL and set its value on a subsequent update call. I think this is the simplest way. [quoted text, click to view] >What IsolationLevel would be appropriate to guarantee no other call will return the same result?To guarantee no other call will be renturn the same result, you can set IsolationLevel to Serializable. This requires that each transaction complete before any other transactions are allowed to operate on the data. Hope this helps, please feel free to let us know if you have any more concern. We are glad to assist you. Have a great day, Best regards, Wen Yuan Microsoft Online Community Support Delighting our customers is our #1 priority. We welcome your comments and suggestions about how we can improve the support we provide to you. Please feel free to let my manager know what you think of the level of service provided. You can send feedback directly to my manager at: msdnmg@microsoft.com. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights.
1) IDENT_CURRENT in Insert statement is quite clever. The downside is I have
to override the Insert statement generated by LINQ-to-SQL. 2) External systems previously called logic in database. This forced me to have quite complex logic in stored procedures. It has given me many problems as logic is changed. I found Enum values in stored procedures is no good idea when many of them needed to change .... The logic in SQL is also quite limited causing complicated code. Now I am switching interface and will use webservices for external systems. In .Net code I have much better overview and will immediately detect dependencies of changes. What kind of security issue are you afraid of? The SQL user will only be used by my code and must be protected anyhow. Integrity should be possible to uphold in both cases. 3) I always like to guarantee data integrity in database and hesitate to open up ParentID column by allowing NULL. Actually I decided to go for a compromise. I call IDENT_CURRENT function and use this value to calculate expected next ID which is used to set ParentID value. After insert I check created ID column to ensure it is same as ParentID. In most cases it will of course be identical. In most unlikely case where they differ I am able to adjust ParentID to the ID value. Much ado about nothing maybe, but it is robust and I am happy :) 4) I guessed IsolationLevel=Serialize was necessary but decided to avoid it because it is too expensive for intensive transactions.
Hello Jaklithn,
Thanks for your reply. That's great. :) It seems you have developed an auto-recover mechanism to grantee the ParentID value must match the value in ID column. Actually, the security issue which I have concern is that the schema of datatable has been exposed in T-SQL update command. This maybe dangerous if some guys want to hack your database. But, if you ensure you could protect your SQL user account, this should be fine... Anyway, if you have any more concern or you face any further issue, please also feel free to update here again. We are glad to assist you. Have a great day, Best regards, Wen Yuan Microsoft Online Community Support Delighting our customers is our #1 priority. We welcome your comments and suggestions about how we can improve the support we provide to you. Please feel free to let my manager know what you think of the level of service provided. You can send feedback directly to my manager at: msdnmg@microsoft.com. ================================================== This posting is provided "AS IS" with no warranties, and confers no rights.
Jakob,
Now I have reread itk I see that you are not Using ADONet (the name of this newsgroup). With that it is simply possible to tell DataRow.ParentRow(TheParentRow) For those actualy having this problem using AdoNet and searching this newsgroup. Cor "Jakob Lithner" <jaklithn@noemail.noemail> schreef in bericht news:5C4151B7-C099-49C6-86C1-F6A39C5C8396@microsoft.com... [quoted text, click to view] >I am familiar with GUID and you are probably right that it is a good
> alternative. > The problem is I have a production database with 5 years data and 1 > million > records, and the table we are talking of is the main table with all other > tables related to it. It will be a lot of work to change all relations to > GUID and rewrite all stored procedures .... I would like to avoid that > track > if possible :)
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
December 2007
January 2008
February 2008
March 2008
April 2008
| home · blog · groups | Questions? Comments? Contact the dn |