MySQL lock mechanism--write lock

Mysql Series Articles Home

===============

1 Preparing data

1.1 Build Table

1.1.1 Establishment of employee tables

DROP TABLE IF EXISTS employee; CREATE TABLE IF  not EXISTS Employee (    INTPRIMARYKEY  auto_increment,      VARCHAR(+),    INT) engine MyISAM;

1.1.2 Build Department Table

DROP TABLE IF EXISTS Department; CREATE TABLE IF  not EXISTS Department (    INTPRIMARYKEY  auto_increment,     VARCHAR (+)) engine MyISAM;

1.1.3 Note: The storage engine for both tables is MyISam

1.2 Inserting data

INSERT  into VALUES ('Alice'1); INSERT  into VALUES ('Bob'1);

INSERT  into VALUES ('RD');

2 Testing

2.1 Add a write lock to the Employee table

TABLE employee WRITE;

2.2 Querying Employee Tables

2.2.1 Current session (terminal, command-line window)

Note: The current session is the session (window, command line) that executed the LOCK TABLE employee READ statement above; In my case, it's a white background.

SELECT *  from

2.2.2 Other sessions (terminal, command-line window)

Note: A new session window is opened; In my case, it's a black background.

Queries will be blocked (hang)!

Unlock in ' current session '

After unlocking, ' other sessions ' can be queried normally!

2.2.3 Conclusion

As you can see, the current session can be queried after the write lock is added, but other sessions cannot be queried.

2.3 Updating the Employee table

2.3.0 Preparation

Re-add write lock to Employee table

TABLE employee WRITE;

2.3.1 Current session

UPDATE SET = ' ALICE02 ' WHERE = 1;

2.3.2 Other Sessions

It's still blocked!

Current session Unlocked

Unlock to update

2.3.3 Conclusion

As you can see, when a write lock is added, the current session can be updated (write operations), and updates to other sessions will be blocked.

2.4 Querying other tables

2.4.0 Preparation

Re-add write lock to Employee table

TABLE employee WRITE;

2.4.1 Current session

SELECT *  from

2.4.2 Other Sessions

2.4.3 Conclusion

After a write lock is added, the current session cannot read other tables, and other sessions can read the other tables normally.

3 Conclusion

Write lock, for the current session (Owner), can read the locked table, can also update, but can not read other tables;

For other sessions (other), it is not possible to read locked tables, more unlikely to be updated (blocked), but other tables can be read normally;

The write lock is in its line.

MySQL lock mechanism--write lock