Database Row Locking

My C# program is doing the following (pseudo code):

START TRANSACTION READ COMMITTED
Select isOriginal, * from myTable where tnr = x;
//Record found?
Yes:
  //isOriginal? 
  Yes:
    update myTable set is_active = 0 where tnr = x;
  No:
    delete from myTable where tnr = x;
  //Do some simple logic on the values
  Insert into myTable (newvalues)
No:
  return record_not_found;
END TRANSACTION

However, when I start two instances of my program and both edit the same record at the same time two records are inserted as they both find the record in the select query.

What should happen is that the first transaction finds the record and inserts a new row while the second transacti开发者_JAVA技巧on returns a record not found.

How can I fix this? Put my transaction to serializable? Check the return value of update/delete? What's the best way?

Edit:

It should work on Sybase, Oracle & SQL Server.

---

without knowing what db you are using you could setup a lock field in the db.

where each concurrent thread has a pid or thread id or at least a unique timestamp just do a

update myTable set lock = <pid> where pid = null limit 1;

select isOriginal, * from myTable where lock = <pid>

---

If you are using MS SQL You will need to look at NOLOCK and ROWLOCK NOLOCK tells SQL Server to ignore any type of locks and read directly from the actual tables. The pro is it has great performance, the con is in this way you are circumventing a locked system. ROWLOCK on the other hand asks SQL Server to use row-level locks. Performance does hinder with rowlock so you need to determine if you need to lock on UPDATES / DELETES

In your case SELECT isOriginal, * FROM myTable WITH (NOLOCK) WHERE tnr=x

Then UPDATE myTable WITH (ROWLOCK) SET is_active=0 WHERE tnr=x