I've got a SELECT WITH (UPDLOCK, ROWLOCK) WHERE followed by an UPDATE WHERE statement. The results of the SELECT statement are deserialized in C# and updates are made to the deserialized object. Then the object is serialized back into the table with the UPDATE statement. I've got this code running within a transaction scope with the ReadCommited isolation level.
My service receives requests to update data and the requests can come in on different threads. What I'm seeing, is that once in a while, the log messages from my application indicate that two different threads are able to issue the above SELECT statement and both are receiving results. This is a problem since the thread that issues the last UPDATE will overwrite the changes made by the first. Each thread has its own connection and transaction scope.
I've researched all over the place and have tried a few different things, but all things point to the fact that query hints are just hints and that SQL may or may not pay attention to them. If that's the case, how am I suppose to perform a SELECT with the intention of updating so that no one else can do the same? I haven't tried table level locking, but I'd really like to avoid that if possible.
Here is the situation i am stuck with, see the example first and below explained the problem:
-- 'SESSION A
create table foo (
id integer,
pid integer,
data varchar(10)
);
begin transaction
insert into foo values ( 1, 1, 'foo' )
insert into foo values ( 2, 1, 'bar' )
insert into foo values ( 3, 1, 'bozo' )
insert into foo values ( 4, 2, 'snafu' )
insert into foo values ( 5, 2, 'rimrom' )
insert into foo values ( 6, 2, 'blark' )
insert into foo values ( 7, 3, 'smeg' )
commit transaction
create index foo_id_idx on foo ( id )
create index foo_pid_idx on foo ( pid )
begin transaction
insert into foo values ( 9, 3, 'blamo' )
-- 'SESSION B
begin transaction
select id, data from foo with ( updlock, rowlock ) where id = 5;
-- Problem:
-- Uncommitted transaction in session A, with insert into table FOO, aquires lock on index foo_pid_idx which BLOCKS select with ( updlock, rowlock ) in session B.
-- Insert should aquire only exclusive rowlock. Why does insert block select with ( updlock, rowlock )?
This article instructed me on how to process rows from a table used as a data queue for multiple processes.
http://www.mssqltips.com/tip.asp?tip=1257
I tested this against the AdventureWorks DB (SQL 2005) and multiple SQL connections inside of Sql Mgmt. Studio).
Connection1:
BEGIN TRANSACTION
SELECT TOP 1 * FROM Production.WorkOrder WITH (updlock, readpast) --skips over locked rows --COMMIT TRANSACTION
Connection2:
BEGIN TRANSACTION
SELECT TOP 1 * FROM Production.WorkOrder WITH (updlock, readpast) --skips over locked rows
COMMIT TRANSACTION
This works like I want where connection 2 skips over the locked row from connection 1 and gets the next available record from the table / queue. However, when I add ORDER BY tsql to each sql statement, connection 2 is now blocked waiting for Connection 1 to commit. (This is not what I want)
Connection1:
BEGIN TRANSACTION
SELECT TOP 1 * FROM Production.WorkOrder WITH (updlock, readpast) order by DueDate
--COMMIT TRANSACTION
Connection2:
BEGIN TRANSACTION
SELECT TOP 1 * FROM Production.WorkOrder WITH (updlock, readpast) order by DueDate --is blocked until connection 1 commits transaction
COMMIT TRANSACTION
How do I prevent blocking when using these locking hints with ORDER BY?
There is a trace flag that tells SQL Server to ignore index hinting in incoming queries. I'm having a Monday morning problem and I can't remember the trace number nor find it in my notes. Can anyone else come up with it?
I am trying to issue the following statement to lock a record with inner join but I have an error:-
SELECT amaster.acctno,bmaster.balance,bmaster.YEAR FROM amaster [ROWLOCK] with (Xlock) INNER JOIN bmaster ON amaster.acctno = bmaster.acctno WHERE (bmaster.YEAR = 2007) acctno = 10000100 ORDER BY amaster.acctno
The error: Msg 4104, Level 16, State 1, Line 1 The multi-part identifier "amaster.acctno" could not be bound.
Hello,I need to lock only one row with exclusive lock (nobody else can see ormodify this row), but when I use "with (xlock, rowlock)" it doesn't work.XLOCK always locks whole table.Can anybody help me?Thanks in advance.Magda
connection 1. one does select max(grp) from orv and one does select max(grp) from orh. orh is the historical file from orv. We did this to know which is the greather grp between these 2 files. After having did this, we add 1 at grp field. we insert into orv the record max(grp) + 1
connection 2. an other application could insert at the same time record in this table orv with same parameters.
my problem is the following. I need to block record in orv table either on the select ( connection 1) or Insert ( connection 2) to avoid having select max(grp) + 1 on orv at connection 1 and Insert a record into orv at connection 2. I believe I need to use HOLDLOCK, UPDLOCK. but I have not the habitude to use them.
Can I do this ? connection 1 select max(grp) from orv WITH HOLDLOCK connection 2 what should I use to avoid lock when I need to insert into orv. ?
Is there anything I can do from the Enterprise Manager console or fromwithin a JDBC connection to achieve the same effect as WITH(UPDLOCK)?Yes, I could change all of my SQL statements to include the lock... butisn't there any way to set or tweak something in SQL Server so that I won'thave to hack a lot of code* to make things concurrent? Perhaps a way to setUPDLOCK as the default behaviour for the server, or schema, or table, orsomething?Jerry H.* == The existing SQL has to remain as generic as possible so that it can beimplemented for four other databases.
Hi,Sql-Server 2000, 2005.A report fetches a lot of rows using the "WITH (ROWLOCK)" syntax (thesql is generated on the fly by a tool and not easily changeable).SELECT col1, col2 FROM mytab WITH (ROWLOCK) WHERE ...."The select-clause runs for several minutes.Another user fetches one of those rows and tries to update it. Theresult is a lock timeout.I suppose that the long running select-clause has put a shared lock onthe rows and the updater (exclusive-lock) will have to wait for thelong-running select and so the lock timeout is expiring.Are all those rows "shared locked" until all are fetched?Would there be any change if the "WITH (ROWLOCK)" is removed, isn'talthough "shared lock" the default behaviour?The "WITH (NOLOCK)" would probably help?What about the definition of optimistic concurrency, shouldn't allselect-clauses contain "WITH (NOLOCK)" to allow an optimisticconcurrency scenario?Regards Roger.PS. Probably some misunderstanding from me here, but this should bethe right place to get it right.
Hi all,Is this legal ?SELECT a.col1,b.col2,c.col3FROM tab1 a WITH (UPDLOCK) , tab2 b, tab3 cWHERE a.col1 = b.col1and b.col2 = c.col1WIll the above cause a UPDLOCK on tab1 and not tab2 and tab3?COmments,thoughts,criticisms?I have a problem with a query that performs a multi-table jointo get column values and then one of the tables in the join isbeing updated. I am getting DEADLOCKs and was wondering if I couldtry and reduce that.Also how can I find out what locks are in effect for a certainquery..Is it syslocks or are there any other tables..?DrD
Hello all. I'm a litle confused about what's best to use, either isolation levels or locking per table. Cause there are some queries in the stored procedures where I don't need locking i.e. when I check the status of client, but other queries where I do need locking like when I check the existence of a product.
What's best to use, can I combine both? Could you explain it thecnically?
Reading about avoiding deadlocks in SQL, I have found different opnions about whether using or not NOLOCK/ROWLOCK hints on SELECT/UPDATE/DELETE statements. I have several applications executing transactions on the same databases and tables, including inserts, updates and deletes. I wonder if by using NOLOCK/ROWLOCK I could decrease the chances of having deadlocks. At least using ROWLOCK on my update statements?. I just need some advice here.
The benifit of UPDLOCK is that it avoids deadlock in case both sessions run the below query at the same time.The table has clustered index on ID column
----session 1 -------- begin transaction select * from a1
update a1 set id = 22 where id = 2
----session 2 -------- begin transaction select * from a1
update a1 set id = 22 where id = 2
Now to avoid deadlock in the above scenario we should use (UPDLOCK) hint in the select statement.Now my question is that deadlock will be avoided in this case when both the sessions use UPDLOCK hint. If only one session uses UPDLOCk and other does not then there will be deadlock .For example session 1 uses UPDLOCK hint this will hold the U lock on the row, but the session 2 does not use this hint and apply shared lock on the same row. Now there will be deadlock when session 1 tries to update the record and is blocked by shared locks of session 2. same will be the case with session 2 and both will wait for each other and hence dead lock.so what steps can be taken to avoid deadlocks in this case. I do not want to use Snapshot isolation.
I have a stored procedure that updates a table. I also have an UDF that allows dirty reads (nolock).
What's the precedence level in SQL server? If I add xlock,rowlock to the update statement, will the dirty read wait for the update transaction to commit, or will it perform a dirty read regardless of the locking scheme in the update statement?
I have a SSIS package that is run from one job, nowhere else. The package has TransactionOption NotSupported.
In the SSIS package I first have a sequence container that has TransactionOption Required (Serializable). The sequence container contains several Execute SQL tasks that access the same SQL Server 2005 database. The Execute SQL tasks have TransactionOption supported / Serializable. The first SQL statement is:
select p.column_name
from table_name p with (XLOCK, ROWLOCK)
where p.second_column_name = 'COLUMN_VALUE'
After this there are a couple of SQL tasks, and finally a task containing the following SQL:
select p.third_column_name
from table_name p
where p.second_colomn_name = 'COLUNM_NAME'
The idea is that the first query exclusively locks the row in the table_name-table for mutual exclusion purposes, i.e. so that if for some reason the SSIS package would be executed simultaneously two or more times, only one package execution could lock the row and proceed and the other executions will have to wait. There's an index on column second_column_name in the table to avoid the select locking other rows in addition to the required row.
Some questions:
1) Is it so in my setup that when SSIS runtime executes the sequence container it creates a transaction in the beginning of the sequence container and commits the transaction in the end of the sequence container? And in my setup the Execute SQL tasks in the sequence containar are executed under the same transaction?
2) I have a problem that the second query sometimes gives this error:"ErrorCode: -1073548784. ErrorDescription: Executing the query "XXXXX" failed with the following error: "Transaction (Process ID 73) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly."
The 1st query has locked the row for the transaction. How can the second query be deadlocked, shouldn't the transaction have a lock on the row? I'd understand if the 1st query failed sometimes, but don't understand how the 2nd quey can fail.
I am kind of confused about the way SQL Server 2000 handles the hintsthat users supply with their SQL statements.[color=blue]>From BOL, it seems that one can specify them with "WITH (...)" clauses[/color]in SQL statements known as table hints. Sometimes, multiple uses ofthis form in a statement is OK. Then there is the OPTION clause forspecifying statement hints. However, the documentation on OPTIONsection discourages their use.Being relatively new to SQL Server and still learning about it, what isthe general practice? Use hints or not? And if so, how (through WITHor OPTION clauses)?Cheers!
I am running SQL7 SP2 and and noticing table the query processor table scans when I ussue a between 'date1' and 'date2' instead of using the datetime index. If I put in the index hint (index = ix_datetimeXXXX) the query runs fine. My question is does this index hint restict the use of other indexes in the query and secondly how can I specify multiple index hints? Thanks in advance.
Whilst running a query I recieved the error below. Cannot create a worktable row larger than allowable maximum. Resubmit your query with the ROBUST PLAN hint.
My VB.Net (with SQL Server backend) application currently allows more than one user to look at a particular record at the same time. This is not a problem unless both those users also try to update that record as well. One user's changes then overwrite the other's.
I've been reading up on locking hints but my database knowledge is a little scant and I'm also rather dense and need things spelling out for me!! So I have a few questions that I hope someone can help with:
If I add an updlock to my update SQL statement, this would allow both users to view the record but would only allow one user's changes through. Is that correct?
For the other user, would SQL Server return an error message that I can use to tell the user that their update has not worked?
Would I have to get my VB.NET application to re-get the record information so that the user who's update failed can see the changes made by the other user and reapply their own changes?
Does the updlock become unlocked once the record is updated or do I need to specifically unlock it somehow?
I am having problems with doing what seams to be a very easy query. For some reason the SQL Server is trying to do nested loops instead of hash join. I tried to force the use of the hash join using the join hint.
Query 1
select * from DIM_DATE DD inner hash join ( select A.student_key, CONVERT(int, CONVERT(varchar, COALESCE (A.date_withdrawn, getdate()), 112)) AS date_withdrawn_current FROM FACT_STUDENT AS A ) SSE on DD.date_key= date_withdrawn_current This query gives an error:
Msg 8622, Level 16, State 1, Line 1 Query processor could not produce a query plan because of the hints defined in this query. Resubmit the query without specifying any hints and without using SET FORCEPLAN
Second query is not really what I want but it illustrate that it works fine when getdate() is not used.
Query 2
select * from DIM_DATE DD inner hash join ( select A.student_key, CONVERT(int, CONVERT(varchar, COALESCE (A.date_withdrawn, A.date_enrolled), 112)) AS date_withdrawn_current FROM FACT_STUDENT AS A ) SSE on DD.date_key= date_withdrawn_current Is there some problem with using function getdate() ? It works fine in SQL Server 2000
This problem occurs on the SQL Server 2005 SP2 ( 9.00.3050.00 (X64) ) and (9.00.2050)
I noticed that the online books say the following: Note The SQL Server query optimizer automatically makes the correct determination. It is recommended that table-level locking hints be used to change the default locking behavior only when necessary. Also, at another place in online books, it says: The table hints are ignored if the table is not accessed by the query plan. From the above, it seems that using locking hints is not going to guarantee that SQL Server will follow them. Is this true?
Why SQL server dose work as follows when I dose not provide any join hints? It looks like HASH join is the best plan, but SQL dose not. What kind of JOIN method is used by SQL optimizer?
Thanks in advance, Wonhyuk William Chung wonhyukc@usa.net MCSE/ MCT
----------- use northwind go select orderid, CompanyName --productname, from orders o inner join customers c on o.customerID = c.CustomerID /* Table `Orders`. Scan count 91, logical reads 184, physical reads 0, read-ahead reads 0. Table `Customers`. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0. .0553 */
hi all, I'm trying to run queries on relatively small tables (a few hundred thousand rows) with subqueries of counts per primary key columns as such:
(ColA in tableA is the primary key)
select * from tableA p where exists (select 1 from ( select ColA, count(1) cnt from TableA group by ColA having count(1)>1 ) t where t.ColA= p.ColA) order by some_col
my problem is that sqlserver 2005 sp5 does not materialize the internal subquery properly, or execute it beforehand and it gets confused as heck and pegs the CPUs at 100% forever.
What hints can I use to solve this issue? I've tried to use ..... "with ...." to prepare/materialize the table upfront, no luck, one version of statement pegged one cpu at 100%, while the other statement pegged ALL cpu's at 100% -- don't remember which.
My only solution right now was to create these subqueries as PHYSICAL tables -- and this would solve the problem but that would entail creating a lot of un-necessary objects.
have a dts package that does txt -> sql server. i have 200 txt files with the same exact format.
just want to know if i can write a SP passing a parameter that loads this txt files. because i dont wanna create 200 packages or 200 sources to load 200 txt files.
say: exec SP_loadTXT txt1
or should i use bulk insert?
any approaches are fine. any suggestions are fine too.
