MS SQL Server :: Regarding Locks

Locks
I 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 Replies ! View Related

Locks
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 ??

View Replies ! View Related

Locks
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 !!!

View Replies ! View Related

Locks
Some of my tables Were locked IN IS Mode.What does it mean?
Thanks.

View Replies ! View Related

Locks
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.

View Replies ! View Related

Locks
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

View Replies ! View Related

Locks
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!!!

View Replies ! View Related

Locks For A Max Value
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?

View Replies ! View Related

SQL Locks
what the best way to control locks, if inserting couple thousands records from one table to another.

View Replies ! View Related

Locks
Hi
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.

View Replies ! View Related

EM Locks Up..
Has anyone had this before and know a solution. We cannot connect to our SQL 7.0 database (on W2K cluster) through the EM tool. Our application and Query analyzer, profiler, etc. connect just fine.

Any ideas?
Thx..

View Replies ! View Related

Locks
I 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 Replies ! View Related

Locks

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

View Replies ! View Related

Locks
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

View Replies ! View Related

Locks In SQL2000
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

View Replies ! View Related

Many Tempdb Locks?
hi all,we have a SQL2000SP3 runing in W2K3. The application is JDEdwards.recently I've observed that once in a while (about a few hours), therewould be a process from the ERP application that holds quite a numberof extent locks in tempdb, can be as high as 10000 locks. when I runsp_lock on that spid, it gives something like this:697200EXT1:156760 XGRANT697200EXT1:94896 XGRANT697200EXT1:132224 XGRANT697200EXT1:140488 XGRANT697200EXT1:181552 XGRANT697800DB SGRANT697200EXT1:165280 XGRANT697200EXT1:127888 XGRANT697200EXT1:173544 XGRANT697200EXT1:152624 XGRANT697200EXT1:160888 XGRANT697200EXT1:144616 XGRANT697200EXT1:198336 XGRANT697200EXT1:107296 XGRANT697200EXT1:99176 XGRANT697200EXT1:169344 XGRANT697200EXT1:115704 XGRANTI am wondering what action is it doing, creating temp tables?? manythanks.

View Replies ! View Related

Question On Locks
somebody helps ?1.What kind of lockes are created on what resources when followingquerys executes ?select * from JOBQUEUE where ID='XXX'update JOBQUEUE set columnA='YYY' where ID='XXX'2.Under what kind of situation, share lock on a row or on a page or on atable will convert to exclusive lock?Thanks a lot.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View Replies ! View Related

Tracing Sql For Locks
I am fairly new to SQL Server. I am writing a tool in stored procedureto identify locks in a table. I have already written the basic frameworkof the SP. It will reside in master database and take two inputs. Databasename and table name. From that it will show all locks at that instanton that table of that database. If table name is omitted, then it will showlocks on all tables.I am using syslockinfo, spt_values tables and joining with SP_WHO procedureto get the table name, user name and the session id.Now what I need is to find out which SQL is causing the lock and since whenlock is being held on the table. Which tables in master database holds therequired information.TIA.Ravi

View Replies ! View Related

Copy Without Locks
I have a stored procedure which copies data from a view into atemporary table (x2) and then from the temporary table into a tablewhich the users use. It takes 1 minute to get the data into the temptable and seconds to update into the final one (hence the two stages).When I do the initial copy from the view, it locks the various tablesused in the view and potentially blocks the users. It's a complex viewand uses plenty of other tables. We get massive performance issues'generating' the data into a table as opposed to the view.What I want to do is take all the data without locking it. I don'twant to modify the data, just read it and stick the data into a table.ThanksRyanSQL as follows :/*Drop into temp tables first and then proper ones later as thisworks out a lot less time when no data will be available*/TRUNCATE TABLE MISGENERATE.dbo.CBFA_MISDATATemp -- Temp TableTRUNCATE TABLE MISGENERATE.dbo.CBFA_MISPIPDATATemp -- Temp TableINSERT INTO MISGENERATE.dbo.CBFA_MISDATATempSELECT * FROM MIS.dbo.CBFA_MISDATA -- ViewINSERT INTO MISGENERATE.dbo.CBFA_MISPIPDATATempSELECT * FROM MIS.dbo.CBFA_MISPIPDATA -- View/*Now drop this into full MIS tables for speed*/TRUNCATE TABLE MISGENERATE.dbo.CBFA_MISDATATRUNCATE TABLE MISGENERATE.dbo.CBFA_MISPIPDATAINSERT INTO MISGENERATE.dbo.CBFA_MISDATA -- Final TableSELECT * FROM MISGENERATE.dbo.CBFA_MISDATATempINSERT INTO MISGENERATE.dbo.CBFA_MISPIPDATA -- Final TableSELECT * FROM MISGENERATE.dbo.CBFA_MISPIPDATATemp

