Re: SQL Query

Anirudh Chitnis via DBMonster.com wrote: > Hi, > > I have a insert query which has got select query inside. Eg: > INSERT INTO table_1 (col_1, col_2, col_3, col_4) VALUES (13,'A',0,1 || (> SELECT SUBSTRING( NVL(MAX ( col_4 ), 10 ) FROM 2 ) FROM table_1 WHERE col_1 > = 13 AND col_2 ='A' ) + 1 ) > > The select part in the query gives a maximum number for the combination of > col_1 & col_2. i.e. col_4 is a running number(serial) depending on col_1 & > col_2. > > My question is when simultaneous users fire this query with the same values > for col_1 & col_2, how would the engine behave ? Will I get unique number for > col_4 ? I am using IDS 9.30. > > I have done following testings and did not find any issues :- > - Run two queries in batch with "Commited Read" transaction isolation. A > unique no. is generated for col_4 in both cases. > > Any thoughts on this ? The vast majority of the time the above will work. However, sometimes you may not get unique values for col_4 if the script is run twice at exactly the same time. To prevent this problem from occurring you will need to lock the table during the insert. This works assuming you have a logged database: BEGIN WORK; LOCK TABLE table_1 IN EXCLUSIVE MODE; INSERT INTO.... COMMIT WORK; This will guarantee that col_4 is always unique. However of course you must be in a position to lock the table exclusively (i.e. no other locks of any kind must be on the table). Ben.