Questions tagged [table-locking]
104 questions
1
vote
1 answer
Unable to apply read or write lock manually into Innodb table via mysql command line
I am trying to test a scenario in table locked cases.
I tried to apply read and write locks like these -
mysql> lock tables table_name write;
Query OK, 0 rows affected (0.01 sec)
But was able to write after this -
mysql> insert into table_name…
Sandeepan Nath
- 9,966
- 17
- 86
- 144
1
vote
1 answer
Why my statement failed with resource busy exception?
As a part of an ETL process,I have this exchange subpartition statement:
ALTER TABLE DWH.QV_FACT_AMS EXCHANGE SUBPARTITION P08_2018_300_SALES WITH TABLE DWH.STG_QV_FACT_AMS;
(each run with different subpartition but the same 2 tables).
We started…
user2671057
- 1,411
- 2
- 25
- 43
1
vote
1 answer
MySql - innodb - How row level lock on composite key worked in simple words
Folks, I read the reference manual of MySQL 5.5 but its very tricky to understand row level lockings. I am using MySQL innoDB and want to understand the row level locking on a table which has composite primary key. Both keys are identifying keys of…
Mubasher
- 943
- 1
- 13
- 36
1
vote
3 answers
Teradata - how to select without locking writers? (LOCKING ROW FOR ACCESS vs. LOCKING TABLE FOR ACCESS)
I am developing an application which fetches some data from a Teradata DWH. DWH developers told me to use LOCK ROW FOR ACCESS before all SELECT queries to avoid delaying writes to that table(s).
Being very familiar with MS SQL Servers's WITH(NOLOCK)…
Alexei - check Codidact
- 22,016
- 16
- 145
- 164
1
vote
3 answers
how to lock a DB table or a range of rows for writing?
I have a simple table with a primary key. Most of the read operations fetch one row by the exact value of the key.
The data in each row maintains some relationship with rows before and after it in the key order. So when I insert a new row I need to…
davka
- 13,974
- 11
- 61
- 86
1
vote
0 answers
Concurrent MYSQL procedure calls returning different results with an unrelated SELECT statement
I'm experiencing some very strange transactional behaviour in my MYSQL application.
I've managed to reduce the problem down to a small isolated test case, the code for which I’ve included below:
-- Setup a new environment
SET GLOBAL TRANSACTION…
Luke
- 11
- 2
1
vote
0 answers
How to check if table is empty during metadata lock in mysql
I need to maintain ping service that has to check if particular table is empty every 20 seconds. However, from time to time a script performs work on that table which results in metadata lock. This script can sometimes run for hours. This results in…
Tofig Hasanov
- 3,303
- 10
- 51
- 81
1
vote
2 answers
Locking a SQL Server table to prevent inserts
I am writing this procedure in SQL Server 2008 R2:
CREATE Procedure [dbo].[SetLocalSeed](@tableName nvarchar(128))
AS
BEGIN
-- Find the primary key column name
DECLARE @pkName NVARCHAR(128)
SELECT @pkName = COLUMN_NAME
FROM…
user4013241
- 55
- 1
- 5
1
vote
2 answers
Prevent parallel execution using a table lock (MySQL)
I have a MySQL table called cronjobs which holds entires for every cronjob needed (e.g. delete old emails, update profile age, and so on). For every cronjob there is a defined code block which gets executed if the cronjob is due (I got different…
Freddy
- 349
- 1
- 2
- 12
1
vote
0 answers
InnoDB & Isolation Levels - Aren't repeatable reads a bad thing?
I was reading about InnoDB's isolation levels and it makes sense for the most part, but what I don't get is why are unrepeatable reads a bad thing? Shouldn't it be the other way around?
For examples:
So say we had a stock column for selling products…
Brett
- 19,449
- 54
- 157
- 290
1
vote
4 answers
Record deleted during a transaction
I have a system that handles many queries per second. I code my system with mysql and PHP.
My problem is mysqli transaction still commit the transaction even the record is deleted by other user at the same time , all my table are using InnoDB.
This…
Leon Armstrong
- 1,285
- 3
- 16
- 41
1
vote
1 answer
Column Store Indexes Update With table locking
I'm currently working on a tricky task.
Background:
I have a MSSQL 2012 database with multiple tables and a huge amount
of rows. Since searching takes quite a while in this database, i
search for ways to improve the queries. (And yes I'm using…
Anubis
- 36
- 4
1
vote
1 answer
Sybase/JDBC: how to detect reorgs or exclusive locks?
We use Sybase ASE (15.5) server as our DB and are having strange, intermittent SPID blocking issues that I am trying to detect and mitigate programmatically at the application-layer.
Sybase allows you to schedule so-called "reorgs" which from what I…
user1768830
1
vote
3 answers
Serializing INSERTs
If the answer to this question depends on the DMBS, I'd be interested to hear the answer for Oracle 11g or higher and SQL Server 2012.
We have a table which has a foreign key that references itself:
CREATE TABLE Versions (
Id INT IDENTITY(1,1)…
Jonas Sourlier
- 13,684
- 16
- 77
- 148
1
vote
1 answer
Large UPDATE [...] SELECT FROM causes parallel UPDATE/DELETE to die
I'm trying to explain my problem by examples. I've a long running statement like
UPDATE
INNER JOIN ON [...]
LEFT JOIN ON [...]
LEFT JOIN ON [...]
LEFT JOIN ON [...]
SET .=X
WHERE…
rabudde
- 7,498
- 6
- 53
- 91