|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
concurrency in stored procI am trying add an item to a table that does not allow duplicates but if the
item already exists I want to return its id. I need an SQL equivalent to a critical section but I am not sure how its done. It would like something like the below code. --Begin Critical Section SELECT @id = id from domains where domains.name = @domain if @id is null begin INSERT Domains(name) SELECT @domain; SELECT @domain_id = SCOPE_IDENTITY(); end -- End critical section Thanks for any suggestions. Here you go ...
SELECT @id = id from domains where domains.name = @domain if @id is null begin INSERT Domains(name) Values( @domain) SET @domain_id = @@IDENTITY end Actually, scope_identity() is fine, as long as there are no triggers on the
table which would insert rows into other tables. @@identity is IMHO the worst possible way. ML So I never have to worry about two threads calling the select part of the
proc at once and both getting Null? Then both call the insert forcing one to get an error because the domain existed. Thanks Show quote "SQL novice" <bal***@gmail.com> wrote in message news:1131719749.241699.198710@g44g2000cwa.googlegroups.com... > Here you go ... > > SELECT @id = id from domains where domains.name = @domain > if @id is null > begin > INSERT Domains(name) Values( @domain) > SET @domain_id = @@IDENTITY > end > To answer that we'll have to see more of the procedure. Please post entire
procedure. ML That is a good question. I would like to know the answer on that as well.
Naturally, you want to do a Monitor type operation, but how does that translate into sql? Wonder if you could use sql clr proc and wrap a Monitor around the select. That is a brute force way, but there must a sql answer with some kind of row locking semantics. -- Show quoteWilliam Stacey [MVP] "Bob" <msg***@hotmail.com> wrote in message news:uRqxbGt5FHA.3984@TK2MSFTNGP10.phx.gbl... > So I never have to worry about two threads calling the select part of the > proc at once and > both getting Null? Then both call the insert forcing one to get an > error because the domain existed. > > Thanks > > > "SQL novice" <bal***@gmail.com> wrote in message > news:1131719749.241699.198710@g44g2000cwa.googlegroups.com... >> Here you go ... >> >> SELECT @id = id from domains where domains.name = @domain >> if @id is null >> begin >> INSERT Domains(name) Values( @domain) >> SET @domain_id = @@IDENTITY >> end >> > > OR if there was a way to catch the error and then do a select again that
would work. i.e. SELECT @id = id from domains where domains.name = @domain if @id is null begin try INSERT Domains(name) Values( @domain) SET @domain_id = @@IDENTITY catch(DuplicateRecordErrorr) SELECT @id = id from domains where domains.name = @domain end catch end Show quote "William Stacey [MVP]" <william.sta***@gmail.com> wrote in message news:e4Wcfft5FHA.3136@TK2MSFTNGP09.phx.gbl... > That is a good question. I would like to know the answer on that as well. > Naturally, you want to do a Monitor type operation, but how does that > translate into sql? Wonder if you could use sql clr proc and wrap a > Monitor around the select. That is a brute force way, but there must a > sql answer with some kind of row locking semantics. > > -- > William Stacey [MVP] > > "Bob" <msg***@hotmail.com> wrote in message > news:uRqxbGt5FHA.3984@TK2MSFTNGP10.phx.gbl... >> So I never have to worry about two threads calling the select part of the >> proc at once and >> both getting Null? Then both call the insert forcing one to get an >> error because the domain existed. >> >> Thanks >> >> >> "SQL novice" <bal***@gmail.com> wrote in message >> news:1131719749.241699.198710@g44g2000cwa.googlegroups.com... >>> Here you go ... >>> >>> SELECT @id = id from domains where domains.name = @domain >>> if @id is null >>> begin >>> INSERT Domains(name) Values( @domain) >>> SET @domain_id = @@IDENTITY >>> end >>> >> >> > > How about this:
select @id = <identity_column> from domains where (name = @domain) if (@id is null) begin insert ... set @id = identity_scope() end Or you can try by using TRY...CATCH on SQL 2005. ML I don't think that is thread safe. Multiple threads could end up in the
insert block. -- Show quoteWilliam Stacey [MVP] "ML" <M*@discussions.microsoft.com> wrote in message news:A20D8A8E-4054-476E-9B77-0EC1C96202C7@microsoft.com... > How about this: > > select @id > = <identity_column> > from domains > where (name = @domain) > > if (@id is null) > begin > insert ... > set @id = identity_scope() > end > > > Or you can try by using TRY...CATCH on SQL 2005. > > > ML Say two thread enter at ~same time (say MP machine, but same issue with 1
cpu) and not domain name added yet. Both threads get null id as none inserted yet. Both threads move passed "if (id is null)" test. Now one thread will insert and return and the other will fail on non-unique index. And there is no error handling to catch the error on the second thread so it just throws exception back to caller. -- Show quoteWilliam Stacey [MVP] "ML" <M*@discussions.microsoft.com> wrote in message news:ED328E47-9ABB-4A51-8EA0-CC38470DF2C7@microsoft.com... > How? > > > ML Let me qualify that. If procs are not serialized by the system, this is not
thread safe. This begs a question about procs and atomicness. Can procs be entered by more then one thread at same time? I am guessing yes. If you could "sync" the proc with some attribute, then this would appear to work correctly. Any good whitepaper on locks and threads in context of procs and updates/deletes, etc. -- Show quoteWilliam Stacey [MVP] "William Stacey [MVP]" <william.sta***@gmail.com> wrote in message news:uLViQ4u5FHA.1032@TK2MSFTNGP11.phx.gbl... > Say two thread enter at ~same time (say MP machine, but same issue with 1 > cpu) and not domain name added yet. > Both threads get null id as none inserted yet. Both threads move passed > "if (id is null)" test. Now one thread will insert and return and the > other will fail on non-unique index. And there is no error handling to > catch the error on the second thread so it just throws exception back to > caller. > > -- > William Stacey [MVP] > > "ML" <M*@discussions.microsoft.com> wrote in message > news:ED328E47-9ABB-4A51-8EA0-CC38470DF2C7@microsoft.com... >> How? >> >> >> ML > > What second thread? You're talking about two users, two sessions. Threads are
irrelevant here. In SQL there is no "out-of-the" box solution to this problem - it must be handled programmatically. SQL2005 introduces TRY...CATCH exception handling, and ever since rowversion was introduced, concurrency violations can be prevented quite easily, as long as this question is taken under consideration at design time. Of course some situations cannot be handled on server alone - they are left to the client application. As far as threads go, each session is isolated from any other session, regardless of the number of threads. ML > What second thread? You're talking about two users, two sessions. The threads running sproc. If two users execute the same sproc and around the same time, they may be run on seperate threads - no? > Threads are irrelevant here. How so?> In SQL there is no "out-of-the" box solution to this problem That is what we are taking about.> - it must be handled programmatically. > As far as threads go, each session is isolated from any other session, Hence the same problem we talking about AFAICT.> regardless of the number of threads. -- William Stacey [MVP] On Fri, 11 Nov 2005 13:30:55 -0500, William Stacey [MVP] wrote:
>Say two thread enter at ~same time (say MP machine, but same issue with 1 Hi William,>cpu) and not domain name added yet. >Both threads get null id as none inserted yet. Both threads move passed "if >(id is null)" test. Now one thread will insert and return and the other >will fail on non-unique index. And there is no error handling to catch the >error on the second thread so it just throws exception back to caller. This can be prevented by setting the appropriate isolation level. If you set the transaction isolation level to "SERIALIZABLE" (or add a SERIALIZABLE locking hint to the SELECT statement), the first attempt to read the row will issue a range lock that prevents any insertions with the same domain name; this would cause the second connection to be blockeed until the first one finishes and releases it's locks. Or, if you prefer not to mess with the isolation level (it might have an adverse effect on the performance, after all), reverse the logic a bit: -- Step 1: insert if it doesn't exist yet INSERT Domains (Name) SELECT @Domain WHERE NOT EXISTS (SELECT * FROM Domains WHERE Name = @Domain) -- Step 2: it's always there now - retrieve ID value SET @id = (SELECT id FROM Domains WHERE Name = @Domain) Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) > This can be prevented by setting the appropriate isolation level. If you Thanks Hugo. That was helpful.> set the transaction isolation level to "SERIALIZABLE" (or add a > SERIALIZABLE locking hint to the SELECT statement), the first attempt to > read the row will issue a range lock that prevents any insertions with > the same domain name; this would cause the second connection to be > blockeed until the first one finishes and releases it's locks. Show quote > Or, if you prefer not to mess with the isolation level (it might have an That is helpful also. Is Step 1 gaureenteed to be atomic? If so, this > adverse effect on the performance, after all), reverse the logic a bit: > > -- Step 1: insert if it doesn't exist yet > INSERT Domains (Name) > SELECT @Domain > WHERE NOT EXISTS (SELECT * > FROM Domains > WHERE Name = @Domain) > -- Step 2: it's always there now - retrieve ID value > SET @id = (SELECT id > FROM Domains > WHERE Name = @Domain) > would appear to be the way to go. Far from a DB expert, but it would appear there might be an issue between step1 and step2 if another user happened to delete the record between step1 and step2. for this thread. In the c# world, we could remove all doubt by placing a lock() {} around the whole thing. Not sure if above handles all cases or not. Cheers. -- William Stacey [MVP] -- William Stacey [MVP] On Fri, 11 Nov 2005 18:57:01 -0500, William Stacey [MVP] wrote:
Show quote >> This can be prevented by setting the appropriate isolation level. If you Hi William,>> set the transaction isolation level to "SERIALIZABLE" (or add a >> SERIALIZABLE locking hint to the SELECT statement), the first attempt to >> read the row will issue a range lock that prevents any insertions with >> the same domain name; this would cause the second connection to be >> blockeed until the first one finishes and releases it's locks. > >Thanks Hugo. That was helpful. > >> Or, if you prefer not to mess with the isolation level (it might have an >> adverse effect on the performance, after all), reverse the logic a bit: >> >> -- Step 1: insert if it doesn't exist yet >> INSERT Domains (Name) >> SELECT @Domain >> WHERE NOT EXISTS (SELECT * >> FROM Domains >> WHERE Name = @Domain) >> -- Step 2: it's always there now - retrieve ID value >> SET @id = (SELECT id >> FROM Domains >> WHERE Name = @Domain) >> > >That is helpful also. Is Step 1 gaureenteed to be atomic? AFAIK, all DML statements are guaranteed to be atomic in SQL Server. I'm not sure how exactly the locking is handled internally. But I've never heard or read that this would result in duplicate insertions. And though I see some (theoretic) possibility of a deadlock scenario here, I haven't seen those reported either. > If so, this Yes, that possibility still exists if the default transaction isolation>would appear to be the way to go. Far from a DB expert, but it would appear >there might be an issue between step1 and step2 if another user happened to >delete the record between step1 and step2. for this thread. level (read committed) is used. I concentrated on the scenario of two simulteneous execution of the same procedure, I had not yet pondered other procs being simultaneously executed. This could also be handled by increasong the isolation level, but in this case there's no need to go as far as SERIALIZABLE. Using REPEATABLE READ (or the locking hint REPEATABLEREAD) is sufficient. That means that there won't be a range lock, but the shared lock on the row that is read if the NOT EXISTS is false will be kept until the transaction is finished. That shared lock will probably not block other connections from executing the same query (since they also need a shared lock only for the EXISTS check) - but it will prevent any DELETE or UPDATE statement from messing with the row. Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) > This can be prevented by setting the appropriate isolation level. If you Setting the isolation level is not enough. You must enclose the SELECT and > set the transaction isolation level to "SERIALIZABLE" the INSERT with a transaction. It does no good to prevent changes during the SELECT, but to allow changes between the SELECT and the INSERT--which is indeed possible in a concurrent environment. In addition, to prevent deadlocks you must also specify WITH(UPDLOCK) or WITH(XLOCK) on the SELECT statement. Show quote "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:0p9an11fs7mb3dn1lobj7rube3blv7jclt@4ax.com... > On Fri, 11 Nov 2005 13:30:55 -0500, William Stacey [MVP] wrote: > >>Say two thread enter at ~same time (say MP machine, but same issue with 1 >>cpu) and not domain name added yet. >>Both threads get null id as none inserted yet. Both threads move passed >>"if >>(id is null)" test. Now one thread will insert and return and the other >>will fail on non-unique index. And there is no error handling to catch >>the >>error on the second thread so it just throws exception back to caller. > > Hi William, > > This can be prevented by setting the appropriate isolation level. If you > set the transaction isolation level to "SERIALIZABLE" (or add a > SERIALIZABLE locking hint to the SELECT statement), the first attempt to > read the row will issue a range lock that prevents any insertions with > the same domain name; this would cause the second connection to be > blockeed until the first one finishes and releases it's locks. > > Or, if you prefer not to mess with the isolation level (it might have an > adverse effect on the performance, after all), reverse the logic a bit: > > -- Step 1: insert if it doesn't exist yet > INSERT Domains (Name) > SELECT @Domain > WHERE NOT EXISTS (SELECT * > FROM Domains > WHERE Name = @Domain) > -- Step 2: it's always there now - retrieve ID value > SET @id = (SELECT id > FROM Domains > WHERE Name = @Domain) > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address) I think we have a winner. Care to codify small example of that for the rest
of us :-) ? Then maybe others can poke at it until all agree. Thanks Brian. -- Show quoteWilliam Stacey [MVP] "Brian Selzer" <br***@selzer-software.com> wrote in message news:ep1xfBy5FHA.252@TK2MSFTNGP15.phx.gbl... >> This can be prevented by setting the appropriate isolation level. If you >> set the transaction isolation level to "SERIALIZABLE" > > Setting the isolation level is not enough. You must enclose the SELECT > and the INSERT with a transaction. It does no good to prevent changes > during the SELECT, but to allow changes between the SELECT and the > INSERT--which is indeed possible in a concurrent environment. In > addition, to prevent deadlocks you must also specify WITH(UPDLOCK) or > WITH(XLOCK) on the SELECT statement. > > "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message > news:0p9an11fs7mb3dn1lobj7rube3blv7jclt@4ax.com... >> On Fri, 11 Nov 2005 13:30:55 -0500, William Stacey [MVP] wrote: >> >>>Say two thread enter at ~same time (say MP machine, but same issue with 1 >>>cpu) and not domain name added yet. >>>Both threads get null id as none inserted yet. Both threads move passed >>>"if >>>(id is null)" test. Now one thread will insert and return and the other >>>will fail on non-unique index. And there is no error handling to catch >>>the >>>error on the second thread so it just throws exception back to caller. >> >> Hi William, >> >> This can be prevented by setting the appropriate isolation level. If you >> set the transaction isolation level to "SERIALIZABLE" (or add a >> SERIALIZABLE locking hint to the SELECT statement), the first attempt to >> read the row will issue a range lock that prevents any insertions with >> the same domain name; this would cause the second connection to be >> blockeed until the first one finishes and releases it's locks. >> >> Or, if you prefer not to mess with the isolation level (it might have an >> adverse effect on the performance, after all), reverse the logic a bit: >> >> -- Step 1: insert if it doesn't exist yet >> INSERT Domains (Name) >> SELECT @Domain >> WHERE NOT EXISTS (SELECT * >> FROM Domains >> WHERE Name = @Domain) >> -- Step 2: it's always there now - retrieve ID value >> SET @id = (SELECT id >> FROM Domains >> WHERE Name = @Domain) >> >> Best, Hugo >> -- >> >> (Remove _NO_ and _SPAM_ to get my e-mail address) > > --Begin Critical Section
DECLARE @_ROWCOUNT INT, @_ERROR INT BEGIN TRANSACTION SELECT @id = id from domains WITH(UPDLOCK, HOLDLOCK) where domains.name = @domain SELECT @_ROWCOUNT = @@ROWCOUNT, @_ERROR = @@ERROR IF @_ERROR != 0 GOTO ERROR IF @_ROWCOUNT = 0 BEGIN -- if you're inserting one row and not selecting from a table, then use the VALUES clause -- that way you can differentiate at a glance between single-row INSERTs and set-based INSERTs INSERT domains (name) VALUES (@domain) SELECT @_ROWCOUNT = @@ROWCOUNT, @_ERROR = @@ERROR IF @_ERROR != 0 OR @_ROWCOUNT = 0 GOTO ERROR -- use SET @id = @@IDENTITY if there is an INSTEAD OF INSERT trigger on domains -- or SELECT @id = id where domains.name = @domain SET @id = SCOPE_IDENTITY() END COMMIT TRANSACTION -- End critical section RETURN 0 ERROR: IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION IF @RC != 0 RETURN @RC IF @_ERROR != 0 RETURN @_ERROR RETURN -1 END Show quote "William Stacey [MVP]" <william.sta***@gmail.com> wrote in message news:uNLVfky5FHA.3976@TK2MSFTNGP15.phx.gbl... >I think we have a winner. Care to codify small example of that for the >rest of us :-) ? Then maybe others can poke at it until all agree. Thanks >Brian. > > -- > William Stacey [MVP] > > "Brian Selzer" <br***@selzer-software.com> wrote in message > news:ep1xfBy5FHA.252@TK2MSFTNGP15.phx.gbl... >>> This can be prevented by setting the appropriate isolation level. If you >>> set the transaction isolation level to "SERIALIZABLE" >> >> Setting the isolation level is not enough. You must enclose the SELECT >> and the INSERT with a transaction. It does no good to prevent changes >> during the SELECT, but to allow changes between the SELECT and the >> INSERT--which is indeed possible in a concurrent environment. In >> addition, to prevent deadlocks you must also specify WITH(UPDLOCK) or >> WITH(XLOCK) on the SELECT statement. >> >> "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message >> news:0p9an11fs7mb3dn1lobj7rube3blv7jclt@4ax.com... >>> On Fri, 11 Nov 2005 13:30:55 -0500, William Stacey [MVP] wrote: >>> >>>>Say two thread enter at ~same time (say MP machine, but same issue with >>>>1 >>>>cpu) and not domain name added yet. >>>>Both threads get null id as none inserted yet. Both threads move passed >>>>"if >>>>(id is null)" test. Now one thread will insert and return and the other >>>>will fail on non-unique index. And there is no error handling to catch >>>>the >>>>error on the second thread so it just throws exception back to caller. >>> >>> Hi William, >>> >>> This can be prevented by setting the appropriate isolation level. If you >>> set the transaction isolation level to "SERIALIZABLE" (or add a >>> SERIALIZABLE locking hint to the SELECT statement), the first attempt to >>> read the row will issue a range lock that prevents any insertions with >>> the same domain name; this would cause the second connection to be >>> blockeed until the first one finishes and releases it's locks. >>> >>> Or, if you prefer not to mess with the isolation level (it might have an >>> adverse effect on the performance, after all), reverse the logic a bit: >>> >>> -- Step 1: insert if it doesn't exist yet >>> INSERT Domains (Name) >>> SELECT @Domain >>> WHERE NOT EXISTS (SELECT * >>> FROM Domains >>> WHERE Name = @Domain) >>> -- Step 2: it's always there now - retrieve ID value >>> SET @id = (SELECT id >>> FROM Domains >>> WHERE Name = @Domain) >>> >>> Best, Hugo >>> -- >>> >>> (Remove _NO_ and _SPAM_ to get my e-mail address) >> >> > > On Fri, 11 Nov 2005 19:31:06 -0500, Brian Selzer wrote:
>> This can be prevented by setting the appropriate isolation level. If you Hi Brian,>> set the transaction isolation level to "SERIALIZABLE" > >Setting the isolation level is not enough. You must enclose the SELECT and >the INSERT with a transaction. Yes, of course - I assumed that the procedure was part of a transaction, but I should have pointed that out explicitly. Thanks for the addition!! Best, Hugo -- (Remove _NO_ and _SPAM_ to get my e-mail address) So is there agreement that Hugos code with addition of transaction will work
like below, or do you still need the UPDLOCK, HOLDLOCK stuff? Alter PROCEDURE AddDomainName @domainname varchar(255), @id int OUT AS BEGIN TRANSACTION -- Step 1: Add domain name if it does not exist. INSERT Domains (DomainName) SELECT @domainname WHERE NOT EXISTS (SELECT * FROM Domains WHERE DomainName = @domainname) -- Step 2: it's always there now - retrieve ID. SET @id = (SELECT ID FROM Domains WHERE DomainName = @domainname) COMMIT TRANSACTION go -- Show quoteWilliam Stacey [MVP] "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message news:g28fn1h2m4m7p75b306phdt8nm4e67gki5@4ax.com... > On Fri, 11 Nov 2005 19:31:06 -0500, Brian Selzer wrote: > >>> This can be prevented by setting the appropriate isolation level. If you >>> set the transaction isolation level to "SERIALIZABLE" >> >>Setting the isolation level is not enough. You must enclose the SELECT >>and >>the INSERT with a transaction. > > Hi Brian, > > Yes, of course - I assumed that the procedure was part of a transaction, > but I should have pointed that out explicitly. > > Thanks for the addition!! > > Best, Hugo > -- > > (Remove _NO_ and _SPAM_ to get my e-mail address) With Hugo's code, I don't think you need a transaction, but I think you
still need the UPDLOCK, HOLDLOCK stuff. A transaction is automagically started for the INSERT statement which encompasses the SELECT and in particular the EXISTS clause. But the EXISTS clause is executed before locks are obtained for the INSERT. Without HOLDLOCK, in a highly concurrent environment another transaction could insert or change a row between the time that the EXISTS clause executes and the time that the exclusive lock is obtained for the INSERT, causing a constraint violation. Without UPDLOCK, two similar transactions executing simultaneously could obtain shared range-locks on Domains (from the EXISTS clause) and then deadlock. Try this: INSERT Domains (DomainName) SELECT @domainname WHERE NOT EXISTS (SELECT * FROM Domains WITH(UPDLOCK, HOLDLOCK) WHERE DomainName = @domainname) IF @@ERROR != 0 OR @@ROWCOUNT = 0 GOTO ERROR SET @id = SCOPE_IDENTITY() Show quote "William Stacey [MVP]" <william.sta***@gmail.com> wrote in message news:eLDxc4nEGHA.4036@TK2MSFTNGP09.phx.gbl... > So is there agreement that Hugos code with addition of transaction will > work like below, > or do you still need the UPDLOCK, HOLDLOCK stuff? > > Alter PROCEDURE AddDomainName > @domainname varchar(255), > @id int OUT > AS > BEGIN TRANSACTION > -- Step 1: Add domain name if it does not exist. > INSERT Domains (DomainName) > SELECT @domainname > WHERE NOT EXISTS (SELECT * > FROM Domains > WHERE DomainName = @domainname) > > -- Step 2: it's always there now - retrieve ID. > SET @id = (SELECT ID > FROM Domains > WHERE DomainName = @domainname) > COMMIT TRANSACTION > go > > -- > William Stacey [MVP] > > "Hugo Kornelis" <hugo@pe_NO_rFact.in_SPAM_fo> wrote in message > news:g28fn1h2m4m7p75b306phdt8nm4e67gki5@4ax.com... >> On Fri, 11 Nov 2005 19:31:06 -0500, Brian Selzer wrote: >> >>>> This can be prevented by setting the appropriate isolation level. If >>>> you >>>> set the transaction isolation level to "SERIALIZABLE" >>> >>>Setting the isolation level is not enough. You must enclose the SELECT >>>and >>>the INSERT with a transaction. >> >> Hi Brian, >> >> Yes, of course - I assumed that the procedure was part of a transaction, >> but I should have pointed that out explicitly. >> >> Thanks for the addition!! >> >> Best, Hugo >> -- >> >> (Remove _NO_ and _SPAM_ to get my e-mail address) > > > INSERT Domains (DomainName) Thanks again. I saw the following in the Insert docs, not sure if this > SELECT @domainname > WHERE NOT EXISTS (SELECT * > FROM Domains WITH(UPDLOCK, HOLDLOCK) > WHERE DomainName = @domainname) > IF @@ERROR != 0 OR @@ROWCOUNT = 0 GOTO ERROR > SET @id = SCOPE_IDENTITY() applies here, but it sounds like it may: "The ability to specify the HOLDLOCK, SERIALIZABLE, READCOMMITTED, REPEATABLEREAD, or UPDLOCK hints on tables that are targets of INSERT statements will be removed in a future version of SQL Server. These hints do not affect the performance of INSERT statements. Avoid using them in new development work, and plan to modify applications that currently use them." What do you think? Cheers. -- wjs Nope, it doesn't apply. It would apply were the statement like this:
INSERT Domains WITH(HOLDLOCK) (DomainName) SELECT @domainname WHERE NOT EXISTS (SELECT * FROM Domains WITH(UPDLOCK, HOLDLOCK) WHERE DomainName = @domainname) IF @@ERROR != 0 OR @@ROWCOUNT = 0 GOTO ERROR SET @id = SCOPE_IDENTITY() Locks on tables referenced within the EXISTS clause above are handled separately from the locks on the target table. It's just coincidental that the target table and the table in the EXISTS clause happen to be the same. Even if you had enclosed the SELECT clause in a stored procedure and issued INSERT...EXEC, the locks would be obtained in the same order. The statement below refers to the target of the INSERT--the table that is about to receive data. Show quote "William Stacey [MVP]" <william.sta***@gmail.com> wrote in message news:u9sQgvuEGHA.336@TK2MSFTNGP14.phx.gbl... >> INSERT Domains (DomainName) >> SELECT @domainname >> WHERE NOT EXISTS (SELECT * >> FROM Domains WITH(UPDLOCK, HOLDLOCK) >> WHERE DomainName = @domainname) >> IF @@ERROR != 0 OR @@ROWCOUNT = 0 GOTO ERROR >> SET @id = SCOPE_IDENTITY() > > Thanks again. I saw the following in the Insert docs, not sure if this > applies here, but it sounds like it may: > > "The ability to specify the HOLDLOCK, SERIALIZABLE, READCOMMITTED, > REPEATABLEREAD, or UPDLOCK hints on tables that are targets of INSERT > statements will be removed in a future version of SQL Server. These hints > do not affect the performance of INSERT statements. Avoid using them in > new development work, and plan to modify applications that currently use > them." > > What do you think? Cheers. > -- > wjs > On Fri, 6 Jan 2006 13:06:07 -0500, William Stacey [MVP] wrote:
Show quote >> INSERT Domains (DomainName) Hi William,>> SELECT @domainname >> WHERE NOT EXISTS (SELECT * >> FROM Domains WITH(UPDLOCK, HOLDLOCK) >> WHERE DomainName = @domainname) >> IF @@ERROR != 0 OR @@ROWCOUNT = 0 GOTO ERROR >> SET @id = SCOPE_IDENTITY() > >Thanks again. I saw the following in the Insert docs, not sure if this >applies here, but it sounds like it may: > >"The ability to specify the HOLDLOCK, SERIALIZABLE, READCOMMITTED, >REPEATABLEREAD, or UPDLOCK hints on tables that are targets of INSERT >statements will be removed in a future version of SQL Server. These hints do >not affect the performance of INSERT statements. Avoid using them in new >development work, and plan to modify applications that currently use them." > >What do you think? Cheers. This pertains to locking hints on the INSERT part of the INSERT statement, like this: INSERT Domains WITH (TABLOCK) (DomainName) SELECT .... WHERE .... The hints specified in the above quote are useless since inserted rows will always be locked exclusively until the end of the transaction. -- Hugo Kornelis, SQL Server MVP On Fri, 6 Jan 2006 03:06:06 -0500, Brian Selzer wrote:
>With Hugo's code, I don't think you need a transaction, but I think you Hi Brian,>still need the UPDLOCK, HOLDLOCK stuff. A transaction is automagically >started for the INSERT statement which encompasses the SELECT and in >particular the EXISTS clause. But that implicit transaction does not extend beyond the INSERT statement. This could cause the SELECT statement that follows to fail if the row just inserted gets changed or deleted by another connection between these two steps. (I know, it's very unlikely, but at leastt theoretically impossible - so why not play safe and keep the explicit transaction?) (snip) >Try this: This would set @id to NULL if the WHERE clause in the INSERT statement> > INSERT Domains (DomainName) > SELECT @domainname > WHERE NOT EXISTS (SELECT * > FROM Domains WITH(UPDLOCK, HOLDLOCK) > WHERE DomainName = @domainname) >IF @@ERROR != 0 OR @@ROWCOUNT = 0 GOTO ERROR >SET @id = SCOPE_IDENTITY() evaluates to FALSE, since there would be no rows inserted. (Or, if an other INSERT statement has already been executed in the same scope, the identity value generated for that INSERT statement would be returned!) -- Hugo Kornelis, SQL Server MVP Hi Hugo,
I'm almost positive that the implicit transaction extends to the SELECT clause of the INSERT...SELECT statement. It's difficult to prove this, but I did a profiler trace of SQLTransaction events for a typical INSERT...SELECT statement, and there's only one entry with "Begin" for the EventSubClass, and only one entry for "Commit." In addition, locks were aquired on both of the affected tables--first the object of the SELECT, and then on the target of the INSERT--between the Begin and Commit SQLTransaction events. I was able to make the batch block by holding locks in another Query Analyzer window, so it's clear that the SELECT was operating within the context of a transaction. --Brian Show quote "Hugo Kornelis" <h***@perFact.REMOVETHIS.info> wrote in message news:ql1ur1t0ck46mcqrmi6tsh3ncndh0f20p0@4ax.com... > On Fri, 6 Jan 2006 03:06:06 -0500, Brian Selzer wrote: > >>With Hugo's code, I don't think you need a transaction, but I think you >>still need the UPDLOCK, HOLDLOCK stuff. A transaction is automagically >>started for the INSERT statement which encompasses the SELECT and in >>particular the EXISTS clause. > > Hi Brian, > > But that implicit transaction does not extend beyond the INSERT > statement. This could cause the SELECT statement that follows to fail if > the row just inserted gets changed or deleted by another connection > between these two steps. (I know, it's very unlikely, but at leastt > theoretically impossible - so why not play safe and keep the explicit > transaction?) > > (snip) >>Try this: >> >> INSERT Domains (DomainName) >> SELECT @domainname >> WHERE NOT EXISTS (SELECT * >> FROM Domains WITH(UPDLOCK, HOLDLOCK) >> WHERE DomainName = @domainname) >>IF @@ERROR != 0 OR @@ROWCOUNT = 0 GOTO ERROR >>SET @id = SCOPE_IDENTITY() > > This would set @id to NULL if the WHERE clause in the INSERT statement > evaluates to FALSE, since there would be no rows inserted. (Or, if an > other INSERT statement has already been executed in the same scope, the > identity value generated for that INSERT statement would be returned!) > > -- > Hugo Kornelis, SQL Server MVP On Fri, 6 Jan 2006 21:15:18 -0500, Brian Selzer wrote:
(snip top-post) Show quote >"Hugo Kornelis" <h***@perFact.REMOVETHIS.info> wrote in message (paste top-post)>news:ql1ur1t0ck46mcqrmi6tsh3ncndh0f20p0@4ax.com... >> On Fri, 6 Jan 2006 03:06:06 -0500, Brian Selzer wrote: >> >>>With Hugo's code, I don't think you need a transaction, but I think you >>>still need the UPDLOCK, HOLDLOCK stuff. A transaction is automagically >>>started for the INSERT statement which encompasses the SELECT and in >>>particular the EXISTS clause. >> >> Hi Brian, >> >> But that implicit transaction does not extend beyond the INSERT >> statement. This could cause the SELECT statement that follows to fail if >> the row just inserted gets changed or deleted by another connection >> between these two steps. (I know, it's very unlikely, but at leastt >> theoretically impossible - so why not play safe and keep the explicit >> transaction?) >Hi Hugo, Hi Brian,> >I'm almost positive that the implicit transaction extends to the SELECT >clause of the INSERT...SELECT statement. (snip) And so am I. The SELECT statement that could (theoretically) fail is the second one, the one that gets the identity value of the row that was either just inserted or already existing. Here's the code William referred to, with some comments: Alter PROCEDURE AddDomainName @domainname varchar(255), @id int OUT AS BEGIN TRANSACTION -- Step 1: Add domain name if it does not exist. -- *** No need for a transaction for just this step; -- *** single statements are always ACID. INSERT Domains (DomainName) SELECT @domainname WHERE NOT EXISTS (SELECT * FROM Domains WHERE DomainName = @domainname) -- Step 2: it's always there now - retrieve ID. -- *** Here's the reason why we need a transaction. -- *** Without it, the row that prevented the insert in the first step -- *** might possibly be removed when it's read here. SET @id = (SELECT ID FROM Domains WHERE DomainName = @domainname) -- *** Now it's safe to end the transaction. COMMIT TRANSACTION go -- Hugo Kornelis, SQL Server MVP I must have been half asleep when I read your post. Sorry.
Show quote "Hugo Kornelis" <h***@perFact.REMOVETHIS.info> wrote in message news:6bc0s19ilo0f7da6s166h8ckh7ekha0nn7@4ax.com... > On Fri, 6 Jan 2006 21:15:18 -0500, Brian Selzer wrote: > > (snip top-post) >>"Hugo Kornelis" <h***@perFact.REMOVETHIS.info> wrote in message >>news:ql1ur1t0ck46mcqrmi6tsh3ncndh0f20p0@4ax.com... >>> On Fri, 6 Jan 2006 03:06:06 -0500, Brian Selzer wrote: >>> >>>>With Hugo's code, I don't think you need a transaction, but I think you >>>>still need the UPDLOCK, HOLDLOCK stuff. A transaction is automagically >>>>started for the INSERT statement which encompasses the SELECT and in >>>>particular the EXISTS clause. >>> >>> Hi Brian, >>> >>> But that implicit transaction does not extend beyond the INSERT >>> statement. This could cause the SELECT statement that follows to fail if >>> the row just inserted gets changed or deleted by another connection >>> between these two steps. (I know, it's very unlikely, but at leastt >>> theoretically impossible - so why not play safe and keep the explicit >>> transaction?) > (paste top-post) > >>Hi Hugo, >> >>I'm almost positive that the implicit transaction extends to the SELECT >>clause of the INSERT...SELECT statement. > (snip) > > Hi Brian, > > And so am I. > > The SELECT statement that could (theoretically) fail is the second one, > the one that gets the identity value of the row that was either just > inserted or already existing. Here's the code William referred to, with > some comments: > > > Alter PROCEDURE AddDomainName > @domainname varchar(255), > @id int OUT > AS > BEGIN TRANSACTION > -- Step 1: Add domain name if it does not exist. > -- *** No need for a transaction for just this step; > -- *** single statements are always ACID. > INSERT Domains (DomainName) > SELECT @domainname > WHERE NOT EXISTS (SELECT * > FROM Domains > WHERE DomainName = @domainname) > > -- Step 2: it's always there now - retrieve ID. > -- *** Here's the reason why we need a transaction. > -- *** Without it, the row that prevented the insert in the first step > -- *** might possibly be removed when it's read here. > SET @id = (SELECT ID > FROM Domains > WHERE DomainName = @domainname) > > -- *** Now it's safe to end the transaction. > COMMIT TRANSACTION > go > > -- > Hugo Kornelis, SQL Server MVP (snip)
> This would set @id to NULL if the WHERE clause in the INSERT statement I don't think so. That's why I used,> evaluates to FALSE, since there would be no rows inserted. (Or, if an > other INSERT statement has already been executed in the same scope, the > identity value generated for that INSERT statement would be returned!) IF @@ERROR != 0 OR @@ROWCOUNT = 0 GOTO ERROR Show quote "Hugo Kornelis" <h***@perFact.REMOVETHIS.info> wrote in message news:ql1ur1t0ck46mcqrmi6tsh3ncndh0f20p0@4ax.com... > On Fri, 6 Jan 2006 03:06:06 -0500, Brian Selzer wrote: > >>With Hugo's code, I don't think you need a transaction, but I think you >>still need the UPDLOCK, HOLDLOCK stuff. A transaction is automagically >>started for the INSERT statement which encompasses the SELECT and in >>particular the EXISTS clause. > > Hi Brian, > > But that implicit transaction does not extend beyond the INSERT > statement. This could cause the SELECT statement that follows to fail if > the row just inserted gets changed or deleted by another connection > between these two steps. (I know, it's very unlikely, but at leastt > theoretically impossible - so why not play safe and keep the explicit > transaction?) > > (snip) >>Try this: >> >> INSERT Domains (DomainName) >> SELECT @domainname >> WHERE NOT EXISTS (SELECT * >> FROM Domains WITH(UPDLOCK, HOLDLOCK) >> WHERE DomainName = @domainname) >>IF @@ERROR != 0 OR @@ROWCOUNT = 0 GOTO ERROR >>SET @id = SCOPE_IDENTITY() > > This would set @id to NULL if the WHERE clause in the INSERT statement > evaluates to FALSE, since there would be no rows inserted. (Or, if an > other INSERT statement has already been executed in the same scope, the > identity value generated for that INSERT statement would be returned!) > > -- > Hugo Kornelis, SQL Server MVP On Sat, 7 Jan 2006 20:16:15 -0500, Brian Selzer wrote:
>(snip) Hi Brian,>> This would set @id to NULL if the WHERE clause in the INSERT statement >> evaluates to FALSE, since there would be no rows inserted. (Or, if an >> other INSERT statement has already been executed in the same scope, the >> identity value generated for that INSERT statement would be returned!) > >I don't think so. That's why I used, > >IF @@ERROR != 0 OR @@ROWCOUNT = 0 GOTO ERROR I guess I was half asleep as well. I missed that. Your version is better if the situation that the row already exists shuld not arise, and should therefor be treated as an error condition. My version is better if the "row already exists" case is an expected, non-error case: the routine is designed to add a row if it's not there, then return the identity value of the row that either already existed or was just inserted. I thought the latter was what was asked for in this thread, but I may well be confused with another discussion, here or in another group. I don't feel like going back up this thread to check - if the original poster is still here, (s)he should pick either your or my suggestion, depending on what (s)he really needs. -- Hugo Kornelis, SQL Server MVP But we need to take into account that any insert triggers will fire even if
no rows are inserted. I'm not saying this is a problem. I'm simply pointing it out. :) ML --- http://milambda.blogspot.com/ On Fri, 6 Jan 2006 00:00:25 -0500, William Stacey [MVP] wrote:
Show quote >So is there agreement that Hugos code with addition of transaction will work Hi William,>like below, >or do you still need the UPDLOCK, HOLDLOCK stuff? > >Alter PROCEDURE AddDomainName > @domainname varchar(255), > @id int OUT >AS >BEGIN TRANSACTION >-- Step 1: Add domain name if it does not exist. >INSERT Domains (DomainName) >SELECT @domainname >WHERE NOT EXISTS (SELECT * > FROM Domains > WHERE DomainName = @domainname) > >-- Step 2: it's always there now - retrieve ID. >SET @id = (SELECT ID > FROM Domains > WHERE DomainName = @domainname) >COMMIT TRANSACTION >go I think that you'd need to add a HOLDLOCK hint to Domains in the NOT EXISTS subquery. Basically, there are two scenario's: A. Row is new: 1. Subquery returns no row, NOT EXISTS is true --> Row inserted 2. Because of INSERT, row is exclusive-locked and this lock is held for entire transaction 3. Final SELECT retrieves row just inserted. B. Row already exists: 1. Subquery returns row, NOT EXISTS is false --> No row inserted 2. Row is shared-locked and this lock is by default not held 3. Final SELECT retrieves row just read. In scenario B, there is a small chance that the row is deleted or changed after steps 1 and 2 but before step 3. In that case, the subquery won't return any rows and @id will be set to NULL. Adding the HOLDLOCK hint prevents that - the lock is held during the transaction, so that no other connection can change or delete the row. -- Hugo Kornelis, SQL Server MVP William Stacey [MVP] wrote:
> I don't think that is thread safe. Multiple threads could end up in I'm getting confused about this part of the discussion. I assume we're > the insert block. > not disputing that multiple users can run the same stored procedure at the same time. I also assume we're not disputing that performing a SELECT to see if a row exists in a table, and having that SELECT fail, will not prevent two users from possibly hitting the INSERT command at the same time. I don't think thread-safe is the correct term here. Thread-safe (at least to me) implies the prevention of multiple threads in the same program hitting the same data structure and stepping on each others data. If we're using the term here in the context of preventing two users from trying to insert the same key value at the same time, I don't think this can be done if the key row does not already exist. If it did, you could SELECT the row and hold an update lock on the data in a transaction to prevent other users from getting past the same SELECT portion - but inthat case there is no insert to be done. If the row is not there in the first place, there's nothing to lock and each user could hit the INSERT at the same time. Constraints would obviously prevent data integrity issues. So what are we really talking about here? Still confused... Just that the second thread will now throw dup exception and client has to
handle it. IMO, this should be handled correctly inside the sproc. The sproc should add item if not exist and return id of new or existing row, and only throw exception if a real unhandled exception occurs. The client could catch the error and retry, but I don't think that is right way to handle this. I also don't like the idea of using try/catch blocks in this way in the "normal" logic path as exceptions are relatively expensive and should be used for real exceptions per best practices. That said, I don't currently have a better idea then Bob's, but I would sure think this is a common DB pattern, that probably has a simple (and fast) solution. -- Show quoteWilliam Stacey [MVP] "David Gugick" <david.gugick-nospam@quest.com> wrote in message news:usmQY9u5FHA.964@tk2msftngp13.phx.gbl... > William Stacey [MVP] wrote: >> I don't think that is thread safe. Multiple threads could end up in >> the insert block. >> > > I'm getting confused about this part of the discussion. I assume we're not > disputing that multiple users can run the same stored procedure at the > same time. I also assume we're not disputing that performing a SELECT to > see if a row exists in a table, and having that SELECT fail, will not > prevent two users from possibly hitting the INSERT command at the same > time. I don't think thread-safe is the correct term here. Thread-safe (at > least to me) implies the prevention of multiple threads in the same > program hitting the same data structure and stepping on each others data. > If we're using the term here in the context of preventing two users from > trying to insert the same key value at the same time, I don't think this > can be done if the key row does not already exist. If it did, you could > SELECT the row and hold an update lock on the data in a transaction to > prevent other users from getting past the same SELECT portion - but inthat > case there is no insert to be done. If the row is not there in the first > place, there's nothing to lock and each user could hit the INSERT at the > same time. Constraints would obviously prevent data integrity issues. > > So what are we really talking about here? Still confused... > > > -- > David Gugick > Quest Software > www.imceda.com > www.quest.com > > I also don't like the idea of using try/catch blocks in this way in So, you're saying basic business logic should be implemented in the platform > the "normal" logic path as exceptions are relatively expensive and should be > used for real exceptions per best practices. itself? How? Should a failed insert become an update? Should a failed insert be ignored by design? And should it become a select? Or maybe "prophesied exclusive locking" could be introduced - one that can see into the future? IMHO that's exactly why TRY...CATCH was introduced - plus it enables us to handle more exceptions on the server rather than propagating them to the client. Either way, I'd much rather see exceptions than end up with unexpected results. ML Not sure we are talking about the same thing any longer. The proc should
handle the test-and-set atomically without needing to throw an internal exception to get that specific job done. Try/Catch is good, but should be used to handle true exceptions (i.e. things we did not expect to happen). This exception, I hope and suspect, can be avoided by the implementation (which we are trying to find). -- Show quoteWilliam Stacey [MVP] "ML" <M*@discussions.microsoft.com> wrote in message news:A93CFB6F-CD1C-4C84-8509-22FB38282ACF@microsoft.com... >> I also don't like the idea of using try/catch blocks in this way in >> the "normal" logic path as exceptions are relatively expensive and should >> be >> used for real exceptions per best practices. > > So, you're saying basic business logic should be implemented in the > platform > itself? How? Should a failed insert become an update? Should a failed > insert > be ignored by design? And should it become a select? Or maybe "prophesied > exclusive locking" could be introduced - one that can see into the future? > > IMHO that's exactly why TRY...CATCH was introduced - plus it enables us to > handle more exceptions on the server rather than propagating them to the > client. > > Either way, I'd much rather see exceptions than end up with unexpected > results. > > > ML Ok. First of all - when you say thread, do you actually mean connection? It
sure seems so. :) > Not sure we are talking about the same thing any longer. The proc should TRY...CATCH is the general (generic, if you will) solution. It handles most > handle the test-and-set atomically without needing to throw an internal > exception to get that specific job done. Try/Catch is good, but should be > used to handle true exceptions (i.e. things we did not expect to happen). exceptions... > This exception, I hope and suspect, can be avoided by the implementation ....and I agree - some are better handled without the TRY...CATCH, but I > (which we are trying to find). don't think the platform should handle them, since their nature is (in most cases) purely business-related. In case of an insert that should not happen, because it already happened, although not detected by the current session (does that sound weird or is it just me? :), then using serializable transaction isolation level would be the natural option. The only trouble being that it blocks *everything*. Thus transactions (or sessions) not intended to insert data should make use of the readpast locking hint. Which still leaves us with transactions that intend to insert rows - using readpast might lead to exceptions as might using nolock. What about the combination of both? 1) If any of the rows returned by the nolock-select that are not returned by the readpast-select correspond to the row I'm trying to insert, then the insert will fail if the other transaction is rolled back => better not insert. 2) However, if none of the rows returned by the nolock-select that are not returned by the readpast-select correspond to the row I'm trying to insert, the insert won't fail, since no other transaction is trying to insert the same data => continue with insert, get scope_identity, insert details. 3) And, if the row I'm trying to insert is returned by both the readpast-select and the nolock-select, then I don't need to insert at all, so just get identity and insert details. I think a combination of the three states can lead us to a solution. Any other thoughts? ML > Ok. First of all - when you say thread, do you actually mean connection? No. As I said, 1+ threads in the *server (i.e. the server process). > It > sure seems so. :) Connection will boil down to threads in the server, so talking in terms of threads is proper for this discussion I think. > TRY...CATCH is the general (generic, if you will) solution. It handles I am a big fan of try/catch, I use it all the time. My point is still this. > most > exceptions... Using try/catch in this way is, IMO, not good. Catching exceptions are expensive and should not be used as a control flow construct if it can be avoided. If we can do the test-set atomic, we don't need the try/catch and will let unexpected exceptions flow back to client. > ...and I agree - some are better handled without the TRY...CATCH, but I I never said the platform should handle them. I would agree.> don't think the platform should handle them, I think Brian and Hugo may have the answer here. -- William Stacey [MVP] > No. As I said, 1+ threads in the *server (i.e. the server process). If the DML tasks of a single connection get split amongst several threads > Connection will boil down to threads in the server, so talking in terms of > threads is proper for this discussion I think. for purposes other than supporting parallelism, we're - pardon my language - completely screwed, and can start looking for jobs in the fast-food business. This simply cannot happen, since it absolutely breaks the atomicity of a DML task. Even Brian's example won't work if the server actually allows threads to "run amok". But there's only one way to know for sure - we should test it. Or ask MS. Or better yet, test it. ML > If the DML tasks of a single connection get split amongst several threads Hi ML. We are not on same page yet. Not single connection worried about > for purposes other than supporting parallelism, we're - pardon my > language - > completely screwed, and can start looking for jobs in the fast-food > business. > This simply cannot happen, since it absolutely breaks the atomicity of a > DML > task. here. Multiple connections (i.e. from diff users or different jobs) that run the same sproc at ~same time, which AFAIK, start new threads. So if multiple threads are running same proc, we need to design for that depending on spoc. I think Brian has got it above. Cheers. Have a great weekend guys. -- William Stacey [MVP] Bob wrote:
> So I never have to worry about two threads calling the select part of That's a possiblilty, but is easily handled with with constraints or > the proc at once and > both getting Null? Then both call the insert forcing one to get an > error because the domain existed. indexing. If you want to check if a row exists before the insert, I think that's fine, despite the added overhead. But why is it a problem if the insert fails because someone inserted a row before that time. The insert will error out and the application can easily handle that. There would be no damage to the data. Which inevitably leads us to ask: Why not just perform the insert and forget the checking and the overhead it adds since that actually reduces concurrency overall. On Fri, 11 Nov 2005 10:07:15 -0500, "Bob" <msg***@hotmail.com> wrote: You don't worry about thread safety in the relational world, you worry>So I never have to worry about two threads calling the select part of the >proc at once and >both getting Null? Then both call the insert forcing one to get an >error because the domain existed. about transactions and isolation levels. It comes to the same thing. J. Bob wrote:
Show quote > I am trying add an item to a table that does not allow duplicates but That looks fine. Just make sure you have an index on the "Name" column. > if the item already exists I want to return its id. I need an SQL > equivalent to a critical section but I am not sure how its done. It > would like something like the below code. > > --Begin Critical Section > > SELECT @id = id from domains where domains.name = @domain > if @id is null > begin > INSERT Domains(name) SELECT @domain; > SELECT @domain_id = SCOPE_IDENTITY(); > end > > -- End critical section > > > Thanks for any suggestions. If you have a clustered index on ID and a non-clustered index on "Name" then you can eliminate a bookmark lookup operation on the SELECT.  |
|||||||||||||||||||||||
Other interesting topics