Printable View
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...
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...
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...
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.
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...
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
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 ?
yes, you should set the new column as your primary key.
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.