Stack Exchange Network

Stack Exchange network consists of 174 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.

Visit Stack Exchange
Make your voice heard. Take the 2019 Developer Survey now

Questions tagged [locking]

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

0
votes
1answer
46 views

Ensuring unique value for group of rows in table with trigger and concurrent transactions

I've seen similar variations of this question asked, but nothing that tackles this particular implementation and I was wondering if it was possible to do with sql/plpgsql. Take this as more of a ...
1
vote
1answer
45 views

Is it possible to show DML that is locking some table?

I already saw here how I can list my locked objects. Suppose I, using PL/SQL Developer(pink cylinder logo one), run select * from someTable for update and didn't commit nor rollback. Now using the ...
0
votes
1answer
15 views

locking question around INSERT during UPDATE query

Say I have a large table products id, color, cost and I want to run a query to update the cost of all 'red' items UPDATE products SET cost = cost + 1 WHERE color = 'red' Let's pretend that there ...
0
votes
2answers
48 views

Delete queries blocks other concurrent transactions

DELETE queries in my production SQL server are causing deadlocks. I know that DELETE is causing this because i checked the Extended Events and checked the deadlock XML and found out that this DELETE ...
2
votes
1answer
30 views

Prevent a user from making changes to some columns in a MS SQL Server database

I'd like to prevent a user from making changes to some columns in a MS SQL Server database. I cannot lock the column as READ ONLY as there will be changes made to that column. Let me explain as best ...
0
votes
1answer
17 views

perform non locking select when updating another table

We need to perform the following (periodic) query in mariadb: update A set status='INACTIVE' where status='ACTIVE' and a.b_id in (select id from B where ... LOCK IN SHARE MODE); Unfortunately it ...
0
votes
1answer
90 views

Why select statement against a table in oracle returns ORA-04021

I have a big partitioned, local indexed table in Oracle database, what I am doing is import a big dump file into it with "impdp" Why when I issue a select statement against that table Oracle returns ...
2
votes
1answer
58 views

Deadlock with insert and delete

Deadlock graph: <deadlock> <victim-list> <victimProcess id="process21881f1bc28" /> </victim-list> <process-list> <process id="process21881f1bc28" ...
0
votes
1answer
114 views

Concurrent update queries locking rows when `where` clause doesn't match

My application sends messages to Slack. On very rare occasions we may need to retract the message going out. These retractions are handled by queueing a job for each messageId that has been created ...
2
votes
1answer
79 views

Backend Table/Row Locking Structure using CTE Queries

This question is an extension of a previous question that I asked. Table Locking When using Unions With the same query we are using that is causing blocking the various queries used in these UNIONS ...
2
votes
1answer
82 views

Table Locking When using Unions

When multiple selects are used in a UNION/UNION ALL are the table locks for each individual SELECT statement handled individually or are all table locks held until all SELECT statements are completed? ...
0
votes
2answers
24 views

Software Uses SQLite and Has Frequent DB Lock Errors. Zero Access to Code. Anything we can do?

I've written an API for a client that accepts orders from clients and converts them into print jobs that go to an HTTP bridge into the company's RIP software. On the backend, the bridge is reading and ...
2
votes
1answer
21 views

ExclusiveLock on related table due to update?

Recently ran into the following: Consider the tables created by this: create table table_a ( id bigserial not null, last_update timestamp, primary key (id) ); create table ...
-1
votes
0answers
44 views

When would exclusive lock escalate from row level to table level when I delete data from my table?

I have SQL Server 2012 (SP3-CU10) and a table, that has - 10 million rows - 11 Gb in size (6 GB data + 5 Gb index) - 20 foreign keys from 20 other tables are referencing this target table's ID column ...
1
vote
0answers
35 views

DeadLock - Two Process Locked Same Index

We have met a scenario that two process acquired exclusive lock on same index ans the same time two are requesting for shared lock to other process which eventually leads to dead lock. I am still not ...
0
votes
0answers
44 views

Concurent DELETE with condition

For example, I have articles, and authors. An article needs at least one author. But you can delete multiple authors at the same time and concurrently. So I need to prevent having a state where the ...
1
vote
1answer
77 views

Mongo queries are slow and yielding/waiting for locks thousand times

We are using mongo setup with replica sets on AWS. Setup Details: 1 Primary node: r3.8xlarge 4 secondary nodes: r5.xlarge Mongo version: 3.0.8 (WiredTiger) Database size: 358GB We have configured ...
1
vote
1answer
155 views

