对一个表加读锁然后再加写锁有什么意义么？

如果一个线程获得在一个表上的read锁，那么该线程和所有其他线程只能从表中读数据，不能进行任何写操作。
如果一个线程在一个表上得到一个 WRITE 锁，那么只有拥有这个锁的线程可以从表中读取和写表。可以看出来读写都加锁的话那么结果是只有本线程是可以读数据的

我刚才在mysql的命令行界面试了一下，能读能写。但是如果先加写锁再加读锁的话，只能读不能写。
mysql> use test;
Database changed
mysql> lock table test read;
Query OK, 0 rows affected (0.00 sec)mysql> lock table test write;
Query OK, 0 rows affected (0.00 sec)mysql> select * from test;
+----+----------+
| id | content  |
+----+----------+
|  1 | asdfasdf |
+----+----------+
1 row in set (0.00 sec)mysql> insert into test values(NULL,'saldjfa');
Query OK, 1 row affected (0.00 sec)mysql> select * from test;
+----+----------+
| id | content  |
+----+----------+
|  1 | asdfasdf |
|  2 | saldjfa  |
+----+----------+
2 rows in set (0.00 sec)mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)mysql> lock tables test write;
Query OK, 0 rows affected (0.00 sec)mysql> lock tables test read;
Query OK, 0 rows affected (0.00 sec)mysql> insert into test values(NULL, 'askdjflas');
ERROR 1099 (HY000): Table 'test' was locked with a READ lock and can't be update
d
mysql> select * from test;
+----+----------+
| id | content  |
+----+----------+
|  1 | asdfasdf |
|  2 | saldjfa  |
+----+----------+
2 rows in set (0.00 sec)mysql> lock tables test write;
Query OK, 0 rows affected (0.00 sec)mysql> insert into test values(NULL, 'askdjflas');
Query OK, 1 row affected (0.00 sec)mysql> select * from test;
+----+-----------+
| id | content   |
+----+-----------+
|  1 | asdfasdf  |
|  2 | saldjfa   |
|  3 | askdjflas |
+----+-----------+
3 rows in set (0.00 sec)mysql> unlock tables;
Query OK, 0 rows affected (0.00 sec)mysql>

Rules for Lock Acquisition To acquire table locks within the current session, use the LOCK TABLES statement. The following lock types are available: READ [LOCAL] lock: The session that holds the lock can read the table (but not write it). Multiple sessions can acquire a READ lock for the table at the same time. Other sessions can read the table without explicitly acquiring a READ lock. The LOCAL modifier enables nonconflicting INSERT statements (concurrent inserts) by other sessions to execute while the lock is held. (See Section 7.3.3, “Concurrent Inserts”.) However, READ LOCAL cannot be used if you are going to manipulate the database using processes external to the server while you hold the lock. For InnoDB tables, READ LOCAL is the same as READ. [LOW_PRIORITY] WRITE lock: The session that holds the lock can read and write the table. Only the session that holds the lock can access the table. No other session can access it until the lock is released. Lock requests for the table by other sessions block while the WRITE lock is held. The LOW_PRIORITY modifier affects lock scheduling if the WRITE lock request must wait, as described later. If the LOCK TABLES statement must wait due to locks held by other sessions on any of the tables, it blocks until all locks can be acquired. A session that requires locks must acquire all the locks that it needs in a single LOCK TABLES statement. While the locks thus obtained are held, the session can access only the locked tables. For example, in the following sequence of statements, an error occurs for the attempt to access t2 because it was not locked in the LOCK TABLES statement: mysql> LOCK TABLES t1 READ;
mysql> SELECT COUNT(*) FROM t1;
+----------+
| COUNT(*) |
+----------+
| 3 |
+----------+
mysql> SELECT COUNT(*) FROM t2;
ERROR 1100 (HY000): Table 't2' was not locked with LOCK TABLESTables in the INFORMATION_SCHEMA database are an exception. They can be accessed without being locked explicitly even while a session holds table locks obtained with LOCK TABLES. You cannot refer to a locked table multiple times in a single query using the same name. Use aliases instead, and obtain a separate lock for the table and each alias: mysql> LOCK TABLE t WRITE, t AS t1 READ;
mysql> INSERT INTO t SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> INSERT INTO t SELECT * FROM t AS t1;The error occurs for the first INSERT because there are two references to the same name for a locked table. The second INSERT succeeds because the references to the table use different names. If your statements refer to a table by means of an alias, you must lock the table using that same alias. It does not work to lock the table without specifying the alias: mysql> LOCK TABLE t READ;
mysql> SELECT * FROM t AS myalias;
ERROR 1100: Table 'myalias' was not locked with LOCK TABLESConversely, if you lock a table using an alias, you must refer to it in your statements using that alias: mysql> LOCK TABLE t AS myalias READ;
mysql> SELECT * FROM t;
ERROR 1100: Table 't' was not locked with LOCK TABLES
mysql> SELECT * FROM t AS myalias;WRITE locks normally have higher priority than READ locks to ensure that updates are processed as soon as possible. This means that if one session obtains a READ lock and then another session requests a WRITE lock, subsequent READ lock requests wait until the session that requested the WRITE lock has obtained the lock and released it. A request for a LOW_PRIORITY WRITE lock, by contrast, allows subsequent READ lock requests by other sessions to be satisfied first if they occur while the LOW_PRIORITY WRITE request is waiting. You should use LOW_PRIORITY WRITE locks only if you are sure that eventually there will be a time when no sessions have a READ lock. For InnoDB tables in transactional mode (autocommit = 0), a waiting LOW_PRIORITY WRITE lock acts like a regular WRITE lock and causes subsequent READ lock requests to wait. LOCK TABLES acquires locks as follows: Sort all tables to be locked in an internally defined order. From the user standpoint, this order is undefined. If a table is to be locked with a read and a write lock, put the write lock request before the read lock request. Lock one table at a time until the session gets all locks. This policy ensures that table locking is deadlock free. There are, however, other things you need to be aware of about this policy: If you are using a LOW_PRIORITY WRITE lock for a table, it means only that MySQL waits for this particular lock until there are no other sessions that want a READ lock. When the session has gotten the WRITE lock and is waiting to get the lock for the next table in the lock table list, all other sessions wait for the WRITE lock to be released. If this becomes a serious problem with your application, you should consider converting some of your tables to transaction-safe tables. Rules for Lock Release When the table locks held by a session are released, they are all released at the same time. A session can release its locks explicitly, or locks may be released implicitly under certain conditions. A session can release its locks explicitly with UNLOCK TABLES. If a session issues a LOCK TABLES statement to acquire a lock while already holding locks, its existing locks are released implicitly before the new locks are granted. If a session begins a transaction (for example, with START TRANSACTION), an implicit UNLOCK TABLES is performed, which causes existing locks to be released. (For additional information about the interaction between table locking and transactions, see Section 12.4.5.1, “Interaction of Table Locking and Transactions”.) If the connection for a client session terminates, whether normally

楼上的太长了。其实这个是不是只有最近的一个lock tables是有效的？

调试易

对一个表加读锁然后再加写锁有什么意义么？

解决方案 »