View Replies ! View Related

Locks With Select
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 !!

View Replies ! View Related

Intermittent Locks
We are experiencing problems intermittently.

Users report problems of various types including timeout messages. We investigate and find a user has acquired a lock which is blocking other users.

We contact the user and they have usually completed their activity and are not always aware of any problem despite them owning a lock.

When the user logs out of the application the lock clears and the system returns to normal.

Indexes have been rebuilt, auto update statistics is on.

Does anyone have any suggestions? :cool:

View Replies ! View Related

SQL Server Locks?
I have a simple web application using ASP.NET/C#/SQL Server 2000/Windows 2000

I'm having problems with the server "hanging" for ~15 minutes. Disk/CPU are idle so this isn't a resource issue. In Enterprise Manager, under "Current Activity" I see several processes waiting on:

LCK_M_X
LCK_M_S
LCK_M_S
NETWORKIO

How do I investigate this? My application is so simple, I have trouble imagining how a lock-related problem could ocurr? There is absolutely no concurrent processing done on this server. There is only one user running this app and there are no separate threads spawned.

Although this problem is sporadic, this happens the most while doing the following:

- Open connection1 (for SELECT)
- Open connection2 (for UPDATE)
- Open SqlDataReader through connection1 with SELECT statement
- Iterate through
- For certain records issue UPDATE statement via connection2.

Here are some sample code snippets. Default ADO.NET options and everything should be cleaned up through C# "using" blocks without relying on garbage collection.

