Tagged Questions
1
vote
1answer
38 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 ...
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
77 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
64 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
101 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
788 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: ...
0
votes
0answers
42 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 ...
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 ...
0
votes
0answers
219 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 ...
0
votes
1answer
116 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) ...
5
votes
4answers
494 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 ...
4
votes
1answer
121 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
823 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 ...
6
votes
1answer
434 views
How do I swap tables in MySQL?
Suppose, I have a table foo, which contains some statistics that are computed every now and then. It is heavily used by other queries.
That's why I want to compute more recent statistics in foo_new ...
3
votes
2answers
215 views
Writing transactions to .MYD file to take Backup
I'm trying to backup MySQL DB by archiving the data directory of required databases.
I'm doing FLUSH TABLES WITH READ LOCK before taking the backup, but even then some of the transactions were not ...
4
votes
2answers
5k views
way to prevent queries from waiting for table level lock
We've encountered a problem after moving the database of our customer to an extra server. This should have had positive effects on the site's performance, but there is a problem with table locking in ...
1
vote
2answers
900 views
Cannot write to mysql database while select query in process
Running Mysql 5.1.57 on InnoDB, and have a database locking issue.
I have two separate sessions connected to a single database MySQL. On the first session, I run a long SELECT query (technically a ...
0
votes
1answer
599 views
Lock wait timeout exceeded on insert despite low lock_time in the slow query log
Our Java/Spring application reports occasional lock wait timeouts on single record INSERTs to a MySQL 5.5 InnoDB table. Here are a few slow query log records for some of the failed INSERTs.
# ...
3
votes
1answer
1k views
MySQL get next unique value without auto increment
I have an MySQL database (InnoDB) that I'm developing for tracking Work Orders at my organization. There are multiple 'sites', and each site has work order numbers starting at 100.
WorkorderID ...
2
votes
1answer
280 views
In InnoDB, does a Transaction imply any implicit locking of a table?
The Isolation level is REPEATABLE_READ.
The logic is as under:
Transaction begins
Read data from Table A
If (Table A has Any Data) End Transaction and exit
If Table A has No Data, Proceed further
...
2
votes
2answers
489 views
Will an ALTER TABLE ADD COLUMN on a slave break replication?
I need to add a last_modified column to a MyISAM table tbl_items. The crux of this problem is that tbl_items houses several gigabytes of data. Also of note, I am using a master-slave deployment with ...
3
votes
2answers
572 views
MySQL Locks while CREATE TABLE AS SELECT
I am running the following (dummy) query
CREATE TABLE large_temp_table AS
SELECT a.*, b.*, c.*
FROM a
LEFT JOIN b ON a.foo = b.foo
LEFT JOIN c ON a.bar = c.bar
Suppose the query ...
2
votes
1answer
1k views
How can I find who holds the lock based on the hex-dumped?
I've been reading the Diagnosing MySQL InnoDB Locks article. Karl E. Jørgensen writes on 2008 so I'm consfusing it is in effect.
I would like to supply a snippet of the SHOW ENGINE INNODB STATUS:
...
2
votes
1answer
1k views
Why can't I get a lock on an InnoDB table?
I have problem with lock table command: LOCK TABLE v3_cam_date WRITE;
the command always running with state: "Waiting for table metadata lock".
Try to find out which thread was hold the lock on ...
1
vote
1answer
469 views
MySQL hang in load data infile command
From the link: LOAD DATA (400k rows) INFILE takes about 7 minutes, cannot kill the "logging slow query" process?
I had changed the command order (unlock before commit + replace some ...
2
votes
2answers
2k views
LOAD DATA (400k rows) INFILE takes about 7 minutes, cannot kill the “logging slow query” process?
MySQL version: 5.5.13
The query that I'm using:
SET AUTOCOMMIT=0;
LOAD DATA INFILE '/data10/select_into.outfile/v3_zone_date.out' INTO TABLE v3_zone_date FIELDS TERMINATED BY ',';
COMMIT;
...
3
votes
1answer
374 views
mysqladmin debug writes out Current locks but PROCESSLIST and INNODB STATUS don't show any
I'm using the 1.1.8 mysql-cacti-templates to gather metrics on MySQL 5.0.77 (CentOS 5.7).
Both the InnoDB Current Lock Waits and InnoDB Lock Structures graphs are charting with Cur[rent] values but ...
2
votes
1answer
468 views
LOCK IN SHARE MODE
On dev mysql LOCK IN SHARE MODE is described as...
SELECT ... LOCK IN SHARE MODE sets a shared mode lock on the rows read. A shared mode lock enables other sessions to read the rows but not to modify ...
1
vote
3answers
442 views
Improve `Update` performance (rows locking issue)
I am running 30 scripts (PHP CLI) on Linux , each script are updating (loop) the data in the MySQL database.
When I typed 'mysqladmin proc' in terminal, I can see a lot of rows has been locked for ...
1
vote
1answer
1k views
MySQL MyISAM Locking question
It was my understanding that MyISAM uses table locking during inserts and updates.
To me that meant that read and write by other queries on that table is disallowed until the first insertion or update ...
4
votes
4answers
1k views
Benefits of table level locking
What are the benefits of table level locking which is used by the MyISAM storage engine? Row level locking has lots of benefits like concurrent updates and reads that do not lock the table.
Edit
Its ...
2
votes
3answers
5k views
Set “lock wait timeout” in MySQL
Where would I set the maximum time a query will wait for a lock in MySQL 5.0.68 before timing out?
2
votes
1answer
697 views
MySQL : “FLUSH TABLES WITH READ LOCK” started automatically
I would like to understand how this happened. I was running a query that would take a long time, but should not lock up any table. However, my dbs were practically down - it seems like it was being ...