Will (ROWLOCK,UPDLOCK,READPAST) with ORDER BY non-clustered columns work?

I have a SQL code as below #table creation CREATE TABLE TAB ([i_task_id] [int] IDENTITY(1,1) NOT NULL, [i_priority] [tinyint] NULL, [i_status] [smallint] NULL CONSTRAINT [PK_TAB] PRIMARY KEY ...
0
votes
1answer
75 views

DROP TABLE IF EXISTS hangs on Waiting for table metadata lock

I'm using a dockerized MySQL 5.7.13 as part of an integration test of an application. The way the system works is as follows: I start up the main application. It opens up a connection to MySQL, but ...
1
vote
0answers
43 views

PostgreSQL : FOR SHARE & FOR UPDATE priority

Here is my situation: I have 2 types of actions A & B, each read or update a single ressource. Actions A should be able to be executed concurrently. Actions A should wait for Actions B to be ...
0
votes
0answers
43 views

What happens if two processes attempt to update the same row in the same Postgres table at the same time

To clarify the question, what is supposed to happen if two different processes attempt to UPDATE the same row (uniquely identified by primary key) at the same time? - And does it make any difference ...
-1
votes
0answers
30 views

Type of locks and conflicts between them in MySQL and PostgreSQL

I'm doing some research of the existing type of locks both on MySQL and PostgreSQL databases. Consider reasonabily recent versions. PostgreSQL current and MySQL 5.7, for example. I came accross these ...
0
votes
1answer
93 views

What does innodb_table_locks setting do exactly?

