CODEWITHSUNDEEP
mysqllocking
Francesco Rizzi
Francesco Rizzi

Reputation: 631

MySQL - No lock while selecting rows in table

I'm starting to study MySQL syntax and now I'm asking how to lock / unlock tables.

After a bit of research, it seems that mysql does not provide a single "nolock" key word.

But if I try to execute the following query:

select *from logs NOLOCK order by timestamp desc;

no errors occur. So, is there a standard way in order to achieve this?

Upvotes: 0

Views: 6317

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562951

NOLOCK is not an option supported by MySQL.

It's a feature specific to Microsoft SQL Server: https://learn.microsoft.com/en-us/sql/t-sql/queries/hints-transact-sql-table

You must understand that even though SQL is a common standard, each company who offers a SQL-compliant database product has implemented their own extensions to standard SQL. Therefore a product like Microsoft SQL Server has some syntax features that are not supported — and not needed — by other RDBMS products.

MySQL is not Microsoft SQL Server. They are two different implementations of RDBMS.

As Raymond commented above, you unintentionally used NOLOCK in a place where it would be interpreted by MySQL as a table alias.

... FROM logs [AS] NOLOCK ...

The SQL standard supports making the AS keyword optional when definining table aliases and column aliases. This can cause some weird surprises, even though it's technically legal syntax to omit the AS keyword.

Upvotes: 1

Related Questions