Lock Table For Queries With Query WAITFOR
Sep 13, 2007Hi all.
How can I lock table with longtime query?
ADVERTISEMENT
Analyzing Lock Order Of Queries Against Same Table To Prevent Deadlocks
Aug 5, 2014I need to discover the actual order in which locks are acquired on a table during a query.
This with a goal of analyzing the lock order of queries against the same table to prevent deadlocks.
I'm using SQL Server 2008 R2.
From Management Studio I execute:
begin transaction
<my query>
exec sp_lock
rollback transaction
In the output I see interesting information about which locks are acquired, but:
- are this locks ordered by the time they're acquired? That is, can I be sure that lock at row n is acquired before lock at row n+1?
- if not, how can I get this information?
Waitfor Time Vs Waitfor Delay
May 21, 2004Whats the difference between waitfor time and waitfor delay in SQL Sever2000.
Which one is better if you want to send a file at a certain time?
Thanks,
SQL Server 2012 :: Find Queries That Lock Tables Or Not Using Primary Key While Running Update
Jul 20, 2015I need to search for such SPs in my database in which the queries for update a table contains where clause which uses non primary key while updating rows in table.
If employee table have empId as primary key and an Update query is using empName in where clause to update employee record then such SP should be listed. so there would be hundreds of tables with their primary key and thousands of SPs in a database. How can I find them where the "where" clause is using some other column than its primary key.
If there is any other hint or query to identify such queries that lock tables, I only found the above few queries that are not using primary key in where clause.
How To Lock A Table So Others Cannot Lock It
May 23, 2001Hi,
I want to lock a table so others cannot lock it but able to read it inside transactions.
The coding I need is something like this: set implicit_transactions on begin transaction select * from table1 with (tablock, holdlock) update table2 set field1 = 'test' commit transaction commit transaction
I have tried the coding above, it won't prevent others from locking table1.
So, I changed the tablock to tablockx to prevent others from locking table1. But this will also prevent others from reading table1. So, how can I lock table1 so others cannot lock it but still able to read it?
Thank you for any help
Waitfor NULL
Jan 13, 2006I have an issue where I am trying to copy between 10-100 TIFF images to a directory, use the command line to combine them (I have a command line driven utility to accomplish this), copy the combined TIFF to another directory, and move to another set of TIFF images.
I can't figure out to make SQL wait for xp_cmdshell to return NULL and then move through the loop. Is there a way to use WAITFOR to look to see what is returned and then move on?
WAITFOR DELAY '000:00:05'
Jul 23, 2005Env: SQL Server 2000It actually waited for 50 seconds instead of 5, triedWAITFOR DELAY '000:00:005' and WAITFOR DELAY '000:00:5' respectively,and got same behavior. Bug or ?TIA
View 3 Replies View RelatedWAITFOR DELAY
Jul 20, 2005System: Dell PowerEdge Server with 3 GB RAM, 2.4 GHz CeleronSoftware: Microsoft SQL Server 2000 Enterprise running on Windows 2003ServerSoftware: Microsoft SQL Server 2000 Enterprise running on Windows 2000ServerIf you run the code below, you'll notice something odd occuring. TheMilliSecond value does not change after a 1Millisecond delay. Is this a bugor am I doing something wrong?Any assistance will be greatly appreciated-----------------------------------------------------------------------------------/*Programmer : Des L. DavisDate : July 4, 2004Purpose : Testing Delayed Reaction*/CREATE PROCEDURE [sp_TestDelay] ASDECLARE @DELAYPERIOD NCHAR(12)SET @DELAYPERIOD = '00:00:00:001'DECLARE @Retries INTSET @Retries = 0DECLARE @MAXTRIES INTSET @MAXTRIES = 1000 -- Maximum number of tries before timing outDECLARE @Modified DATETIMEWHILE ( ( @Retries <= @MAXTRIES ) ) BEGINSET @Modified = GetDate() -- Set Modification DatePRINT @RetriesPRINT @ModifiedPRINT 'Year = ' + CONVERT( NCHAR(4), DATEPART( YEAR, @Modified ) )PRINT 'DayOfYear = ' + CONVERT( NCHAR(3), DATEPART( DAYOFYEAR ,@Modified ) )PRINT 'Hour = ' + CONVERT( NCHAR(2), DATEPART( HOUR, @Modified ) )PRINT 'Minute = ' + CONVERT( NCHAR(2), DATEPART( MINUTE, @Modified ) )PRINT 'Second = ' + CONVERT( NCHAR(2), DATEPART( SECOND, @Modified ) )PRINT 'MilliSecond = ' + CONVERT( NCHAR(3), DATEPART( MILLISECOND,@Modified ) )SET @Retries = @Retries + 1 -- Increment loop counter and retryWAITFOR DELAY @DELAYPERIODENDGO-----------------------------------------------------------------------------------
View 14 Replies View RelatedWAITFOR DELAY And CPU Comsuming
Sep 25, 2000Hi,
I created a stored procedure that run as a service in the SQL Server , as long as the server is up.
It queries a table , does some work on the rows one by one , and after each row is done - deletes it from the table.
If there is no data - it goes to 'sleep' - waitfor delay ('00:00:03')
does anyone knows if that waitfor command releases the cpu , or just counting and uses the cpu ?
Eyal.
WAITFOR DELAY Too Slow?
Jun 23, 2006http://www.castellcomputers.com/?p=44
This bit of SQL script can be used to insert a pause of one hundredth of a second in a trigger, stored procedure or SQL script. This can be useful for when MSSQL’s built-in WAITFOR DELAY function is just too long (it’s minimum increment is a full second!)
declare @later datetime
declare @now datetime
set @later = current_timestamp+'00:00:00.01'
set @now = current_timestamp
while @now < @later
begin
set @now = current_timestamp
end
I'm sure it could be further tweaked to become a function.. And heck, maybe one of the gurus will tell me that there's something i don't know about WAITFOR or whatnot.. Feedback appreciated!
--
Doug Castell
GoldMine Guru
www.castellcomputers.com
Error: A Deadlock Was Detected While Trying To Lock Variable X For Read Access. A Lock Could Not Be Acquired After 16 Attempts
Feb 2, 2007I simply made my script task (or any other task) fail
In my package error handler i have a Exec SQL task - for Stored Proc
SP statement is set in following expression (works fine in design time):
"EXEC [dbo].[us_sp_Insert_STG_FEED_EVENT_LOG] @FEED_ID= " + (DT_WSTR,10) @[User::FEED_ID] + ", @FEED_EVENT_LOG_TYPE_ID = 3, @STARTED_ON = '"+(DT_WSTR,30)@[System::StartTime] +"', @ENDED_ON = NULL, @message = 'Package failed. ErrorCode: "+(DT_WSTR,10)@[System::ErrorCode]+" ErrorMsg: "+@[System::ErrorDescription]+"', @FILES_PROCESSED = '" + @[User::t_ProcessedFiles] + "', @PKG_EXECUTION_ID = '" + @[System::ExecutionInstanceGUID] + "'"
From progress:
Error: The Script returned a failure result.
Task SCR REIL Data failed
OnError - Task SQL Insert Error Msg
Error: A deadlock was detected while trying to lock variable "System::ErrorCode, System::ErrorDescription, System::ExecutionInstanceGUID, System::StartTime, User::FEED_ID, User::t_ProcessedFiles" for read access. A lock could not be acquired after 16 attempts and timed out.
Error: The expression ""EXEC [dbo].[us_sp_Insert_STG_FEED_EVENT_LOG] @FEED_ID= " + (DT_WSTR,10) @[User::FEED_ID] + ", @FEED_EVENT_LOG_TYPE_ID = 3, @STARTED_ON = '"+(DT_WSTR,30)@[System::StartTime] +"', @ENDED_ON = NULL, @message = 'Package failed. ErrorCode: "+(DT_WSTR,10)@[System::ErrorCode]+" ErrorMsg: "+@[System::ErrorDescription]+"', @FILES_PROCESSED = '" + @[User::t_ProcessedFiles] + "', @PKG_EXECUTION_ID = '" + @[System::ExecutionInstanceGUID] + "'"" on property "SqlStatementSource" cannot be evaluated. Modify the expression to be valid.
Warning: The Execution method succeeded, but the number of errors raised (4) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors.
And how did I get 4 errors? - I only set my script task result to failure
Stored Procedure Waitfor Question
Nov 28, 2003The following code does work but when the stored procedure is called it takes about 10 seconds for it to load. I am not refering to the 30 minute wait before the name is deleted.
Anyone have any ideas why the call would be so slow? Thanks.
SERVICE CODE
conPubs = (New SqlConnection("Server=blah blah blah")
cmdCommand = New SqlCommand("DeleteName", conPubs)
cmdCommand.CommandType = CommandType.StoredProcedure
cmdCommand.Parameters.Add("@Name", Name)
cmdCommand.Parameters.Item(0).Value = Name
conPubs.Open()
cmdCommand.ExecuteNonQuery()
conPubs.Close()
STOREDPROCEDURE CODE
Create Procedure[DeleteName]
@Name varchar (30)
As
Begin
Set NoCount On
WaitFor Delay "00:30:00"
Delete From NameTable Where Name = @Name
End
GO
Lock SQL Table Using Asp.net
Jul 23, 2004Hi,all:
This problem almost drives me crazy, hope I can get some hints from you guyz!!!
Ok, here is the situation:
I wanna only one users 2 modify the data(update) from my page each time, and if at the same time, there are some other users connecting my database through .aspx page, they can only browse the data until the first users finish updating.
It seems I need to implement locking the database, but I am not sure how I am gonna do that using asp.net!!!
Thanx in advance!
How To Lock A Table Or Row In Sql?
Nov 1, 2005hi, i have an application that updates some records in sql tables, and i want to do a web application that updates records in the some database-table(sql) so, my question is how can i lock the row or table so i dont have concurrency problems.tnx in advance.
View 2 Replies View RelatedTable Lock...
Nov 5, 2007Hello Friends,
I am having a VB application running for the SQL SERVER DB. The VB application is installed on the multiple of PCs in the network. Now when I am trying to fetch the same from all the different PCs simultaneously, its amazingly fast. But the issue comes when I am trying to update the same table (but different rows) from the different PCs simultaneously. The time taken is directly proportaional to the number of users. I am not getting what could be the problem? Can any one suggest me the approch? Is it some related to table / row / page locking? As all the connections are trying to update on the same table. I checked the isolation level. Its default, "READ COMMITTED". Kindly suggest...
Thanks in advance........
Rahul Jha
Table Lock
Jun 5, 2008Hi Gurus,
How can we know that particular table is locked.
Thanks,
ServerTeam
How To Lock A Table
Mar 21, 2006Hai to all,
I want to lock a patricular table during my transaction is this possible, for Example
Create sp() as
BEGIN
Lock Table
Do Some work
Relaese the Lock
END
like this.
Table Lock
Feb 10, 2008Is there any way to lock tables in SQL Server?
View 2 Replies View RelatedTable Lock
Jul 23, 2005Hi,If I run an insert statement from the query analyzer and then try toopen the table from enterprise manager then it takes long time to openthe table. But this problem dissapears when i put the statement insideBegin/End Transaction statement.Any idea why this is happening?Thank in advance.Taw.
View 1 Replies View RelatedTable Lock
Jul 23, 2005Which lock type or isolation level should I use to be sure that no onewill read or write or do anything with the table I'm using?Code block should look something like this:lock tableread value from tablechange value to new_valueupdate table set value = new_valuerelease lockWhile I'm changing the value absolutly no one should be able to readfrom the table.
View 7 Replies View RelatedTable Lock
Nov 21, 2007How do I do a simple table lock?
Thanks,
MeTitus
Table Lock
May 6, 2008Hi Friends
In my server i am getting this error how can i slove this error
could u people any body please help me
error:
Table Lock Wait Time Per Second is 867.3330
milliseconds per second, which exceeds threshold of 10
milliseconds per second."
Thanks
subu
WAITFOR (RECEIVE... Causes System.Transactions.TransactionAbortedException?
Oct 4, 2007I'm implementing a transactional receive from SSB queue. I used System.Transactions.TransactionScope to simplify the transaction management code. When I used the "WAITFOR (RECEIVE ...), TIMEOUT 100" statment I get TransactionAbortedException when I try to open the second connection under the same TransactionScope block.
Note that when I remove the WAITFOR and just doing RECEIVE everything works fine and the transaction state is guaranteed.
Does anybody know if this a known issue with WAITFOR? Does it supposed to abort the transaction when the command completes?
See code below and stack traces I got.
Thanks,
Noam Helfman
Repro code:
Code Blockusing (TransactionScope txnScope = new
TransactionScope(TransactionScopeOption.RequiresNew))
{
using (SqlConnection conn1 = new SqlConnection(TestConnectionString))
{
conn1.Open();
using (SqlCommand command = conn1.CreateCommand())
{
command.CommandText =
"WAITFOR (RECEIVE TOP(1) message_body FROM TestReceiveQueue), TIMEOUT 100"; // <-- this causes TransactionAbortedException below
//command.CommandText = "RECEIVE TOP(1) message_body FROM TestReceiveQueue"; // <-- this works
command.CommandType = CommandType.Text;
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
byte[] message = (byte[])reader["message_body"];
}
}
}
using (SqlConnection conn2 = new SqlConnection(TestConnectionString))
{
conn2.Open(); // <-- TransactionAbortedException here
using (SqlCommand command = conn2.CreateCommand())
{
command.CommandText = string.Format("INSERT INTO tbl1 VALUES (1)");
command.CommandType = CommandType.Text;
command.ExecuteNonQuery();
}
}
txnScope.Complete();
}
}
...
Schema:
Code BlockCREATE QUEUE TestReceiveQueue WITH STATUS=ON ,RETENTION=OFF;
CREATE TABLE tbl1 (col1 int not null);
Stack traces:
Code Block
System.Data.SqlClient.SqlException: Cannot promote the transaction to a distributed transaction because there is an active save point in this transaction.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject
stateObj)
at System.Data.SqlClient.TdsParser.TdsExecuteTransactionManagerRequest(Byte[] buffer, TransactionManagerRequestType request, String transactionName, TransactionManagerIsolationLevel isoLevel, Int32 timeout, SqlInternalTransaction transaction, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransactionYukon(TransactionRequest transactionRequest, String transactionName, IsolationLevel iso, SqlInternalTransaction internalTransaction)
at System.Data.SqlClient.SqlInternalConnectionTds.ExecuteTransaction(TransactionRequest transactionRequest, String name, IsolationLevel iso, SqlInternalTransaction internalTransaction)
at System.Data.SqlClient.SqlDelegatedTransaction.Promote()
System.Transactions.TransactionPromotionException: Failure while attempting to promote transaction.
at System.Data.SqlClient.SqlDelegatedTransaction.Promote()
at System.Transactions.TransactionStatePSPEOperation.PSPEPromote(InternalTransaction tx)
at System.Transactions.TransactionStateDelegatedBase.EnterState(InternalTransaction tx)
System.Transactions.TransactionAbortedException: The transaction has aborted.
at System.Transactions.TransactionStateAborted.CheckForFinishedTransaction(InternalTransaction tx)
at System.Transactions.EnlistableStates.Promote(InternalTransaction tx)
at System.Transactions.Transaction.Promote()
at System.Transactions.TransactionInterop.ConvertToOletxTransaction(Transaction transaction)
at System.Transactions.TransactionInterop.GetExportCookie(Transaction transaction, Byte[] whereabouts)
at System.Data.SqlClient.SqlInternalConnection.EnlistNonNull(Transaction tx)
at System.Data.SqlClient.SqlInternalConnection.Enlist(Transaction tx)
at System.Data.SqlClient.SqlInternalConnectionTds.Activate(Transaction transaction)
at System.Data.ProviderBase.DbConnectionInternal.ActivateConnection(Transaction transaction)
at System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, dbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at SqlServiceBrokerQueueTest.WAITFORBug() in SqlServiceBrokerQueueTest.cs:line 375
Table Lock-urgent
Oct 3, 2000Hi,
How can I see which table is locked up by some particular process?
I know that I can view paricular spid from 'current activity'. But is there any way I can see which table is the center of problem?
I really appreciate your help..
How To Lock The Row Or Table When Update?
Nov 4, 2004Hi,
I am working on a project which need to produce a sequential certificate number, Everytime I need a new certificate number, I need to find out what is the max number in the database and then the new certificate number just max+1.
But how can I block another
transaction to check what is max certi. number while this transaction is in the middle of writing the new certificate number(max+1) into database . Does ADLockOptimistic work in this case? Here is the code:
My database sql 2000.
cmdTemp.CommandText="Select max(certificateNumber) from product_table where certificateNumber<> 8888888"
set cert_info=Server.CreateObject("ADODB.RecordSet")
cert_info.Open cmdTemp, , AdOpenKeySet, adLockOptimistic
If Not cert_info.EOF then
certnumber=cert_info(0)+1
End if
cert_info.close
set cert_info=nothing
To Give A Table Lock
Oct 14, 2005How can i give a table wise exclusive lock in MSSQL Server ?
I got the description but, How can I apply this ?
The sql :
LOCK TABLE <tablename> IN EXCLUSIVE MODE
is not working.
Is there any query/method to do this ?
Please help ...
thanks
About Exclusive locks
--------------------
Exclusive (X) locks are used for data modification operations, such as UPDATE, INSERT, or DELETE.
Other transactions cannot read or modify data locked with an Exclusive (X) lock. If a Shared (S) exists, other transactions cannot acquire an Exclusive (X) lock.
--------------------
Keeping A Lock On Table Or Row
Feb 5, 2004I need to update a row but keep a lock on the table (so no one else can update it) while I do run some more code. In Oracle, it always locks whatever you update until you hit commit, but sql server works opposite. How do I tell it not to commit a statement, or how would I explicitly get a lock and then release it later?
View 4 Replies View RelatedHelp: Table Lock Confirmation
Mar 12, 2004I need confirmation from you SQL Server experts out there. Please let me know if the following works. Thanks!
This stored procedure gets a value and increments by 1, but while it does this, I want to lock the table so no other processes can read the same value between the UPDATE and SELECT (of course, this may only happen in a fraction of a second, but I anticipate that we will have thousands of concurrent users). I need to manually increment this column because an identity column is not appropriate in this case.
BEGIN TRANSACTION
UPDATE forum WITH (TABLOCKX)
SET forum_last_used_msg_id = forum_last_used_msg_id + 1
WHERE forum_id = @forum_id
SELECT @new_id = forum_last_used_msg_id
FROM forum
WHERE forum_id = @forum_id
COMMIT TRANSACTION
How To Remove Lock Associated With Table
Aug 7, 2013I have a query like this
SELECT TOP 1 * FROM ITAM_RAMS_STAGING
not getting value...seems like lock associated with that table
how to remove the lock if exists
Explicitly Lock A Table?
Apr 23, 2007I have multiple applications which can potentially update the same trigger simultaneously. Each application:
(1) Reads the contents of the current trigger
(2) Creates a new trigger based on the current contents
(3) Drops the trigger
(4) Creates the new trigger
I need to insure that once one application begins step (1), then no other application can start step (1) until step (4) has completed.
Any ideas on how this can be done? Some databases have a concept of locking tables explicitly, so for them, I can do:
(0) lock table foo;
(1) - (4)
(5) unlock table foo;
Is there such a concept in mysql or is there another way of accomplishing this?
Thanks,
Eric
Table Lock - Issue
Jul 27, 2007Folks,
I have an update statement and it looks like it is holding exclusive lock on the table and does not release it until it completes. a PAGLOCK hint has been specified on the update statement and i think it is being ignored. It is a transaction database.so, other queries accessing that table has to wait for it to complete and thus causing timeouts.The Update statement is also causing high IO and CPU utilization. How do I
1) reduce the granularity level to prevent the locking
2)any ideas on optimizing the query?
Here is the query:
UPDATE Customers
SET IndexStart = TMP.IndexStart,
IndexPosition = TMP.IndexStart,
IndexStop = TMP.IndexStop,
IndexLevel = TMP.IndexLevel
FROM Customers AS C WITH (PAGLOCK)
INNER JOIN #tmp_IndexBCs AS TMP WITH (TABLOCKX) ON C.ID= TMP.ID
Thx
Sri
Table Lock Issue?
Oct 6, 2007Hi All
We are facing atable lock issue , while running a SSIS package
We have two flows . one for insert and other for update into the same target table.
The update is done using a procedure.
We have disabled the lock table option in the target( in the insert flow)
The first set of records which come for updates flow fine.
But when the records start coming into the insert flow, the data flow stops.
Is this a table lock issue?
How do i set the commit interval at the update flow?
can someone lpease help me out of this situation
Thanka and Regards
Preethe
How Do You Lock An Entire Table?
Jul 23, 2005Hi,I need to lock a table so that Inserts are prevented as well as deleted andupdates. At present I'm thinking this might do it:SELECT * FROM myTable WITH(UPLOCK)but then again I'm not sure whether this will cover the insert case.Thanks,Robin
View 5 Replies View Related