Can someone please explain how this setting affects the locking behaviour in innodb (with an example). I ran numerous experiments with different configurations of (autocommit, innodb_table_locks, LOCK ...
0
votes
1answer
19 views

Synchronising batch delete from multiple applications with advisory lock in Postgres

We are moving our application from one-instance to multiple instances (all connected to the same Postgres database) in order to increase resiliency of one of our services, and I am now facing a synch ...
0
votes
1answer
102 views

sp_getapplock not working in multiple Connection [closed]

I wrote below script and run it in two tab of SQL Server management studio: DECLARE @RC INT Begin TRAN Exec @RC =sp_getapplock @Resource='MyLock', @LockMode='Exclusive', @LockOwner='Transaction', ...
2
votes
2answers
94 views

Strange deadlock with multi-column index with innodb on MySQL

I'm seeing some odd (to me) behavior in MySQL. Let's start with the table that I'll be talking about. CREATE TABLE `active_foo` ( `active_foo_id` bigint(20) NOT NULL AUTO_INCREMENT, `c_id` ...
1
vote
1answer
77 views

How can I drop a column from a large (700M rows) hot (multiple transactions per second) table with minimal downtime?

My research suggests I can drop a column on a table in a matter of moments (provided I obtain an exclusive lock) because it is only a metadata change. The table has two different services ...
0
votes
1answer
40 views

Reporting on a SQL DB Server with many write operations and few read operations [closed]

I know this is a generic question, but I have to clarify some basic questions. I know that when a write is happening to a SQL Server database table, a read to that table will have to wait. My question ...
1
vote
1answer
17 views

How to explain/introspect what locks a DDL operation will need (Postgres)

I would like to have some way of inspecting what locks a DDL operation will need to acquire. Preferably something generic enough that I don't need to personally know/encode knowledge of all of the ...
1
vote
2answers
333 views

Could not obtain exclusive lock on database 'model' - who is using it?

when trying to run the following query in order to create a new database: CREATE DATABASE [Lunch] CONTAINMENT = NONE ON PRIMARY ( NAME = N'Lunch', FILENAME = N'E:\Data Files\...
2
votes
2answers
73 views

Allow only one reader to a table

I have a simple Postgres table which port numbers to listen on. If one port is used it can't be used again. However due to multiple worker/threads they many times read simultaneously. I want that ...
0
votes
1answer
35 views

How many row level locks can I have to same row?

I need some insights on row-level locking in Postgres. How many shared locks (FOR SHARE) can be concurrently on a particular tuple in a relation . Will have many shared locks on same tuple cause any ...
4
votes
1answer
68 views

How locks in Postgres behave when quitting the JVM abnormally

I have a table 'car', as follows Table "public.car" Column | Type | Collation | Nullable | Default ----------+--------+-----------+----------+--------- car_no | bigint | ...
2
votes
1answer
52 views

Row Locking in Postgres

I have table named 'car' Table "db145004db.car" Column | Type | Collation | Nullable | Default ----------+--------+-----------+----------+--------- car_no | bigint | |...
1
vote
1answer
31 views

Missing rows (gaps) on range read from the end of the table

On a data processing pipeline, getting gaps in returned data. Process A - inserts sequentially into a table. Table has an auto-increment primary key id. Process B - selects max(id) [into X] and then ...
0
votes
2answers
130 views

MySQL 5.1 - long running select locking the table and not allowing inserts

Platform - MySQL 5.1, MyISAM engine We reached max on int (id, auto increment) in our Prod system. Changed the type to BigInt. Even since this change was made, long running selects are locking the ...
0
votes
1answer
53 views

Select data from a table while update is running within a transaction in SQL Server

The following sql query is used to update a specific row inside transaction. BEGIN TRANSACTION; Update [dbo].[StudentExamAttendanceDetails] SET StudentFeedBack = 1 Where Id = 4883174 COMMIT ...
3
votes
1answer
124 views

Why does MySQL forbid LOCK TABLES in stored procedures?

I have read through several sections of the MySQL reference manual, and while it states repeatedly that LOCK TABLES cannot be used in a stored procedure, I have never found an explanation for the ...
-1
votes
1answer
57 views

SQL Server Intellectual Property (Protecting DB Structure and Relations)

I would like to know is there any update about protecting DB Structure? We deliver a licensed app that uses SQL as its main DBMS and we would like to lock the database structure (schema). I know I ...
0
votes
1answer
77 views

Lock a table in Sybase ASE 15.5

I would like to lock a table to only allow users to perform read operation in Sybase ASE 15.5, how can I do it? I have tried the below command, but seems there is some confusion. Any idea? Session A:...
-2
votes
1answer
76 views

RID and Key lock on same object by a Select and Delete query

To be specific how did the select query (spid 128, on the left) is able to acquire a S (shared) lock on Index but not on the row? Also, how did Delete query (SPID 121, on the right) is able to get X ...
0
votes
0answers
43 views

SQL Server locking question

Could you please clarify how SQL Server locking works internally during an index seek? Lets look into such case. We have a table TEST that consists of three columns Col1, Col2, Col3. Col1 has a ...
2
votes
3answers
102 views

Encounter deadlock even when there is no relation between tables

I've been studying about deadlock and it's different causes . I found an example regarding this issue but I could not understand the cause of the deadlock exactly because two DIFFERENT tables are ...
0
votes
1answer
60 views

Data locking question

I'm learning locking basics and I have some doubts about how it works. I'm using following query to examine transaction locks : SELECT request_session_id AS spid , DB_NAME(resource_database_id) AS ...
0
votes
1answer
383 views

Benefit of using WITH (TABLOCK, XLOCK) when inserting one line in a table?

I found in some legacy code (originally SQLServer 2008) INSERT hints that I'm not sure to understand. I have a table (storing many rows) and some procedures to fill this table. We insert rows one by ...
1
vote
2answers
595 views

SQL Server 2012 - How to view the locks' log? [closed]

I'm using SQL Server 2012 Express Edition, may I know how can I view all the lock/block processes which were recorded in my database? The reason I want to view the previous locked/blocked processes is ...
4
votes
2answers
300 views

Is there any risk in SELECT … FOR UPDATE outside transaction scope in MySQL?

In a project where I work the sql for selecting objects does always select for update, whether the context is a transaction or not. My question is if it has any risk, or performance issue, or chance ...
0
votes
1answer
761 views

out of shared memory error when doing pg_dump

I have a postgres database with 3 schemas and around 1200 tables. When I try to take a schema only pg_dump of it using the command, pg_dump -h localhost -p 5432 -U postgres -d my_test_db -s, I ...
0
votes
2answers
56 views

Is it possible to make a SELECT wait until an ongoing UPDATE finishes?

Suppose that I am implementing a bank account logic using PostgreSQL. At some point, I will want to check if the user has sufficient balance to perform an operation. However, the user's balance might ...
0
votes
1answer
29 views

Controlled Transaction Blocking - Resume Behavior

When several procedures are blocked on a shared resource, how does sql server pick which one to resume? Example 1 proc sp_a begin tran select 1 from dbo.lock_tbl with ( tbllockx ) delay wait ...