Guarantee read lock in transaction?

[Jakob Lithner]

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?

 

[Cor Ligthert[MVP]]

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]

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

 

[Wen Yuan Wang [MSFT]]

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)]

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:
(e-mail address removed).
==================================================
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.

 

[Jakob Lithner]

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 ....

 

[Wen Yuan Wang [MSFT]]

Hello Jakob,

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.

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.

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.

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:
(e-mail address removed).
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Jakob Lithner]

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.

Thanks for useful feedback!

 

[Wen Yuan Wang [MSFT]]

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:
(e-mail address removed).
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Cor Ligthert[MVP]]

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