103 questions
0
votes
0
answers
82
views
Magento2 database table locking error causing site to go offline several times a day
Several times a day a database insert query runs that seems to take a long time and locks up a table, and this in turn causes our site to go offline (error 503). It does not come back up until certain ...
- 1
0
votes
2
answers
530
views
How to acquire exclusive lock in replicated mysql and clustered servers?
I have this scenario:
My php web application is deployed in a clustered environment, it uses a replicated mariadb database with galera.
It has to acquire an exclusive lock on database row resource and ...
- 9,625
1
vote
0
answers
277
views
MS Access - Table locked by another person or process, after closing form
I am running a sub to 'snapshot' data from a pass-through query (which is slow) and store the results to a table. It is supposed to delete the table, then re-create it to be an updated snapshot of the ...
- 2,885
0
votes
1
answer
53
views
Doing FOR UPDATE and JOIN when using ROW SHARE lock?
According to PostgreSQL documentation, the ROW SHARE lock works like this:
The SELECT command acquires a lock of this mode on all tables on which
one of the FOR UPDATE FOR NO KEY UPDATE, FOR SHARE, ...
0
votes
0
answers
154
views
Lock escalation on update statement that uses a primary key
I inherited a database that seems to have a LOT of lock escalation going on. I ran a trace and it's full of escalation events.
Some of it is caused by things like BULK INSERTS. I can understand how ...
- 127
0
votes
0
answers
473
views
SQL Server table data deletion while insertion is in progress in another session
I have a table (TableA).
There is a probability that more than one process may try to delete data from TableA and at the same time more than one process may try to insert data into same table i.e. ...
0
votes
0
answers
134
views
Does stored procedure with select statement called from another stored procedure with transaction lock the table?
I have a stored procedure calling another stored procedure. The outer stored procedure got tran while the inner stored procedure has no tran just select statement. Does the table in the inner tran ...
- 1
0
votes
1
answer
2k
views
How to remove a lock from a table in a Synapse Dedicated Pool?
There has been a lock on 2 different tables since last night. What is the query to remove a lock from a table in a Synapse Dedicated Pool?
Thanks in advance
- 76
0
votes
1
answer
1k
views
Preventing/Reducing deadlocks within a django application
I've been having a few issues recently working with a large MySQL database within django, MySQL seems to be having issues with deadlocks.
I keep getting the error:
(1213, 'Deadlock found when trying ...
0
votes
0
answers
354
views
unlocking a table that someone else locked
Let's say a coworker does LOCK TABLES table_name and then leaves for the day without unlocking the table. Maybe their computer is locked so you can't just kill the process on their computer. Maybe you ...
- 17k
1
vote
1
answer
1k
views
Difference between a row lock and a table lock in Oracle database
What is the difference between row locking and table locking in Oracle Database? Would a for loop with the UPDATE statement trigger a table lock?
- 558
0
votes
1
answer
622
views
Temporal Table and Table locks
I am thinking of using a temporal table solution for one of our application, and want to read historic data while new data is being written. Can this cause table locks? I have millions of rows to ...
- 281
0
votes
1
answer
485
views
Does InnoDB block on a SELECT that joins with a locked table?
Suppose, I run this query, which locks the entire table in InnoDB until it finishes:
Update UserDetails set balance = 0
Then I run this query:
Select * from User inner join UserDetails on (User.id = ...
- 3
0
votes
2
answers
3k
views
Does a general InnoDB UPDATE lock the entire table and block incoming specific UPDATEs?
Suppose I ran this statement:
UPDATE Employees set country='AU'
On an InnoDB table, Employees, with about 10 million rows.
This table is also actively being updated by other users through SQL queries ...
- 3
2
votes
1
answer
6k
views
Create index in huge MariaDB production database without table locking
I have a table with 202M records where I need to add a few indexes and I can't find it anywhere (or maybe I don't understand the lingo) if that is possible to do, without locking, in MariaDB 10.3.
I ...
3
votes
2
answers
1k
views
Mysql 8 metadata locks on foreign key tables during alter
When I do alter table commands Im getting lots of dead locks. Where commands are waiting for metadata lock on tables that are not part of the alter table statement. The tables are linked through a ...
- 4,346
0
votes
1
answer
779
views
Oracle how child table behave when data from parent table is modified?
Scenario: We have table A (Parent Table) referring to table B (Child Table) and also we have Foreign Key Index for every Foreign Key.
Operation: Now when any user is deleting a row from table A then ...
-1
votes
1
answer
171
views
Is mixing transactions and table locks unrealistic in MySQL 5.7?
I am looking to mix table locks and transactions in an API. I have selected MySQL 5.7 as the storage engine, though I am not particularly committed to any specific RDBMS. I am interested in seeing ...
- 20.2k
1
vote
1
answer
4k
views
How to prevent table locking with multiple users writing and reading from table at the same time?
I have a program I wrote for a group that is constantly writing, reading and deleting per session per user to our SQL server. I do not need to worry about what is being written or deleted as all the ...
- 15.3k
4
votes
2
answers
7k
views
Multiple Update statements on a single row at exactly same time
I've a scenario where I get a cab request from a customer and I send pickup request to multiple drivers. Now Imagine if two of the drivers who got the request hit the "Accept" button at the exact same ...
- 650
1
vote
1
answer
558
views
PDO General error: 2014 Cannot execute queries while other unbuffered queries are active when trying to LOCK TABLES
I am updating someone's old old code from using mysql() to using PDO. In one place they have some LOCK TABLES commands to prevent two users from accessing the same data at the same time. When running ...
- 2,745
0
votes
2
answers
2k
views
innodb full table lock during simple insert
Our code does
insert into user (email, name) values ((),()) // 1000 rows at a time, without specifying primary key value
Such inserts run in parallel on multiple server and with high concurrency (...
- 890
1
vote
1
answer
114
views
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 ...
- 10.3k
2
votes
0
answers
73
views
Locking of tables in MySQL seems that they aren't able to handle the parallel request?
I locked the table using FOR UPDATE with an IF validation. However, it seems that they aren't able to handle the parallel request?
I have two samples of stored procedures. I tested these using cURL ...
- 21
1
vote
1
answer
4k
views
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 ...
- 1,553
20
votes
2
answers
26k
views
MySQL 5.6 - table locks even when ALGORITHM=inplace is used
I'm running the following ALTER command on a MySQL 5.6 database on a large table with 60 million rows:
ALTER TABLE `large_table` ADD COLUMN `note` longtext NULL,
ALGORITHM=INPLACE, LOCK=NONE;
...
- 13.9k
0
votes
1
answer
103
views
mySql innoDB write queries locked for long time by read queries
I have a mySql innoDB database with a table (with auto increment primary key). I have many processes performing a read operation on the table. I have other (few) processes that need to write in the ...
- 1,493
2
votes
1
answer
206
views
postgresql db table locking or row locking multi query execution
I would like to understand how postgreSQL is doing multi query execution for example i have a DB that there are lots of insert queries running for example 20-40 per minute and lots of select queries ...
- 1,511
0
votes
2
answers
3k
views
MYSQL 5.6.10 ANALYZE TABLE lock issue
I ran the analyze table command on production mysql db without knowing it would prevent me from selecting the contents of the table. This caused production site to go down :( How long can it take for ...
- 11
4
votes
1
answer
5k
views
Difference between table and row locks
I'm studying about MySQL and how it works, and something confuses me and I don't find any clear explanation on the web about this.
What exactly is the difference between row and table locks? One ...
- 55
1
vote
1
answer
495
views
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 ...
- 950
0
votes
1
answer
3k
views
mysqldump with --single-transaction and avoid DDL statements without table locking
I am looking for a solution to take logical backup of MySQL database with mysqldump with single-transaction option and also avoiding any DDL statements during backup without locking any table. ...
- 11
3
votes
2
answers
985
views
Innodb Transaction or tables lock?
I have a simple table which is an email queue.
CREATE TABLE `emails_queue_batch` (
`eq_log_id` int(11) NOT NULL DEFAULT '0',
`eq_to` varchar(120) CHARACTER SET utf8 DEFAULT NULL,
`eq_bcc` ...
- 25.5k
25
votes
3
answers
29k
views
Will laravel database transaction lock table?
I use laravel5.5's database transaction for online payment application. I have a company_account table to record each payment(type, amount, create_at, gross_income). I need to access the last record'...
- 28.6k
-1
votes
2
answers
2k
views
Prevent race condition and lock tables in hotel booking system PHP/MySQL
I am building a hotel style reservation system and I am loosing the way. I've got stuck at the exactly reservation moment when to add the reservation and I am concerned about come situations.
My flow ...
- 45
1
vote
3
answers
15k
views
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).
...
- 23.2k
0
votes
0
answers
137
views
Routine to lock an SQL Server Table Asynchronously
I want to write a routine which should be able to lock a table for a specified amount of time and return asynchronously before any waiting.
I have written the following code
public static void ...
- 1,070
1
vote
0
answers
148
views
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:
-- ...
- 11
0
votes
0
answers
237
views
Pattern for simple table lock in SQL Server
I've been reading the MSDN about transaction isolation levels and table hints in an effort to find out what I need to do to exclusively lock a table while I perform a 2 step insert in SQL Server. I've ...
- 4,487
0
votes
2
answers
777
views
Application becomes unresponsive because of oracle lock
The app is connected to an oracle 11G database using the JDBC driver provided from the official website. When many users (Around 50) from different instances connected to the same schema start using ...
- 11
1
vote
0
answers
100
views
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 ...
- 3,779
8
votes
1
answer
44k
views
Mysql - Serialization failure: 1213 Deadlock found when try to get lock; try restarting transaction
I have menus, categories and products tables. I am using mysql 5.5, All tables are innoDB and in all cases id is the primary key (int) with autoincrement.
menus table
id, name, status
categories ...
- 9,327
6
votes
2
answers
4k
views
Mysql transaction with table locking
I need to use table locking (write) and along with that update a few tables, so I need transactions at the same time, as the locking is not transaction-safe.
From mysql documentation I read the ...
- 9,327
3
votes
1
answer
1k
views
PHP Mysql PDO and unlock table
Should I manage the table unlock in case of an error in php (try/catch)? Or the lock is automatically released by PDO object ad the end of the script?
- 9,625
0
votes
0
answers
71
views
Can a Table Lock eliminate a deadlock or make it worse Oracle
We have a situation in which an Oracle Database is having deadlocks issues.
I said that perhaps we should lock into some table locks, and a guys response to me was
"Unless I am misunderstanding ...
user5154099
0
votes
0
answers
56
views
PDO table locking on a table not specified?
Words are hard today, and I'm trying to write this up clearly so that I can help you help me. Some overkill exists below, ymmv.
I'm migrating about 48m records out of table1 into table2, both ...
- 1
0
votes
1
answer
36
views
Explicit table locking to disable DELETES?
Using Oracle 11gR2:
We already have a process that cleans up particular tables by deleting records from them that are past a specified retention date (based on the comparison between the timestamp ...
- 1
7
votes
1
answer
4k
views
Lock Table with exclusive in H2 database
I am having trouble to convert Oracle syntax to H2 syntax:
For example (Oracle):
LOCK TABLE CAR_CHIP_ID_LOCK IN EXCLUSIVE MODE
In H2 it results in a (syntax error)
How do I change the oracle ...
- 2,053
0
votes
0
answers
308
views
MS SQL Server monitoring table locks
I would like to ask whether there's some kind of a mechanism that would allow me to monitor, capture and write (e.g. to a table) information about table locks that occured.
What I found so far are ...
- 1,102
1
vote
2
answers
3k
views
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)
...
- 55