1

first off, i am new to programming.

i am trying making a program that uses many tasks. These tasks will insert data into multiple relational tables. (i am using sql server)

But, i think it is possible that there might be concurrency issues like, when a task inserts data into first table and uses last inserted identity id, in the other relational table, meanwhile the other task could insert a data into first table and change the last inserted identity id, so in that case, as the last inserted identity id changed, first task would use the wrong(changed by second task) last inserted identity id, i guess.

i thought to use serializable lock that locks the whole transaction, i gues that would work but this can also affect performance i guess.

So, what should i do without hurting the performance?

cinfis
  • 345
  • 4
  • 14

2 Answers2

0

As a programmer, mostly what you need to worry about is getting your changes in a correctly scoped TRANSACTION. As long as you do that, you should be fine. If your DB Admin has changed the default isolation level there is a chance of problems, but that's a very rare case.

The basic format is 

BEGIN TRANSACTION AddUser

INSERT INTO Table1 ...

INSERT INTO Table2 ...

...
COMMIT TRANSACTION AddUser

Where AddUser is some name you come up with

If you do that, the DBMS will take care of correctly locking tables tables in the most efficient way that assures correct results. Well, correct from a concurrency standpoint, your logic may still be off :-)

EDIT: NOTE: A mistake new programmers sometimes make is leaving a TRANSACTION open across operations that can cause interruptions. Get all of your input together before you open the transaction, don't BEGIN, write 1 table, ask the user what to do, then write the second table and commit. That locks your tables if the user goers to lunch or the program crashes!

Robert Sheahan
  • 2,100
  • 1
  • 10
  • 12
0

The IDENTITY value assigned by an INSERT statement is available with SCOPE_IDENTITY(). In the case of a multi-row INSERT, an OUTPUT clause may be used to return the IDENTITY value for each inserted row. These are scoped per connection so the IDENTITY value(s) returned by the first insert of each task will be different in the default READ_COMMITTED isolation level, and can be used for the subsequent insert of related rows into the subsequent tables.

Dan Guzman
  • 43,250
  • 3
  • 46
  • 71