We have found deadlocks in our application. Deadlocks occure between SELECT and UPDATE. I get deadlock graph using profiler and find that SELECT makes SIU lock. Below you'll find SELECT statement:
select t1.*
from MyTable t1
--self join on field1 and field2
left outer join
(select field1, field2
[Code] ....
Why SIU lock is set?
Hey I have a terrible problem !!
I have a query, and It doesn't matter if it has finished, the table is still locked. Is there any clause to unlock the share mod lock (force) when the query has finished or any way to ensure that table has no locks to continue with another query or transaction ?
Could you give me any sentence ?
Thanks you !!
Is anyone have already encountered such a problem :
When a table is locked by several locks (key, pag), either a SELECT request which does not need to lock any row cannot succeed.
In SQL Server Enterprise Manager, I see that my request is waiting for a lock "LCK_M_S" !!!
How I can avoid this kind of situation which seems to me completely abnormal.
I have never been faced with this kind of problem in Oracle.
Thanks for anyone who will help me to resolve this "bug".
Hi,
Let's assume the client code call a stored procedure which reads: SELECT * FROM myTable WHERE ID < 100. Let's assume that for whatever reason, the query becomes very slow and takes 60 seconds to complete. This 60 secs, could be the time for the DB engine to fetch all the rows (happening before returning the resultset to client code), or during the transfer of the resultset to client code (slow network throughput).
Question1: could this prevent another user from doing SELECT on myTable? (could be onn different ID or even overlapping the ID between 1 and 100).
Question2: could this prevent another user from performing write (UPDATE/DELETE) on the rows with ID between 1 and 100?
Question3: can another user perform a write (INS, DEL, UPD) on rows outside of the ID between 1 and 100?
Thanks in advance for any help.
Hi,
I want to make SQLTransaction as global and use it checking the State.
But then where there are Only Select queries are going to fire, it will open transaction.
So, Does it locks database/tables when Only Select query comes in SQLTransaction.
If you have another successful way of doing this, Please suggest.
Thanking you.
tats
I've got an INSERT that's selecting data from a linked server and attempting to push 10 million rows into the blank table. More or less, it looks like this:
insert into ReceivingTable (
Field1, Field2, Field3, Field4
, Field5, Field6, Field7, Field8
, Field9, Field10, Field11, Field12
, Field13, Field14, Field15
[code]...
The instance of the SQL Server Database Engine cannot obtain a LOCK resource at this time. Rerun your statement when there are fewer active users. Ask the database administrator to check the lock and memory configuration for this instance, or to check for long-running transactions. There are no other active users. I ran it again and monitored the following DMO to watch the growth of locks for that spid:
SELECT request_session_id, COUNT (*) num_locks
-- select *
FROM sys.dm_tran_locks
--where request_session_id = 77
GROUP BY request_session_id
ORDER BY count (*) DESC
The number of locks started small and held for a while around 4-7 locks, but at about 5 minutes in the number of locks held by that spid grew dramatically to more than 8 million before finally erroring again with the same message. Researching, I can't figure out why it's not escalating from row locks to table locks at the appropriate threshold. The threshold in was set to 0 at first (Server Properties > Advanced > Parallelism > Locks). I set it to 5000, and it still didn't seem to work. Rewriting the INSERT to include a WITH (TABLOCK) allows it to finish successfully in testing. My problem is that it's coming out of an ETL with source code that I can't edit. I need to figure out how to force it to escalate to locking the entire table via table or server level settings.
A colleague suggested that installing service packs may take care of it (the client is running SQL Server 2008 R2 (RTM)), but I haven't found anything online to support that theory.
I am trying to find a way to lock rows of data used in a SELECT query from being read by another SELECT query.
I could do a "begin tran - select - update - rollback" sequence but was wondering if there is a cleaner way to do this??
Tried UPDLOCK, ROWLOCK, TABLOCK, HOLDLOCK in multiple variations but none seem to block the select.
We are migrating our database(s) from ORACLE to SQL. In Oracle we were able to issue a SELECT statement and see all of the locks (Blocking and Non-Blocking) currently in the system. The query also included the Process ID of the process we needed to kill in order to get rid of the lock.
We now need to create the same type of query for Microsoft SQL Server 2012. I have seen postings on different sites saying that this info can be obtained using SP_WHO2 or using the SQL Server Management Studio Activity Monitor's PROCESSES tab, but we are looking for a SELECT statement that will give us similar information.
I am unable to the access on table even after providing the SELECT permission on table.
Used Query by me :
Here Test is schema ; Card is table ; User is Satish
To grant select on Table
GRANT SELECT ON TEST.Card TO satish
Even after this it is not working, So provided select on schema also.
used query : GRANT SELECT ON SCHEMA::TEST TO Satish.
Hi,
Yesterday, we have had a sudden load in our SQL Server 2000 which resulted in several locks. There was not too much time to investigate as we had to rush. A team member had reviewed the processes in EM, Manegement, Current Activity. Looking for blocking processes and killed them.
She told me that as soon as the blocking SPID was killed, another one arose and she had to repeat the operation a dozen of time. When done, the server activity was back to normal. She noticed that more than half of the blocking processes showed that they executed the stored Proc "P_SearchProducts".
We don't own the server and the information on what had happened at that time (batches or resource intensive operations, etc.) is not available for now.
The team suggests that we set the Transaction Isolation Level to Read UNCOMMITTED for this SP. I would like to know better about locks before I go ahead.
P_SearchProducts returns 5 recordsets each one could contains from 1 to 200 rows. To achieve the results, it creates about 10 intermediate tables (SELECT ... INTO #TableX) these temp tables are then used progressively to arrive to the final results. Roughly the volume of these temp tables could be double than the final results. The developer who wrote this SP is not a guru in SQL, there is room for improvement. But here are my questions:
Q1. Could the series SELECT ... INTO #TableX in P_SearchProducts prevent or lock another connection from executing the same SP? If yes, under which conditions?
Q2. Let's assume that P_SearchProducts has a slow execution time. Could it prevent another connection from updating the Product table? And thus leading to a deadlock situation? Something like another transaction (by User2) has obtained lock on most of Product tables, except the Product table which were being slowly read by User1 executing P_SearchProducts. But User1 cannot read the other product tables b/c there are locks by User2.
Q3. If the contention issue was provoked by the slow execution time of many request to exec
P_SearchProducts (let's assume there were suddenly 50 users on the web hitting the search product feature at the same time). Could the Read Uncommitted magically resolve the contention issue, providing we accept the consequences of the dirty read.
Sorry for the long post and thank you in advance for any help.
the cursor at the bottom iterates only to print the number of rows.The problem is in the select. This takes 30 seconds to iterate through 1242 records.But if I add a TOP 1000000 or whatever number to the select, the same iteration takes less than a 1 second.I've tested each query without cursor, and both have the same cost and performance. (Not exactly the same plan)Note that I got the same performance improvement declaring the cursor as STATIC.Why the top is affecting the cursor iteration so much?
Declare @query varchar(512)
DECLARE Itera CURSOR --LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR
select --TOP 1000000
[code]....
Hi All,
I am new to SQL Server and having trouble using SQL Server Management Studio. I am unable to select the Database Engine in management studio.
I am able to see the instance of default database engine (MSSQLServer) running in Reporting Services manager as well as in Surface area configuration manager, but it is not visible in the drop down list in Management Studio's "Select Database Engine" menu.
I had removed Sql server 2005 earlier ( I was able to select the database engine in Management Studio then). But when I installed it again, I was unable to install the Sql Server Tools (it said that my Upgrade is blocked). So, I cleaned the Windows Registry of all keys containing 'Sql'. After this I tried installing it again and successfully installed Sql Server 2005 + ALL TOOLS. But this time I am unable to select the database engine in management studio.
Thanks and Regards to ALL
I’ve been looking at a bug in an application stored proc. This merely inserts 7 million rows from one table into another (empty) table.
INSERT
dbo.TradeDataPrimary
(Id, SinaiTrade)
SELECT
ts.Id,
CAST(ts.XmlTrade
AS varbinary(MAX))
FROM dbo.TradePrimary ts
Both tables have a clustered index with the same key order (on the first column) but the query optimizer insisted on placing a SORT step in the query plan. This use a lot of tempdb which I wanted to avoid.
I discovered that the bug was a data type mismatch. Fixing the bug and/or dropping the clustered index from the target table before the insert resulted in the plan I expected, that is, with no sort.
I was hoping it would run faster but, unfortunately, as can be seen from the new plan below, a serial plan is used. This results in the insert taking nearly three times as long as the original.
I’ve been looking at Paul White’s article: [URL] ....
and Adam Machanic’s: [URL] ....
Both are excellent articles but neither approaches i.e. trace flag 8649 or make_parallel () give me a parallel plan. Am I missing a trick here? How I can force parallelism? I know there are no features that require a serial zone in the plan otherwise the plan with the sort would not be parallel..
This is SQL Server 2012 Enterprise 11.0.5522.0, 512 GB of RAM and 16 procs.
i have a column with mulitple ids stored with commas . i want to pass ids and get data along with name from the table..how to get multiselect value in a variable in sql server function
View 4 Replies View RelatedI am trying to install SQL Server Sep 2005 CTP - ENTERPRISE, there are
View 1 Replies View RelatedI have read that even during read procedures (sql select statements), sql server uses row locking. I know that you can use the NOLOCK keyword, but if you don't everytime that a user makes a selects statement on a table, does sql server really lock those rows, and if so are they then unavailable to another user who wants to make a select statement at the same time on that same table? That does not seem like it would be the case otherwise it would not scale well. Thanks for any clarification on this.
View 5 Replies View RelatedI am using SQL Server7.0. I opened a table through the Enterprise Manager and left it open. In the Query Analyzer when I try to update a field on that table(more than 2000 rows), it goes on running. When I watched the Current Activity, it shows that the update process is being blocked by the select query. But if I try to update the same column for less than 1500 rows, there is no blocking issue and the update occurs immediately. Can anybody let me know why this is happening and what should I do to prevent it?
View 1 Replies View RelatedI am using Sql Server 7.0
To I got the following error message. Can some one tell how to solve this issue.
Server: Msg 1204, Level 19, State 1, Procedure OPEN_OBJECTS, Line 2
SQL Server has run out of LOCKS. Rerun your statement when there are fewer active users, or ask the system administrator to reconfigure SQL Server with more LOCKS.
ranga.
what the best way to control locks, if inserting couple thousands records from one table to another.
View 5 Replies View RelatedHi
I have a big query which updates around 14000 rows at a time if i place a lock on the table and others try to update the same table is it possible to let them know that table is locked by someone else.
2 quick questions :
1) How do I keep multiple users from editing the same record without locking the entire table? What would be a 'standard' way of handling this?
2) How do I keep 2 people from posting the same record?
Please help me understand locking, THANKS!!!
I have a stored proc which will be entering/updating a record into a table. The table's key is an integer field which I may have to increment by one. I know I can use
declare @nextid int
set @netxid = max(id) from table
insert @nextid into table
Is some kind of lock the best way to approach this?
Hello,
I have a problem in SQLSERVER 2000, when I execute a Query, the table get locked for insert or any other transaction, even for other queries.
Does SQL Server have a kind of lock mode different of Oracle ?
How do I solve this problem ??
Hello There !!
I have a very big problem, with SQL SERVER 2000. I want to know about the locks with select.
When I execute a Select (so big), and I try to update or Insert into one of the tables that I invoke in the select, I get locked.
Is there in SQL Server, something like a Select for update, that could be causing the problem ???
Is there any way to select rows from a table without locking it ?
I really have a big problem with this, and I don't know so much about sql server !
Thank you so much !!!
Some of my tables Were locked IN IS Mode.What does it mean?
Thanks.
I Have 359 locks on MY Database ,They are always there on my DB.The DB is a development database and lots of summary Stored procedures will be running on this DB.
Does it effect the performance.How can I remove those locks.
Thanks.
Hi All,
Sql server 7
pls provide me sql statement that shows all the locks. the goal is to produce the output on an HTML page.
pls help me in solving this
TIA
Adil
Hi
I want to write all my select ststements using locks
how i should write a select ststement using locks
i searched for examples but iam not getting the syntax
Plz give me one example for select statements using locks
thanks in advance
Malathi Rao
if have a transaction with statments like
begin
insert into table A ...where exists (select .. from table A)
transaction
What locks will be placed while inserting and selecting rows,if multiple users are accessing this concurrently
How to lock a Row in SQL2000 so that nobody can select that row.
I applied ROWLOCK, but i am not finding the way.
My query is "SELECT * FROM tablename WITH (ROWLOCK)"
Is this the correct way to write locks.
I would be thankful if u help me
Sir,
This is top urgent Sir !
There are 2 tables for transaction. The header and tail tables. How do I insert records. if one is updated & another is not ? the sequence for tracking the records will fail. How do I deadlock the table for insert especially when I use stored procedures for 'Inserts'.
Sundar Raman S K
Hi,
I have just upgraded my sql 65 to sql 7. The problem I am running into is running out of locks all the time with 50000 locks. The sql box has 4 pentium pro 200 processors and 1 Gig of ram. Here is the error message:
DESCRIPTION:Error: 1204, Severity: 19, State: 1
SQL Server has run out of LOCKS. Rerun your statement when there are fewer active users, or ask the system administrator to reconfigure SQL Server with more LOCKS.
COMMENT:Fatal Error Occured in Resource
Help!!!
Jim
Everytime I try to open the design-mode of a table from enterprise mgr, I get this error 1204.---
SQL Server has run out of LOCKS. Rerun your statement when there are fewer active users, or ask the system administrator to reconfigure SQL Server with more LOCKS.
There no other users but me. Locks have been configured to their max (over 200000000). Don't understand whats wrong. Please help..