when locking a table with:
LOCK TABLE <table> WRITE;
the session does not obtain an exclusive lock. A second session can still read from the table which is locked, but cannot write or update.
A test using another MySQL server (version 5.1.50) showed the correct behaviour: an exclusive lock was obtained with "LOCK TABLE <table> WRITE;" an other session were not able to read the locked table.
ProblemType: Bug
DistroRelease: Ubuntu 10.04
Package: mysql-server-5.1 5.1.41-3ubuntu12.10
ProcVersionSignature: Ubuntu 2.6.32-29.58-generic 2.6.32.28+drm33.13
Uname: Linux 2.6.32-29-generic x86_64
NonfreeKernelModules: nvidia
Architecture: amd64
Date: Wed Mar 16 08:45:53 2011
InstallationMedia: Ubuntu 10.04.1 LTS "Lucid Lynx" - Release amd64 (20100816.1)
Logs.var.log.daemon.log:
Logs.var.log.kern.log:
MySQLConf.etc.mysql.conf.d.mysqld.safe.syslog.cnf:
[mysqld_safe]
syslog
MySQLVarLibDirListing: False
ProcEnviron:
PATH=(custom, no user)
LANG=en_US.utf8
SHELL=/bin/bash
SourcePackage: mysql-dfsg-5.1
Confirmed using the following procedure (on a fresh Lucid 10.04.2 + mysql-server-5.1 install).
Session 1:
CREATE DATABASE test;
USE test;
CREATE TABLE example (
id INT,
data VARCHAR(100)
);
INSERT INTO example VALUES (1,'test-1');
INSERT INTO example VALUES (2,'test-2');
INSERT INTO example VALUES (3,'test-3');
LOCK TABLE example WRITE;
Session 2 (already created - really important - see below):
SELECT * FROM example;
Should return all three rows (which is not correct - see http://
However I did notice that if I created the second session after the first session had acquired the lock on the table, I got the right behaviour - i.e. the SELECT blocked until the lock was released in the first session.
INSERTS seem to be OK - i.e. I was unable to insert to the table when the lock was acquired in the first session.