2

I am generating userId manually i.e. previous userId+1.

but if two or more users reading previous userId and inserting new record.

So,there is a possibility that, any two or more users inserting record with same userId

Lajos Veres
  • 13,595
  • 7
  • 43
  • 56
Chaitanya
  • 81
  • 1
  • 4

2 Answers2

2

You can put reading the previous userid and inserting the record in the same transaction.

The transaction must be set to use the proper Isoltaion level: 'Serializable' or 'Repeatable reads' at least.

Following this approach, the increment of the userid will fall after the begining of the transaction and before the commit.

The RDBMS will take care of race condition.

Andrea Colleoni
  • 5,919
  • 3
  • 30
  • 49
  • This is enough if you use 'Serializable' or 'Repeatable reads' isolation level; not if you use 'Read committed' or 'Read uncommitted'. The OP didn't specify the engine, so it's not possible to suggest how to accomplish his task. But your comment is useful. I'll update the answer. – Andrea Colleoni Jul 16 '14 at 12:25
  • Is there any rdbms where these are the defaults? I mean changing serialisation level for a so easy task seems to be a bit overkill. (And I think 'repatable reads' isn't enough, but I am not 100% sure...) – Lajos Veres Jul 16 '14 at 13:09
  • In MS Sql Server, for example, Read committed is the default (http://stackoverflow.com/questions/10003026/what-is-the-default-transaction-isolation-level-for-sql-server-with-ado-net). It can moreover be set per transaction without changing the default (http://msdn.microsoft.com/library/ms173763.aspx). According to MSSQL docs Repeatable read should be enough. – Andrea Colleoni Jul 16 '14 at 14:14
  • Here http://msdn.microsoft.com/en-gb/library/ms173763.aspx They say: READ COMMITTED ... This option is the SQL Server default. ...Otherwise you are right. Repatable reads should be fine, and per transaction it is less painful. AFAIK in MS-SQL world `identity` is the "standard" solution to solve this task. – Lajos Veres Jul 16 '14 at 14:23
  • I agree. Identity should do the task. – Andrea Colleoni Jul 16 '14 at 14:29
0

You should queue somehow your processes and make sure only one is processed in a given time. The easiest way is to lock something before the select previous userid query, and release the lock after you inserted already the record.

Otherwise this is not a good practice. RDBMS systems have better solutions for this. Search for sequence, identity or auto_increment in your RDBMS's documentation.

Lajos Veres
  • 13,595
  • 7
  • 43
  • 56