Help needed for data insertion by multiple user !!!

[mihirpatel83]

I hv a table on server where in i have a field say TNO varchar2(10) which is auto-number with data T001, T002, T003 and so on...

Now problem is when 2 user adds data 1 can insert and other gets error may b coz both read same data nd try 2 insert same record...How can i achieve this ?? is there any lock or smthing tat can help me or any kind of help will be appreciated.....

Thanks in advance...

[motil]

What the error that you get? there shouldn't problem if two users try to add data at the same time, the later will just wait in line.

on a side note, i might be wrong but varchar data type can not be signed as auto-number you need to use int for example...

[mihirpatel83]

Varchar cannot kept as auto number that i know tats why i have kept a code to increment it manually with code......just before insert statement and finding what number should be added.

But the problem is when i add the record the other gets the primary key constraint error......so wat would b happening is me and my colleague both are readin data bt if i add a record before him , he get primary key error ie recrod already exist or viceversa...

[motil]

why wouldn't you use the built-in primary key? this is the source of your problems, if you must use the T001, T002 ... thingy then create another column named "id" or "row_id" for example with INT as data type set it as the primary key and auto-number. this will solve your problems.

[mihirpatel83]

Hmmmm....Thats a one way of solution but I am looking for some other solution using locks on table...
In one scenario, If i have read data from table meanwhile at the same time other deletes the record then in tat case there would be problems isn't it?? cause there would 1 less row in real than what i have read...

[motil]

not to using primary key with auto-number equal to bad idea and bad practice, all of your tables should contain primary key with auto-number, even if you decide to use table locking you still need your table to hold a primary id/autonumber column.

in multi-users environment bad implement of locking tables can result in unneeded noticeable slowdown for your users.

you should really go for the PK solution

[mihirpatel83]

allrite......so if TNO varchar2(10) is my primary key field, you suggesting me to keep an extra field of autonumber and use both as composite key right ?

[motil]

yes, you should set the new column as your primary key.

[gb1983]

Adding an extra column is the best option. Two users trying to insert at the same time will cause a deadlock which will in turn kill one of the proccesses, meaning that one of the inserts will fail. The only disadvantage of adding a new column is that it will use more space, so if you use this option, try not to use a GUID type field unless absolutley neccessary. An autonumber column is your best bet.