Blog Archive
Every post on Dewiride Developer Blogs, newest first. Pick a year or a month to narrow it down, or page through them all.
- Posts
- 1,085
- Topics
- 132
- Reading time
- 130 h
- First post
- Jun 2025
Posts 511–540 of 1,085 · November 5, 2026
Page 18 of 37
November 2026
802 postsThursday, November 5, 2026
30 of 124 posts
How to Let a MySQL User See Locks and Transactions
Your app's account can't read sys.innodb_lock_waits, data_locks or innodb_trx, and shouldn't need to. Create a separate read-only account for looking at locks: three GRANT statements give it PROCESS, performance_schema and sys, and nothing that touches your data. Tested on MySQL 9.7.
5 min readHow to Reduce a Quantity Only If There Is Enough in MySQL
Put the check in the UPDATE's WHERE, and MySQL locks the row, checks and changes it in one step, so two people can't both take the last one. ROW_COUNT tells you whether you got it. Here it is on MySQL 9.7, with two sessions racing for the last unit.
5 min readHow to Use Advisory Locks in MySQL with GET_LOCK
GET_LOCK takes a named lock that has nothing to do with any row: only one connection can hold a name at a time. It's a simple way to make sure a nightly job runs once. Here are GET_LOCK, RELEASE_LOCK and IS_USED_LOCK on MySQL 9.7, and how they differ from row locks.
5 min readHow to Find What Is Holding a Metadata Lock in MySQL
An ALTER TABLE sits at Waiting for table metadata lock, and row-lock views show nothing. sys.schema_table_lock_waits names the connection holding it, often an idle transaction that only ran a SELECT. Here's how to find it on MySQL 9.7, and what the locks look like.
5 min readHow to Use a Read-Only Transaction in MySQL
START TRANSACTION READ ONLY makes MySQL refuse any change in the transaction, and WITH CONSISTENT SNAPSHOT fixes what it sees from the first moment. Together they give a report numbers that add up, and here they are on MySQL 9.7, including what a read-only transaction may still change.
4 min readHow to Limit How Long a SELECT Runs in MySQL
max_execution_time stops a read-only SELECT after the milliseconds you choose, with ERROR 3024. Set it for a session, for the whole server, or for one query with an optimizer hint. Here's each on MySQL 9.7, and why a SLEEP returns 1 instead of an error.
4 min readHow to See Every Statement MySQL Receives with the General Query Log
The general query log records each statement MySQL receives, from every client, exactly as sent. Turn it on for a moment to see what your driver or ORM really does, read it from a table or a file, and turn it off again. Run live on MySQL 9.7 in Docker.
5 min readWhich MySQL Statements Commit a Transaction Automatically
CREATE TABLE, ALTER TABLE, TRUNCATE and others end your open MySQL transaction with a silent COMMIT, so a ROLLBACK afterwards undoes nothing. Here it is on MySQL 9.7, including a TRUNCATE that failed and committed anyway, with the statements that don't commit.
5 min readHow to Use Savepoints in MySQL
A savepoint marks a place inside a MySQL transaction, so you can undo part of it and keep the rest. Here are SAVEPOINT, ROLLBACK TO SAVEPOINT and RELEASE SAVEPOINT on MySQL 9.7, and when a savepoint stops existing. MySQL has no nested transactions, and this is what it has instead.
5 min readHow to Avoid Deadlocks in MySQL by Locking Rows in the Same Order
Two transactions that take the same rows in opposite orders can deadlock. Take them in the same order, and the second one simply waits its turn. Here are the same two transactions both ways on MySQL 9.7, with the other habits that keep deadlocks rare.
6 min readHow Gap Locks Block an INSERT in MySQL
A SELECT FOR UPDATE on a row that doesn't exist still locks something: the gap where it would be. Another session's INSERT then waits, with no conflict in sight. Here it is on MySQL 9.7, under REPEATABLE READ and READ COMMITTED, with the locks from data_locks.
5 min readWhy a MySQL UPDATE Without an Index Locks Every Row
An UPDATE that changes one row can lock the whole table, when its WHERE has no index to use. Here it is on MySQL 9.7: 15 locks to change one row of 14, another session stuck on a row nobody touched, and 2 locks after switching to an indexed column.
6 min readHow to Stop a Running Query or Connection in MySQL with KILL
KILL QUERY stops a statement and keeps the connection. KILL ends the connection and rolls back its open transaction. Here's how to find the right connection ID, what each side sees afterwards, and what an ordinary account is allowed to kill, run live on MySQL 9.7.
6 min readHow to Change the Lock Wait Timeout in MySQL
By default, a statement waits 50 seconds for a row lock before MySQL gives up with ERROR 1205. Set innodb_lock_wait_timeout for one session or the whole server, and see the part people miss: the timeout undoes only the waiting statement, and leaves the transaction open.
6 min readHow to Log Every Deadlock in MySQL
SHOW ENGINE INNODB STATUS keeps only the latest deadlock. Turn on innodb_print_all_deadlocks and MySQL writes each one to its error log, which is docker compose logs when it runs in a container. Here's the setting, the log lines, and how to keep it on after a restart.
5 min readHow to See the Last Deadlock in MySQL
SHOW ENGINE INNODB STATUS keeps a full report of the latest deadlock: both transactions, the statement each was running, the lock each held, the lock each wanted, and which one MySQL rolled back. Here's a real report from MySQL 9.7, read section by section.
6 min readHow to Find Long-Running Transactions in MySQL
A transaction left open holds its locks, and blocks everyone who needs those rows. information_schema.innodb_trx lists every open one and when it started. Here are the queries that find the old ones, say who owns them, and show what each last ran, on MySQL 9.7.
5 min readHow to See the Locks a MySQL Transaction Holds
performance_schema.data_locks lists every row lock InnoDB holds, and every one it's waiting for. Here's how to read it: the same UPDATE by primary key, by unique index and by a plain index takes very different locks, and X,REC_NOT_GAP, X and X,GAP each mean something.
6 min readHow to See Which MySQL Query Is Blocking Another
An UPDATE is stuck and nothing says why. sys.innodb_lock_waits names the waiting connection, the one blocking it, and even gives you the KILL statement. The blocker's query is often NULL, because it's idle in an open transaction, so here's how to find what it last ran.
5 min readHow to Skip Locked Rows in MySQL with SKIP LOCKED and NOWAIT
A locking read normally waits for a row that's taken. NOWAIT fails at once instead, and SKIP LOCKED leaves the taken rows out of the result. Here are both on MySQL 9.7, and the job queue that SKIP LOCKED makes simple: two workers, each picking a different job.
5 min readFOR SHARE vs FOR UPDATE in MySQL
FOR SHARE lets many transactions hold a row together, as long as none of them changes it. FOR UPDATE lets one. Here are the two side by side on MySQL 9.7, and the classic trap: two FOR SHARE readers who both try to update the row deadlock immediately.
5 min readHow to Lock a Row in MySQL with SELECT FOR UPDATE
SELECT FOR UPDATE reads the newest value of a row and locks it until your transaction ends, so you can look at it, decide, and change it with nobody getting in between. Here's what other sessions do while you hold it: plain reads go through, and locking reads wait.
5 min readREAD COMMITTED vs REPEATABLE READ in MySQL
The same two sessions under MySQL's two common isolation levels: REPEATABLE READ keeps showing the first value a transaction read, and READ COMMITTED shows the newest. Then the part that surprises people: an UPDATE and FOR UPDATE ignore the snapshot under both. Run live on MySQL 9.7.
7 min readHow to Check and Change the Transaction Isolation Level in MySQL
See which isolation level MySQL is using, and change it for one transaction, one session or the whole server. SET TRANSACTION, SET SESSION and SET GLOBAL each reach a different scope, and @@transaction_isolation doesn't always show the level a transaction really runs at.
5 min readHow Autocommit Works in MySQL
With autocommit on, every MySQL statement saves itself. Turn it off, and changes wait for a COMMIT, other sessions keep seeing the old value, and closing the client throws your work away. Here's what each setting does, run live on MySQL 9.7.
5 min readHow to Use a Transaction in MySQL: START TRANSACTION, COMMIT and ROLLBACK
Group several MySQL statements so they all happen or none do: START TRANSACTION, COMMIT and ROLLBACK, what other sessions see in the meantime, and two surprises: an error doesn't end the transaction, and a second START TRANSACTION commits the first. Every step run on MySQL 9.7.
6 min readFixed: MySQL Connection Stays on SERIALIZABLE After TransactionScope with MySql.Data
A TransactionScope with default options sends SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE, and the level stays on the pooled connection afterwards. Give the scope an explicit RepeatableRead. Real output from .NET 10 and MySQL 9.7.
5 min readFixed: "Required parameter was not provided from header" in a Minimal API
A request with no X-Request-Source header got a 400 with no explanation, and the log said Required parameter "string source" was not provided from header. Non-nullable means required. Make it nullable, or send the header. Real output from ASP.NET Core 10.
4 min readFixed: EF Core Returns the Old Value After ExecuteUpdateAsync
After ExecuteUpdateAsync, a tracked entity still had the old value, and even a new query returned it. ExecuteUpdate doesn't touch the change tracker. Project the column, use ReloadAsync, or clear the tracker. Real output from EF Core 10 and MySQL 9.7.
4 min readFixed: "error CA1305: The behavior of 'int.Parse(string)' could vary based on the current user's locale settings"
A plain int.Parse failed the build with CA1305, because the analyzers run as errors in this project. Reading configuration isn't a matter of the user's locale, so pass CultureInfo.InvariantCulture. Real output from .NET 10.
4 min read