Bug #77477 LOCK TABLES doc erroneously states truncate table generates error in transaction
Submitted: 25 Jun 2015 2:06 Modified: 12 Jul 2015 20:43
Reporter: Mike W Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Documentation Severity:S3 (Non-critical)
Version:5.6 OS:Any
Assigned to: CPU Architecture:Any

[25 Jun 2015 2:06] Mike W
Description:
Reference:
http://dev.mysql.com/doc/refman/5.6/en/lock-tables.html

"A table lock only protects against inappropriate reads or writes by other sessions. A session holding a WRITE lock can perform table-level operations such as DROP TABLE or TRUNCATE TABLE. For sessions holding a READ lock, DROP TABLE and TRUNCATE TABLE operations are not permitted. *TRUNCATE TABLE operations are not transaction-safe, so an error occurs if the session attempts one during an active transaction or while holding a READ lock.*"

While it is true an error will occur if you hold a READ lock. It is not true that an error occurs if one is attempted during an active transaction. As referenced here:

http://dev.mysql.com/doc/refman/5.6/en/implicit-commit.html

How to repeat:
mysql> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            0 |
+--------------+
1 row in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> truncate table t1;
Query OK, 0 rows affected (0.47 sec)

mysql> LOCK TABLES t1 READ;
Query OK, 0 rows affected (0.00 sec)

mysql> truncate table t1;
ERROR 1099 (HY000): Table 't1' was locked with a READ lock and can't be updated
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values();
Query OK, 1 row affected (0.01 sec)

mysql> insert into t1 values();
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> START TRANSACTION;
Query OK, 0 rows affected (0.00 sec)

mysql> truncate table t1;
Query OK, 0 rows affected (0.71 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
Empty set (0.00 sec)

Suggested fix:
Remove line:

"*TRUNCATE TABLE operations are not transaction-safe, so an error occurs if the session attempts one during an active transaction or while holding a READ lock.*"

The part about READ lock not allowed is mentioned in the previous sentence. 

"For sessions holding a READ lock, DROP TABLE and TRUNCATE TABLE operations are not permitted."
[2 Jul 2015 13:36] Sinisa Milivojevic
Verified.
[12 Jul 2015 20:43] Paul Dubois
Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly.