A mechanism for managing concurrent access to shared data or resources by granting temporarily exclusive access to the processes requesting the lock.
1
vote
1answer
36 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 ...
-1
votes
0answers
42 views
Lck_m_s suspended queries after running stored proc [closed]
Hi all can anyone advise if I am running cursor using a stores proc which do select and update.
Then if I am running a process which at the same time may call the same stored proc multiple times ...
0
votes
1answer
62 views
Does a single, simple select query creates 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
23 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
32 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
325 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](
...
1
vote
0answers
63 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
1answer
39 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 ...
1
vote
1answer
54 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
52 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
125 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
37 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
154 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
76 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
63 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
100 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 ...
1
vote
3answers
246 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
70 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
160 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
765 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: ...
2
votes
2answers
182 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
355 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
40 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 ...
0
votes
1answer
52 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
102 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
42 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
50 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
218 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
86 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
378 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
1answer
115 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) ...
3
votes
1answer
756 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 ...
5
votes
4answers
488 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
121 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
239 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
267 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
120 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 ...
2
votes
2answers
809 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
110 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; ...
2
votes
2answers
622 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
0answers
623 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
200 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 ...
7
votes
4answers
721 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
353 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 ...
0
votes
0answers
45 views
Deleting a ghost record in a Pervasive PSQL v10 SP3 database
One of my users killed the application he was working on while adding a record. Now the application thinks the record exists and I would like to get rid of it.
I know the user ID and I know the ...
4
votes
2answers
154 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
1answer
740 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 ...
6
votes
3answers
265 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
484 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
590 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 ...
