473,574 Members | 2,288 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Concurrent Access - New Record, Primary Key problem....


Can someone explain what happens when two users concurrently attempt to
create a new record in a table with an autonumber primary key? For example,
user 1 creates a new record and manipulates it within a transaction making
use (perhaps) of the @@IDENTITY value when creating other, related records.
Before this transaction is complete, user 2 creates a new record and does
the same thing. Presumably they will both have the same @@IDENTITY? If
this is the case, how is it possible to manage such a situation?

Thanks.
Jul 23 '05 #1
2 3205

"Robin Tucker" <id************ *************@r eallyidont.com> wrote in
message news:cs******** ***********@new s.demon.co.uk.. .

Can someone explain what happens when two users concurrently attempt to
create a new record in a table with an autonumber primary key? For
example, user 1 creates a new record and manipulates it within a
transaction making use (perhaps) of the @@IDENTITY value when creating
other, related records. Before this transaction is complete, user 2
creates a new record and does the same thing. Presumably they will both
have the same @@IDENTITY? If this is the case, how is it possible to
manage such a situation?

Thanks.


No, each session will have a different value, so there's no problem with
concurrency - check out SCOPE_IDENTITY( ), IDENT_CURRENT() and @@IDENTITY in
Books Online.

Note that just defining a column as an identity column is not enough to
guarantee uniqueness - you can still create duplicates manually (see SET
IDENTITY_INSERT in BOL), so if you want to use the column as a PK, make sure
it is declared as a PK when you create the table.

Simon
Jul 23 '05 #2
Ok that simplifies things somewhat. Yes, the columns in question are both
Identity and Primary Key.

Thanks very much for your reply.

Robin

"Simon Hayes" <sq*@hayes.ch > wrote in message
news:41******** **@news.bluewin .ch...

"Robin Tucker" <id************ *************@r eallyidont.com> wrote in
message news:cs******** ***********@new s.demon.co.uk.. .

Can someone explain what happens when two users concurrently attempt to
create a new record in a table with an autonumber primary key? For
example, user 1 creates a new record and manipulates it within a
transaction making use (perhaps) of the @@IDENTITY value when creating
other, related records. Before this transaction is complete, user 2
creates a new record and does the same thing. Presumably they will both
have the same @@IDENTITY? If this is the case, how is it possible to
manage such a situation?

Thanks.


No, each session will have a different value, so there's no problem with
concurrency - check out SCOPE_IDENTITY( ), IDENT_CURRENT() and @@IDENTITY
in Books Online.

Note that just defining a column as an identity column is not enough to
guarantee uniqueness - you can still create duplicates manually (see SET
IDENTITY_INSERT in BOL), so if you want to use the column as a PK, make
sure it is declared as a PK when you create the table.

Simon

Jul 23 '05 #3

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
2719
by: Frances | last post by:
Hi All, I'm having a problem trying to add a record to a simple Access 2000 db (db is very similar to an address book but with more info than the usual address, phone, etc.). The database is one table, 36 fields. The record_id field is an autonumber field (long int) and primary key. The rest of the fields comprise of 30 text fields, 3...
5
2216
by: Steve Strik | last post by:
My Problem: I have created a database here at work that is exhibiting some very strange behaviour. Essentially the database is structured in a manner where one table is a master record table (tbl_MainRegister) providing a unique identifier for documents and a means for identifying the docuement type. There are then 4 additional tables...
2
1781
by: DataB | last post by:
Hi everyone! I have a forms problem. Bakground: I have created a number of tables. Of these, I have a main parent table (Personal Details) and a number of other child tables (Tax file No., VISA card no.), etc...... All tables have a primary key of "Name", all child tables forming
8
3980
by: doomx | last post by:
I'm using SQL scripts to create and alter tables in my DB I want to know if it's possible to fill the description(like in the Create table UI) using these scripts. EX: CREATE TABLE( Pk_myPrimaryKey INTEGER CONSTRAINT pk PRIMARY KEY DESCRIPTION 'This is the primary key of the table',
2
2362
by: Adnan | last post by:
Hey Ppl, I'm developing an Online Auction Site using ASP.net and am experiencing a problem with Transactions in ADO.Net. When beginTrasaction() function is invoked from a specific connection, and insertions are made into a table, no other connection can access the table, until the transaction is committed. I've set the Isoloation level...
2
1593
by: Hefestus | last post by:
Hey everyone, I have never really used access before and i now find myself knee deep in it trying to solve a problem. Hopefully someone has an idea. Anything would be great. Here's the problem: I am working on a network invrntory solution for my department. We're using a great little utility to audit each machine on the network, storing...
11
4475
by: Alan Mailer | last post by:
A project I'm working on is going to use VB6 as a front end. The back end is going to be pre-existing MS Access 2002 database tables which already have records in them *but do not have any AutoNumber* fields in them. Correct me if I'm wrong, but I'm assuming this means that I cannot now alter these existing Access tables and change their...
5
11644
by: starke1120 | last post by:
Im creating a check in – check out database for RF guns. I have a table that contains models. ID (primary key) Model A table that contains Gun Details ID (primary key) Model_id Gun_Number (assigned gun number by operations)
7
13157
by: kpresidente | last post by:
Hello all, I'm trying to transfer the value of a control on an Access form to an Excel worksheet using VBA. The Access form is a single form with all the controls disabled, so that data is "read only." I have a button on the form which opens the Excel worksheet, which then basically serves as the input form for the Access database. What I...
0
7719
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8044
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
6450
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5616
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5299
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3749
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2238
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1335
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1055
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.