Java program locks MySQL database

The following experiments are only performed in MYSQL 5.0.45.

Method 1: Use the mysql command to lock the table.

1. Public VoidTest (){
3. String SQL ="Lock tables aa1 write";
4. // Or String SQL = "lock tables aa1 read ";
5. // If you want to lock Multiple tables, lock tables aa1 read, aa2 write ,.....
6. String sql1 ="Select * from aa1";
8. String sql2 ="Unlock tables";
9. Try{
10. This. Pstmt = conn. prepareStatement (SQL );
11. This. Pstmt1 = conn. prepareStatement (sql1 );
12. This. Pstmt2 = conn. prepareStatement (sql2 );
13. Pstmt.exe cuteQuery ();
14. Pstmt1.executeQuery ();
15. Pstmt2.executeQuery ();
17. }Catch(Exception e ){
18. System. out. println ("Exception"+ E. getMessage ());
19. }
21. }

Public void test () {String SQL = "lock tables aa1 write"; // or String SQL = "lock tables aa1 read "; // if you want to lock Multiple tables, lock tables aa1 read, aa2 write ,..... string sql1 = "select * from aa1"; String sql2 = "unlock tables"; try {this. pstmt = conn. prepareStatement (SQL); this. pstmt1 = conn. prepareStatement (sql1); this. pstmt2 = conn.preparestatement(sql21_1_pstmt.executequery(1_1_pstmt1.executequery(1_1_pstmt2.exe cuteQuery ();} catch (Exception e) {System. out. println ("exception" + e. getMessage ());}}

1. Official Instructions on read lock and write lock:
1. If a thread gets the READ lock of a table, the thread (and all other threads) can only READ from the table.
If a thread acquires the WRITE lock of a table, only the locked thread can WRITE data to the table.
Other threads are blocked until the lock is released.

2. When you use lock tables, you must LOCK all the TABLES you intend to use in the query.
Although the LOCKTABLES statement is still valid, you cannot access any tables that are not locked by this statement.
At the same time, you cannot use a locked table multiple times in a query-replace it with an alias,
In this case, you must lock each alias separately.

Ii. personal instructions on read lock and write lock:
1. read lock and write lock are at the Thread level (Table level ).
2. The read lock is added to the same session. read operations can only be performed on this table. No tables other than this table can be added, deleted, modified, or queried.
However, in different sessions, read operations can only be performed on the tables added with read lock. However, you can add, delete, modify, and query tables other than read lock.
3. the write lock is added to the same session. only read and write operations can be performed on this table. you cannot add, delete, modify, or query any table other than this table.
However, in different sessions, read and write operations cannot be performed on tables with write lock added. However, you can add, delete, modify, and query tables other than write lock.
4. If the table uses an alias. (SELECT * FROM aa1 AS byname_table)
When locking aa1, you must add the alias (lock tables aa1 as byname_table read)
In the same session, aliases must be used for query.
In different sessions, aliases are not required for query.
5. you can perform the lock read operation on the same table in multiple sessions. but the lock write operation cannot be performed on the same table in multiple sessions (these locks will wait for the locked tables to release their own thread locks)
If multiple sessions perform the lock read operation on the same table, only the locked tables can be read in these sessions.
6. If you want to lock a table, nested query is required. You must use an alias and lock the alias.
For example, lock table aa1 read, aa1 as byname_table read;
Select * from aa1 where id in (select * from aa1 as xx where id = 2 );
7. unlock tables must be used for unlocking;

In addition:
To unlock a table in a JAVA program, you need to call unlock tables to unlock the table.
If unlock tables is not called.
You can unlock connection, end the program, and call GC.

1 2 Next Page