A mechanism for managing concurrent access to shared data or resources by granting temporarily exclusive access to the processes requesting the lock.

learn more… | top users | synonyms (1)

4
votes
1answer
259 views

Performance impact of sp_configure blocked process threshold change

I've been tasked with identifying any blocking happening on a production server. My plan to achieve this is to use EXECUTE sp_configure 'blocked process threshold', 5 In combination with a server ...
1
vote
1answer
245 views

How can I make a select statement get blocked?

Hello I am trying to intentionally make a SQL select statement get blocked by another simple SQL delete or update statement, for the purpose of learning. I prefer only InnoDB tables. To prepare the ...
2
votes
2answers
2k views

Does a simple select query acquire locks?

I am very new to SQL Server, and would like to understand whether the following, very simple select statement would take any locks. Select * from Student; Please consider the case where the ...
0
votes
1answer
32 views

How to get default locking hint of a table

Is there is any way to get default locking hint using a query. I want to know whether the locking use by my transaction is tablock | rowlock | nolock etc.
1
vote
0answers
33 views

Does SQL Server place shared locks on scanned records when using REPEATABLE READ [duplicate]

Assume a SQLCmd session which is using transaction isolation level REPEATABLE READ. In this session I start a transaction and execute an UPDATE statement with a WHERE clause on a non indexed column. ...
7
votes
2answers
1k views

Updating a table with more than 850 million rows of data