using (SqlConnection selectConnection = LeadsDatabase.OpenConnection()) {
using (SqlConnection updateConnection = LeadsDatabase.OpenConnection()) {
using (SqlCommand dbCommand = new SqlCommand(sql, selectConnection)) {
using (SqlDataReader reader = dbCommand.ExecuteReader()) {
while (reader.Read()) {
using (SqlCommand updateCommand = new SqlCommand(sql, updateConnection)) {
updateCommand.ExecuteNonQuery();

View Replies ! View Related

SQL Page Locks
I understand the basic concept of page locks and table locks, but I am
unclear of the exact details of the locks. Would someone please explain
this to me?

User A goes into WORK_ORDER table record 1
User B goes into WORK_ORDER table record 2

records 1 and 2 are on the same page.
Would user B would be able to access/modify the record even though
user A already had a lock set? Or would that page be locked until
user A`s lock was released?

What would happen if user A tried to access
record 2 as well as record 1?

also,

What would happen if user B tried to access record 1 if
user A was already in it?

Thank you so much!
Toni

View Replies ! View Related

SQL Server Locks
I am having a problem with an SQL6.5 server locking up periodically. I go into the error logs and there is the following message:

98/12/08 20:16:30.64 kernel dpdb_asyncread: mirror: Operating system
error 6(The handle is invalid.) encountered

There is also this entry in the Event Application Log:
Event ID: 17055 Source:MSSQLServer Type:Error Category:Kernel
Description: Mesg 17053 : dpdb_asyncread: mirror: Operating system error 6(The handle is invalid.) encountered

I have already searched that ever valuable MS knowledge-base for OS error 6 and found exactly ZIP! Can anybody shed some light on this?

Thanks!
Andy Roche
Senior Consultant
WARCOM Services

View Replies ! View Related

Intent Locks
I am attempting to delete some records in a table using msquery. Msquery locks up and I see that I have an intent lock on the table in enterprise manager that never seems to get an exclusive lock. What would inhibit an exclusive lock. USer connecting to the database at this time all seem to experience an ODBC error until I kill msquery. I can not kill the intent locks unless killing the msquery app.

Any Ideas??

View Replies ! View Related

Tempdb Locks
Good day.
I have a same problem with tempdb syscolumns locking. I call a
procedure thats creates a temporary tables fill its and try to insert a results into a table
which have a trigger which try to create a temporary tables.
Sometimes this work fine, but sometimes the server is hangs up. The sp_who shows next:

spid status loginame hostname blk dbname cmd
------ ---------- ------------ ------------------------------------ ----- ---------- ------------------------------------------------
1 sleeping sa 0 master MIRROR HANDLER
2 sleeping sa 0 master LAZY WRITER
3 sleeping sa 13 tempdb DUMP TRANSACTION
4 sleeping sa 0 master RA MANAGER
13 sleeping sa PROG1 0 SHOPNEW INSERT
14 runnable sa PROG1 0 master SELECT

The sp_lock procedure shows next:
............................ (I cut a big list)
13 Ex_page-blk 3 316 tempdb
............................

After that I can't make anything with hempdb (use sp_who2 or sp_lock2 or any other) and
can't a KILL 13. I also can't stop a service SQLServer.exe I can OLNY restart the NT...

Note: I tried to use a begin tran ... end tran. No effects reached.
And I can't understand why the server hang's up when he want...

I have a 20000 LE Threshold maximum.

View Replies ! View Related

Systems Locks On SQL 6.5
I have a problem where Backup Exec crashed during a backup of a SQL database and now SQL show 3 extent lock that are now redundant. These cannot be removed from Enterprise manager Is it therefore possible to remove them directly from the master database ??

Thanks

View Replies ! View Related

It Can't Happen, SQL Locks Up
I am using SQL Server 6.5, when two or more independent applications put transactions through SQL, it locks up. Example of locks up.

When the OrderLines table is locked, then I put the following (Select * from OrderLines) then the query does not return any values, the world goes round and round, the only way out is to shut down and cross my fingers whilst SQL goes into recovery mode.

I have read through some of the documentation, such as deadlocks, livelocks and lock starvation but it say none of these will lock the whole machine. But somehow simultaneous transactions can, and the current activity dialog goes red, bright red.

Any ideas?

View Replies ! View Related

No More Locks Problem When I Have 200,000
We are trying to add a about 100,000 transactions to a table with one non-clustered index and 31 million rows in one transaction. We have 200,000 locks and 64 meg ram for SQL Server. We get a No More locks error.
When we try to insert this 100,000 transactions into a table with no rows in it. It works fine using about 7934 locks.
How can this be ? what are we doing wrong.?

Thanks for any help in advance.

Rob Doyle

View Replies ! View Related

Sql Server Has Run Out Of Locks
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..

View Replies ! View Related

Update Locks
In the BOL under "Understanding Locking in SQL Server", there are two paragraphs that describe Update locks. In the second paragraph it reads:

To avoid this potential deadlock problem, update (U) locks are used. Only one transaction can obtain an update (U) lock to a resource at a time. If a transaction modifies a resource, the update (U) lock is converted to an exclusive (X) lock. Otherwise, the lock is converted to a shared-mode lock.

The question is how does SQL determine that a lock can be converted from an exclusive lock to a Shared-mode lock.

Thanks

View Replies ! View Related

Urgent Locks
I keep getting locks while trying to look at anything in enterprise manager when loading a large tables clustered index.

I need to modify a stored procedure but cannot access it.

Any advise.

Thanks,

Steve

View Replies ! View Related

Database Locks
How do I temporarily lock a database (read-only mode) programatically?

For example:

BeginTrans

//LOCK OUT ALL OTHER USERS
//Only the Server has Write access

CommitTrans

THANKS!

View Replies ! View Related

Row Vs Table Locks
I am interested in getting a better handle on how SQL 2000 determines the locking level to apply to different transactions. I am familiar with the fact that SQL does this on the fly but I was wondering if this could be specified in a Stored Procedure to use one over the other instead and what impact if any that might have on indexes. The databases I work with run anywhere from under 100 GB to 150 GB. Thanks for anyone's input on this subject.

Thomas

View Replies ! View Related

Locks In SQL Server
Hi All,

We have one intranet system working absolutely fine since 9 months. But, all at sudden locking problems started and its just like a nightmare. I ran SQL traces, I checked event viewer and I made changes in my front-end coding so that all my recordsets use proper locking features. But, it didn't help.

I know that the presentation of problem is very vague. But, I dont know what else I can write about it.

Any help will be highly appreciated.

Thanx

View Replies ! View Related

Understanding Locks
If load a table using bcp or bulk-insert and use the tablock hint, do views or procs that have the nolock hint against the table work or are they locked out while the table is loading?

Thanks,

Steve

View Replies ! View Related

Sql Server Locks
We've experienced persistent hang up on our application. I tried opening the enterprise manager->management->Current Activity->Locks/Object and I've seen ridiculous entries like master.dbo.spt_values, SIS.dbo.feesclass,tempdb.dbo$##lockinfo205. I don't what this mean? does it tell us that something wrong with the application that does not release locks? please help me.

View Replies ! View Related

Locks And Alerts
Hi,

I am having trouble with locks and performance issues in one application. The SQL server is specific to this application. What settings should i choose to set an alert to let me know when i get too many locks? the locks i am interested in ar ethe ones that show up in SQL EM. ie where a process is blocking another process(es).

I have tried New Alert | lock requests /sec but that is a huge figure when the server is running OK, so i cant guess what value to set for the counter. Is there a better object/counter i should use?

thanks
fatherjack

View Replies ! View Related

Understanding Sch-M Locks...
Hi Guys,
I have written quite a big stored procedure which creates a temporary table (multi-session) and updates it. All the statements are encapsulated in a single transaction which is explicitly declared in the code. What happens is that a lock is being put by the server on that table (of type Sch-M) in order thus preventing any type of operations on it (including simple select)

Now, I want to be able read that table from within another transaction. Why is that I cannot use a table hint NOLOCK in the select statement?

Here is some code which reproduces my problem.

Query A:

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

BEGIN TRAN TR_DEMO;

CREATE TABLE ##TBL1(
Oidx int not null primary key identity(1,1),
Name nvarchar(30) not null,
Type char(1) not null
);

INSERT ##TBL1 (Name,Type) VALUES ('Car','M');

WAITFOR DELAY '00:00:10';

INSERT ##TBL1 (Name,Type) VALUES ('Plane','M');

WAITFOR DELAY '00:00:10';

INSERT ##TBL1 (Name,Type) VALUES('Submarine','M');

WAITFOR DELAY '00:00:10';

DELETE FROM ##TBL1;

DROP TABLE ##TBL1;

COMMIT TRAN TR_DEMO;

Query B:

SELECT TOP 1 * FROM ##TBL1 (NOLOCK) ORDER BY oidx DESC;

Launch query A and then execute query B.

Thanks a lot for your help.

View Replies ! View Related

How To Reconfigure SQL 7 With More LOCKS
Hello to all!

I`m two weeks old with SQL 7 Beta 3 with no formal training whatsoever. I just kinda tinker with it at the moment since it`s installed in a stand alone server with me having sole access.

Using DTS (import into SQL), I tried to migrate an Inventory History dbf (Dbase 3) having 13+ million records. I got this error message after a few hours:

"Error at Destination for Row number 6353502. Errors encountered so far in this task: 1. SQL Server has run out of LOCKS. Rerun your statement where there are fewer active users, or ask the system administrator to reconfigure SQL Server with more locks."

It was the only application running on the server (aside from the SQL services) and i was the sole user. This is my first time on SQL Server and as DBA i should know everything `bout it.

Question is how and where do i reconfigure for more LOCKS? And how many LOCKS do i have to set?

Please help. Thank you in advance

View Replies ! View Related

Running Out Of Locks
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

View Replies ! View Related

High Key Locks Per Second?
When monitoring our production database server during peak times, I notice that the Key lock requests per second average about 600,000. Does this seem high to anyone but me? Any thoughts would be appreciated.

Thanks,
John

View Replies ! View Related

Dead Locks - Regarding
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

View Replies ! View Related

Locks / Transaction Help
I'm carrying out some tests on MS SQL server management studio 2005 and am experiencing a few problems.

I'm trying to test out row locks but keep coming across errors when releasing locks.

e.g. i run this:

Code:

BEGIN TRAN m
SET LOCK_TIMEOUT 0
select top 2 rowid from dbo.hes_eclipse_fin with (UPDLOCK)
where rowid in (33,34)

update dbo.hes_eclipse_fin set date_transferred = '2007-09-09'
where rowid in (33,34)

this should lock the rows untill tranaction m is complete...

i then run this:

Code:

BEGIN TRAN k
SET LOCK_TIMEOUT 0
select top 2 rowid from dbo.hes_eclipse_fin with (UPDLOCK)
where rowid in (33,34)

update dbo.hes_eclipse_fin set date_transferred = '2007-09-09'
where rowid in (33,34)

This fails as expected, as transaction m has the locks for these records. The problem comes that when i call

Code:

COMMIT TRAN M

and then try transaction K again , M doesnt appear to release the locks.

What am i doing wrong?

Any help is appreciated.

View Replies ! View Related

ERP Database Locks Up
Our enterprise consists of a mix of mission-critical transaction-oriented applications. The ERP applications are accessed by 70 branch offices around the country. The network also carries file transfers, e-mail, and Web traffic. Each branch is linked to headquarters via a Frame Relay network with port speeds of 64Kbps.

We are facing problems when ERP database locks up and cause time-outs and frustrating messages such as: "User disconnected - unknown error."

Rachel

View Replies ! View Related

Database Locks
Greetings:

I've a stored procedure that grabs from a table all of the records that have a status = 2. Once these records are grabbed the same stored procedure needs to update the status of the records to 3. Something like this:

SELECT
*
FROM
[MyTable]
WHERE
Status = 2

UPDATE
[MyTable]
SET Status = 3
WHERE
Status = 2

What I'm afraid will happen is that between the running of the select statement and the running of the update statement that a record that originally had a status of 1 will be set to 2 - which means the record was never selected by the first half of the stored procedure but had its status updated to 3 in the second half of the stored procedure.

I thought at first wrapping all of this in a transaction would fix the problem but I've done some more reading and that doesn't seem to be the case. I need the Update to run on only those records the Select query grabbed no matter what else happens in the table.

Is there a lock to address this in a way that will allow other records (those not selected) to be updated to a value of 2 without them being updated to 3 when the Update query runs?

View Replies ! View Related

How To Unlock 'Key' Locks?
I had some process in which ... I need to do some transaction control within dynamic SQL like as follows

Begin tran

insert.......

if @@error <>0

rollback
else

commit

Now, when i executed above dynamic SQL for some cases I think there is a some severe error at insert statement and thus resulted in abnormal termination of Dynamic SQL i.e. neither rollback nor commit is executed.

Now certain row level locks are still present on object involved in insert. Now my question is how should i Unlock those locked resources.

Thanks,

Mandip

View Replies ! View Related

DEAD LOCKS
I have a Orders table, this has a Primary key on one Column
( OrderID )

We are using a Stored Proc to insert data into this table ( I am not
starting the Transaction in the stored Proc ) as its controlled by the front end written in C++.

when ever there is a update on this Orders table I move the record to the ordershistory table. ( I have a trigger in the CTOrders table
on INSERT and UPDATE )

The C++ App does an Async I/O. Offlate the App is experiencing a few dead locks during the day espicially at the market open and Close as this database is for the Trading System.

I need a few pointers as to start debugging this.

Thx

View Replies ! View Related