HOW DOES A MYSQL DATABASE SEE WHICH TABLES ARE LOCKED?

Method 1: Leverage the Metadata Locks view.

This method is only applicable to MySQL versions or later, and the performance schema adds Metadata Locks, which can easily locate the global lock session if the probe with metadata lock enabled before locking (the default is not enabled).

Method 2: Use the Events Statements History view This method is suitable for MySQL versions and above, enable Performance is not enabled by default, Enabled by default), the table will be executed by SQL history, if there are too many requests, the early information will be automatically cleaned up, and the information of the locked session may be cleaned up.

Method 3: If you can't use the above two tools or haven't had time to enable them, you can try the third method. Use gdb to find all thread information, view the global lock objects in each thread, and output the corresponding session ID.

You can also use the gdb interactive mode, but after attaching the mysql process, mysql will be completely hung and read requests will also be affected, so it is not recommended to use the interactive mode.

Method 4: Show ProcessList

If the backup program uses a specific user to perform a backup, if it is a root user backup, the higher the time value, the greater the probability of a locked session, if the business is also accessed with root, the focus is that the state and info are empty, here is a small trick to quickly filter, try to kill the corresponding id after filtering, and then observe whether there is a session in the waiting global read lock state.

Method 5: Restart and try!

To view the locked tables of the SQL Server database, you can use the following statement:

You can also use statements like this:

To view the locked tables of the SQL Server database, you can use the following statement:

You can also use statements like this:

Extended Resources:

The difference between locking a table in a database.

select * from table with (holdlock) Other transactions can read the table, but cannot update the deletion.

select * from table with (tablockx) other transactions cannot read, update, and delete tables.

Lock options and their descriptions in the select statement.

nolock

When this option is selected, SQL Server does not add any locks when reading or modifying data. In this case, it is possible for the user to read the data in an uncommited transaction or roll back, the so-called "dirty data".

The nolock statement does not issue a shared lock when executed, allowing dirty reads, which is equal to the read uncommitted transaction isolation level.

HoldLock

When this option is selected, SQL Server retains this shared lock until the end of the entire transaction, without releasing it en route.

HoldLock holds the shared lock until the entire transaction is completed and should be released as soon as the locked object is not needed, equal to the serializable transaction isolation level.

updlock

When this option is selected, SQL Server uses a modification lock instead of a shared lock when reading data, and maintains this lock until the end of the entire transaction or command. Use this option to ensure that multiple processes can read data at the same time, but only that process can modify the data.

tablock (table lock).

When this option is selected, SQL Server will place a shared lock on the entire table until the command ends. This option ensures that other processes can only read the data and not modify it.

paglock

This option is the default option and when selected, SQL Server uses a shared page lock.

Paglock uses multiple page locks where one table lock is used.

TablockX (Exclusive Table Lock).

When this option is selected, SQL Server places an exclusive lock on the entire table until the command or transaction ends. This will prevent other processes from reading or modifying the data in the table. TablockX enforces an exclusive table-level lock that prevents any other transactions from using the table during a transaction.

--View the locked table:

select request_session_id spid,object_name(resource_associated_entity_id) tablename

from where resource_type='object'

SPID lock table process.

tablename.

Unlock: declare @spid int

set @spid = 57 -- the process of locking the table.

declare @sql varchar(1000)set @sql='kill '+cast(@spid as varchar)

exec(@sql)

1.Log in to mysql using a terminal or command prompt, enter the command: mysql -h -p 3306 -u username -p Explanation:

is the database IP address, username is the database user name, after entering the command, you will be asked to enter the password corresponding to the username, you can log in.

2.How Do I View the Deadlock Information of a MySQL Database? Enter the following command in the MySQL client: show engine innodb status g;

3.How to locate the deadlock information of a MySQL database Find the "latest detected deadlock" section in the printed message and look at the red line in the figure.

4.How to analyze the logs and locate the cause of the deadlock Look at the figure in 3, the purple underlined part Analysis: Transaction 1, wait for record locks space id 553 page no 376 n bits 368 index index user id of table tbj

Score User, the x-lock at this location Transaction 2, holds Record Locks Space ID 553 Page No 376 N Bits 368 Index Index User ID of Table TBJscore user S lock in this place Transaction 2, waiting for the X lock in this place Theoretically, this transaction 2 can be committed, deadlock, but this transaction log only prints the last part of the deadlock, information, and the implicit condition is that transaction 1 also holds record locks space id 553 page no 376 n bits 368 index index user id of table tbjscore user This place, so that transaction 2 cannot be locked with x, and transaction 1 cannot be locked with x, resulting in a deadlock.

Method 1: Leverage the Metadata Locks view.

This method is only applicable to MySQL versions or later, and the performance schema adds Metadata Locks, which can easily locate the global lock session if the probe with metadata lock enabled before locking (the default is not enabled).

Method 2: Use the Events Statements History view This method is suitable for MySQL versions and above, enable Performance is not enabled by default, Enabled by default), the table will be executed by SQL history, if there are too many requests, the early information will be automatically cleaned up, and the information of the locked session may be cleaned up.

Method 3: If you can't use the above two tools or haven't had time to enable them, you can try the third method. Use gdb to find all thread information, view the global lock objects in each thread, and output the corresponding session ID.

You can also use the gdb interactive mode, but after attaching the mysql process, mysql will be completely hung and read requests will also be affected, so it is not recommended to use the interactive mode.

Method 4: Show ProcessList

If the backup program uses a specific user to perform a backup, if it is a root user backup, the higher the time value, the greater the probability of a locked session, if the business is also accessed with root, the focus is that the state and info are empty, here is a small trick to quickly filter, try to kill the corresponding id after filtering, and then observe whether there is a session in the waiting global read lock state.

Method 5: Restart and try!

1.Log in to mysql using a terminal or command prompt and enter the command: mysql -h -p 3306 -u username -p

2.How Do I View the Deadlock Information of a MySQL Database?

Enter the command under the mysql client:

show engine innodb status \g;

3.How Do I Locate the Deadlock Information of a MySQL Database?

Find the "latest detected deadlock" section in the printed message and look at the red line in the picture.

4.How to analyze logs and locate the cause of deadlocks?

I look at the figure in 3, and a purple underlined part.

Analysis: Transaction 1, waiting.

record locks space id 553 page no 376 n bits 368 index `index_user_id` of table `tbj`.score user, the x-lock at this position.

Transaction 2, hold.

record locks space id 553 page no 376 n bits 368 index `index_user_id` of table `tbj`.score user The s lock of this place.

Transaction 2, wait for the x-lock of this place.

Theoretically, this transaction 2 can be committed and will not be deadlocked, but this transaction log only prints the last part of the deadlock, the information, and the implicit condition of this is that transaction 1 also holds.

record locks space id 553 page no 376 n bits 368 index `index_user_id` of table `tbj`.score user This place, so that transaction 2 cannot be locked with x, and transaction 1 cannot be locked with x, resulting in a deadlock.