I have been tasked with writing an update query to update a table with more than 850 million rows of data. Here are the table structures: Source Tables : CREATE TABLE [dbo].[SourceTable1]( ...
2
votes
0answers
230 views

Getting LCK_M_S On read committed Snapshot Isolation

I'm getting LCK_M_S on read_committed_snapshot isolation. From my understanding, this isn't supposed to happen since my selects are not placing shared locks anymore. From my logs, I can see that my ...
0
votes
2answers
198 views

Idle connection plus schema-modifying query causing locked database

As part of our automated deployment process for a web app running on a LAMP stack, we drop all our triggers and stored procedures and recreate them from source control. It turns out there was a hidden ...
2
votes
1answer
3k views

Find out number of active locks on a table

I was wondering if there is a way to find out the number of active locks held on a table in SQL Server?
0
votes
1answer
205 views

Invalid rowid error

I'm trying to see, how UPDATE Lock helps to minimize error while dml (delete/update) operations. declare cursor update_lock is select empno from emp where deptno=&no for update of sal; num ...
3
votes
2answers
298 views

pessimistic locking and client death

We are considering pessimistic locking for our project (SELECT ... FOR UPDATE); as we are used to optimistic locking, we are afraid about the operation of the server being blocked on such a lock (lock ...
0
votes
1answer
62 views

Pessimistic locking worst case

We are using Java EE. And are making an apllication where in the worst case, aloot of message queue messages willl come form the same User. Therefore we are looking on Pessimistic, SELECT FOR UPDATE ...
6
votes
1answer
415 views

UNIQUE index key violation

I have a table with a PK and a unique non-clustered index, as follows: CREATE TABLE Table1 ( Id INT IDENTITY(1,1) NOT NULL, Field1 VARCHAR(25) NOT NULL, Field2 VARCHAR(25) NULL, ...
1
vote
1answer
141 views

LOAD DATA INFILE on a MyISAM table blocks user read-queries, can we get around this?

I've got a large reference table, 100GB, in a MyISAM table, load it up from scratch goes well (4ish hours) using LOAD DATA INFILE. But we have 1GB each day we want to update in a batch process using ...
2
votes
1answer
358 views

Minimize locking on UPDATE with multiple WHERE columns

I have a problematic query that's causing a lock timeout: UPDATE <some_table> SET row1=<some value> WHERE row1 IS NULL AND row2 > <some value> ORDER BY row2 LIMIT 100 ...
0
votes
2answers
302 views

How are DB locks tied to connections and sessions?

I've heard of page- and row-level locks, but never heard them used with regards to sessions and connections. Today, our (MySQL) DBA was talking about row locks and implied that their scope can be set ...
2
votes
3answers
2k views

Why is optimistic locking faster than pessimistic locking?

Both forms of locking cause a process to wait for a correct copy of the record if its currently in use by another process. With pessimistic locking, the lock mechanism comes from the DB itself (a ...
1
vote
2answers
122 views

How does optimistic locking actually enforce re-read/update?

My understanding of optimistic locking is that it uses a timestamp on each record in a table to determine the "version" of the record, so that when the record is access by multiple processes at the ...
6
votes
1answer
2k views

When a record is locked in Oracle, can we know which record is locked?

When a record is locked ,can we know which one is locked? How can I get the record rowid or something else info? I can get some info by this sql SELECT ...
1
vote
3answers
4k views

Avoiding “Waiting for table metadata lock” when `ALTER TABLE DROP PARTITION`?

I have some tables that many users need to access to: mysql> show create table v3_cam_date\G *************************** 1. row *************************** Table: v3_cam_date Create Table: ...
3
votes
2answers
1k views

difference between UPDLOCK and FOR UPDATE

given the following code: Set conn = CreateObject("ADODB.Connection") Set RS = CreateObject("ADODB.Recordset") conn.BeginTrans ... //This is the line it is about RS.Open "SELECT a,b FROM c FOR ...
5
votes
1answer
669 views

Global locking for multi-master Oracle GoldenGate replication

This is a very complex scenario, but I figured a state-of-the-art challenge might interest some of the many high-end users of dba.se. Problem I'm working on an intercontinental data replication ...
0
votes
0answers
75 views

What would this non-'unique index with a unique search condition` query lock?

I am not sure about this part of the transaction manual For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement ...
1
vote
2answers
86 views

Do I need a locking read?

I am inserting rows in a table, based on a value and on the existence of a row, which I get from SELECT statements. This function can be run concurrently, so I need to do some extra obviously. The ...
3
votes
3answers
135 views

Tuning reads (or perhaps the truncate!) for unusual database structure requiring regular truncates

We have an unusual database structure (I leave the reasoning for another question), that in it's current form requires a regular truncation and recreation of allot of row (about 1 million). We're ...
0
votes
0answers
70 views

Which mysql server 5.5 variables would be involved in queries locking in sending data state?

A large portion of my queries (~58%) be it for queries with previously quick return rates or other, are spending in my monitor as sending data. I made some configuration changes but it was very broad ...
1
vote
1answer
118 views

What is the locking behavior for Updatable/Insertable Views?

Consider I have a View that is merely a simple join between 2 tables, selecting all columns. The View is Updatable and Insertable. What would be the locking behavior when executing an UPDATE or ...
0
votes
0answers
336 views

InnoDB row level locks without SELECTs?

I have several (>30) MySQL clients inserting into InnoDB tables concurrently. I keep encountering lock wait timeouts and can't figure out why. Here is the situation: Each client runs the following ...
1
vote
1answer
130 views

SQL Server 2005 Blocking Issue

I am experiencing a blocking issue in sql server 2005. An end user executes a query (e.g. select statements) and seems to get into a never-ending blocking until i kill the process e.g. for hours on ...
0
votes
2answers
1k views

Create a “barrier” (prevent parallel execution) in Oracle PL/SQL?

I'm maintaining some stored procedures in an Oracle 11g RAC environment that are part of the pipeline of steps for data-interchange with other systems. One in particular has been producing duplicate ...
0
votes
2answers
365 views

MySQL concurrent INSERTs

I have a MySQL database with InnoDB tables. There are different client processes making SELECT (to check the existence of a value) and INSERT or UPDATE (depending on the result of the select) ...
4
votes
1answer
2k views

ASPState database locking and growth problems

We use an ASPState database to persist .NET Session state on a SQL Server 2005 cluster. We are seeing some strange behavior during peak periods The DeleteExpiredSessions proc is run every minute ...
6
votes
4answers
1k views

MySQL InnoDB locks primary key on delete even in READ COMMITTED

Preface Our application runs several threads that execute DELETE queries in parallel. The queries affect isolated data, i.e. there should be no possibility that concurrent DELETE occurs on the same ...
1
vote
0answers
222 views

Transactional Replication Snapshots causing serious locking after 2012 upgrade

I am experiencing serious locking when adding new articles to an existing transactional replication publication, which is hanging the website. This is the first time I have attempted this since I ...
5
votes
2answers
618 views

Options for setting NOLOCK hint in dataset queries

Some context: At first we wrote reports just "straight up", without any locking hints in the queries. With the larger reports this would sometimes cause locking problems. At first we remedied this by ...
3
votes
1answer
915 views

How to force the use of row locks?

I am trying to get my table to use row locks. I have already disabled lock escalation. The isolation level is READ_COMMITTED_SNAPSHOT alter table <TABLE_NAME> SET (LOCK_ESCALATION=DISABLE) go ...
4
votes
1answer
148 views

Do isolation levels only apply to SELECTS and not UPDATES? [closed]

Scenario that might demonstrate different behavior for SELECTS depending on the isolation level: 1) 0:00 Thread A runs a query that returns 1000 rows that takes 5 minutes to complete 2) 0:02 Thread B ...
5
votes
2answers
3k views

How to correctly implement optimistic locking in MySQL

How does one correctly implement optimistic locking in MySQL? Our team has deduced that we must do #4 below or else there is a risk that another thread can update the same version of the record, but ...
1
vote
0answers
203 views

Oracle locked sessions with ASP.Net

I am connecting to an Oracle database in ASP.Net using SQLDataSource. My connection string is: <add name="ConnectionString3" connectionString="Data Source=sml; User ID=sml; Password=sml; ...
3
votes
2answers
1k views

Deadlock on insert for a table with an insert trigger that updates column in same triggered table

By specification each table uses a guid as the primary key. Another requirement is that each resource requires a 6 number identifier (100001, 100002, ...) as a public id that is unique within the ...
2
votes
2answers
3k views

Table Lock on INSERT - How to optimize

we have a few long running procedures in the database, that cause an instant Table Lock on our main text storage table, which causes all other requests of the web app to wait (because the table is ...
1
vote
1answer
326 views

drop postgres table with minimal locking

I'm trying to run the following Postgres migration script DROP TABLE some_table; DROP TABLE another_table; The problem is that this script causes excessive locking when run on the production ...
10
votes
4answers
2k views

Locking issue with concurrent DELETE / INSERT in PostgreSQL

This is pretty simple, but I'm baffled by what PG does (v9.0). We start with a simple table: CREATE TABLE test (id INT PRIMARY KEY); and a few rows: INSERT INTO TEST VALUES (1); INSERT INTO TEST ...
0
votes
1answer
625 views

SQL Server 2008, database completely locked

Our database becomes completely locked, and it seems that many tables not related to each other are locked at once. When this happens, it becomes locked for approximately 2 minutes, and when this ...
4
votes
2answers
245 views

Schema blocking in READ_COMMITTED_SNAPSHOT

I am trying to troubleshoot locking behavior and the READ_COMMITTED_SNAPSHOT isolation level while attempting to resolve concurrency issues. Background: Assume an online ordering system (ecommerce). ...
2
votes
2answers
2k views

Parallel inserts cause deadlock on DB2

The scenario is the following: The application attempts to insert into the same table from two parallel threads making 4000 insertions in each thread (separate transaction per thread). It causes the ...
8
votes
3answers
855 views

How can I ensure that only one copy of a procedure is running in Oracle?

We have the need to ensure that only one copy of a particular procedure is running in Oracle. If it is already running and a user tries to open another, then it should error. Whats the best method ...
8
votes
3answers
971 views

Does SSRS lock the table when querying?

My senior DBA told me that SQL Query execution by default doesn't lock the table. I was having some issues with my SQL Server Reporting Services (SSRS) report which seems to be getting some issues ...
1
vote
1answer
641 views

Can operations in a loop cause table locks?

I know a bit about locking etc, that when a transaction is working on a table, if it is altering a certain amount of data it might try and lock the entire table (I'm sure that is over simplified, but ...
0
votes
0answers
96 views

Being careful about additional records being added in a duplicate record merge?

We have a multi-user MS-Access 2003 database. Connecting to it using SQuirrel SQL, and the JDBC/ODBC bridge, I've managed to eek transactions of some sort out of JET by turning off autocommit. I've ...