Hi,
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
Hi All,I have a database that is serving a web site with reasonably hightraffiic.We're getting errors at certain points where processes are beinglocked. In particular, one of our people has suggested that an updatestatement contained within a stored procedure that uses a wherecondition that only touches on a column that has a clustered primaryindex on it will still cause a table lock.So, for example:UPDATE ORDERS SETprod = @product,val = @valWHERE ordid = @ordidIn this case ordid has a clustered primary index on it.Can anyone tell me if this would be the case, and if there's a way ofensuring that we are only doing a row lock on the record specified inthe where condition?Many, many thanks in advance!Much warmth,Murray
Hi,I need to lock a database (prevent users from connecting) in order toupdate it. I already know how to kick everyone out with their spid buti can't figure how to prevent them from reconnecting.Thanks !
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?
Hi, I'm taking an Excel spreadsheet (that could have around 30k rows) and processing it in SSIS. I essentially have a flag in one of the spreadsheet cols that indicates whether the record is already in the database or not.
I'm splitting the data using a conditional split on this column and using a OLE DB Destination (Fast Load) to perform the inserts and a OLE DB Command to fire a stored procedure to perform any updates. Both the OLE DB Destination and the stored procedure are hitting the same table and the two operations could be executing at the same time as they both appear directly after the Conditional Split, so the OLE DB Destination is set NOT to lock the table.
This seemed to work OK until recently. I've just added 2 triggers onto the table in question which I don't want to fire 30,000 times during the import. As the OLE DB Destination is set to use Fast Load, it doesn't fire the triggers - cool. In the update stored procedure it disables the trigger before performing it's update and re-enables the trigger when finished. Currently this does mean that if you only had updates, the trigger could be enables/disabled 30,000 times. That sounds kinda bad, but I don't really know if this carries a large overhead or not?
If, when importing now you have both updates and inserts the whole process locks up. From looking at activity monitor, it seems as though the INSERT gets suspended.
Do I have a fundamental problem with how I've structured the Data Flow or am I just being really stupid in Enabling/Disabling a trigger that many times, which is probably causing the problem?
I open query analyser and on one tab I update a record in a transaction and hold it.
begin tran update customers set territory = 'x' where customer = 'A00001' --rollback tran
In a second tab I attempt to update all records in the table
update customers set carrier = ''
Clear this fails because of the lock placed during the first script and this is fine.
However, is there a way to get the 2nd script to ignore the locked rows and just update as many as it can? The obvious answer seemed to be the READPAST hint like follows…
update customers with (READPAST) set carrier = ''
…but this is still blocked by the original lock. I’ve tried combining it with all sorts of other table hints but all seem to get blocked.
The following does work, ignoring the lock and not returning the data
Select * from customers with (READPAST) where customer = 'A00001'
I’ve tried combining this with the update like so…
update customers set carrier = '' from customers with (READPAST) where customer = 'A00001'
..but this is blocked too.
I’m so desperate I tried moving the update into a cursor and update one row at a time. Nothing worked. I thought I might be able to do something like this
If (Select count(*) from customers with (READPAST) where customer = 'A00001') > 0 --then perform update
..but this returns a value of 1 even though the following returns no rows.
Select * from customers with (READPAST) where customer = 'A00001'
I am using conditional split Checking to see if a record exists and if so update else insert. But this cause database dead lock any one has suggestion?
I 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.
We recently implemented merge replication.We were expereincing. The replication is between 2 SQL Servers (2005) over same network box, and since we have introduced the replication, the performance has degraded considerably on subscriber end.
1) One thing that should be mention is that its a "unidirectional Direction" flow of changes is from publisher towards subscriber (only one publisher and distributor as well and one subscriber ).
2) Updates are high than inserts and only one article let say "Article1" ave update up to 2000 per day and i am experiecing that dbo.MSmerge_upd_sp_Article1_GUID taking more cpu time.what should be do..
on subscriber database response time is going to slow and i am experiencing a lot of number of LOCK time outs on application end.
can any one can also suggest me server level settings for aviding locking time out.
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
Hi,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!
hi, 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.
Hello 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...
Hi,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.
Which 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.
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 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. --------------------
I 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?
I 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
I 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:
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
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
Hi,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
HiI want to lock a table using JDBC as I want to perform some query's (readand write) in exclusive mode. Different threads will be executing the samecode simultaneously. I am using the following statementsStatement stmt = connection.createStatement();stmt.executeUpdate("lock table STATUS_TBL in EXCLUSIVE mode");but am getting the following error[Microsoft][SQLServer 2000 Driver for JDBC][SQLServer]Line 1: Incorrectsyntax near 'STATUS_TBL'.can somebody tell the correct syntax for the lock table statement.Thanks
Hi! I use proc handling special business logic (I also use constraints, indexes for that ;-) Now I have a situation where I should check multiple rows with an proc. Preventing multi-user issues I want to lock the table (yes, yes potential performance issue, but in this case there are few simultaneous jobs) - in Oracle I could lock the table, but what to do in SQL Server?
Maybe you have an better alternative, then let me hear ;-)
