DaniWeb Forum Index > Software Development > C#

C# RSS

LastRecord in DB

Please support our C# advertiser: Intel Parallel Studio Home

•

Join Date: Feb 2009

Posts: 3,193

Reputation: sknake has much to be proud of

Solved Threads: 571

sknake

Online

.NET Enthusiast

Re: LastRecord in DB

#11

Aug 3rd, 2009

IDENT_CURRENT
Problem:
1. You insert into "UserTable" and the row's identity is 2005.
2. Another user inserts into 'UserTable' and the row's identity is 2006.
3. You call IDENT_CURRENT() and it returns 2006. The wrong ID.

 Help with Code Tags 
 sql Syntax (Toggle Plain Text) 
INSERT INTO Table (aColumn) VALUES (aValue)
--after your insert but before your select another user executes the same insert.
SELECT Ident_Current
Insert Into Table (aColumn) Values (aValue)
--after your insert but before your select another user executes the same insert.
Select Ident_Current

So the solution for using IDENT_CURRENT would be to wrap the Insert/Select in a translaction and call (TABLOCKX) on the table and lock everyone else out to ensure you get your identity. This will also block other users from even calling a Select on the table and can create deadlocks on your system and cause the queries to hang until the lock is released, slowing down your system. To get around this you could call (NOLOCK) on your selects but then you're ready dirty data. The list goes on and on... Does this sound like a good solution to you? If it does then go back to using flat files for databases.

@@IDENTITY
Problem:
1. You insert a record in to UserTable
2. You select back @@IDENTITY
3. Everything is good.
Two years later your boss wants you to implement logging in your application so you add a trigger to UserTable to log what user modified which fields.

So at this point we have the trigger implemented.
1. You insert in to the usertable and get ID 2074.
2. The trigger fires off and inserts an entry in "UserTableLog" which gets the record id of 1000
3. You call Select @@IDENTITY and the value 1000 is returned.
4. Your application breaks.

Unless you are intending to locate a record created by a trigger then this is unsafe. It will work but on down the road if you change something and have @@IDENTITY calls throughout your application you will break it. Better yet you hire a new DBA and he does this without telling you. You're not aware of the DB changes, hes not aware how your application works, so nobody has half a clue what happened. Does this sound like a good solution?

SCOPE_IDENTITY()
1. You insert a record in to UserTable and it generates record 8751.
2. Another user inserts a record a in the table creating id #8752
3. triggers fire off from both inserts creating ids #1,#2
4. You call SCOPE_IDENTITY and it returns 8751, the expected value.
5. Life is good.

IF you are using SQL Server Enterprise edition (not express, msde, or developer) and you have more than one logical processor and you have the sql server's max degree of parallelism configured to >1 and you write a complex insert query that generates a parallel execution problem then you may have a problem. That being said it is rather unique circumstances in which this command will fail and there are three published workarounds for it.

Last edited by sknake; Aug 3rd, 2009 at 3:19 pm.

Scott Knake
Custom Software Development
Apex Software, Inc.

•

Join Date: Jul 2007

Posts: 276

Reputation: rapture has a spectacular aura about

Solved Threads: 37

rapture

Offline

Posting Whiz in Training

Re: LastRecord in DB

#12

Aug 3rd, 2009

Thanks, that helps - we don't have any inserts that will be affected that I can think of so it's not a huge deal. I think what I was thinking was he just wanted to get the last record regardless of the reason and if someone else entered a record he might not get the right id returned. But based on his question I think your way gets him the last id that this user entered so that's the way to go.

*but hey man I always though reading dirty data was a good thing . .. jk