Comparison of databases and their locks

Question

I have a difficult transaction, and you want to get information about how locks are implemented in current databases. The work on the zero budget of my choice is limited to mysql 5.5 and postgres 9.0.

Is there a site that compares locks?

From the literature, I know that you can have read-only and read-write locks, and a good way to handle locks is to block the data path. This means blocking parts of btree. But I can’t find features of how these databases do their job.

Thanks a lot.

Answer 1

Here is a review for PostgreSQL

http://www.postgresql.org/docs/current/static/explicit-locking.html
http://www.postgresql.org/docs/current/static/locking-indexes.html

I'm not sure what you mean by “read-only locking”, but in PostgreSQL the only way to “lock” a table from reading is to manually lock it using ACCESS EXCLUSIVE mode, which is not something that happens with regular statements DML, Only this DDL expression (e.g. ALTER TABLE) will achieve this.

Answer 2

You might find this book useful: inside Microsoft SQL Server 2005: storage engine. Read the chapters on Registration and Recovery and Blocking and concurrency. A lot of the information in this book applies to many of today's database systems. This is a really good book.

I suggest you read about the concurrency control. You can start with the Concurrency_control wiki , especially in the | database transaction and ACID rules

If you want to compare locks, I would first read about the problems encountered with transactions and transaction isolation levels.

Answer 3

Note this: SQL Server 2008 Lock.