Quais são as técnicas mais eficazes de prevenção de bloqueios do RDBMS?

I agree with this but also in my experience it is equally if not highly important to take into account proper design and careful consideration of locking sequences. These are essential to ensure this approach works as intended in the specific database environment. When standardized locking protocol is enforced across applications and database sessions,the likelihood of transactions waiting indefinitely for resources held by each other is greatly reduced.

"SELECT column1, column2, column3 FROM your_table_name"; In my professional experience, the utilization of the SQL SELECT statement has been incredibly beneficial for retrieving specific data. This powerful command allowed me to extract essential information from a database by selecting either specific columns or the entirety of data from a designated table. The ability to tailor queries for precise data extraction proved fundamental in managing and extracting information, facilitating accurate retrieval based on specified criteria. This experience underscored the critical role of crafting customized SQL queries to acquire precise data necessary for diverse projects and analyses.

It is also least realistic as your customers may use many different devices to acces your service from many different networks at same time, withour warranty that all their requests will reach the same backends in distributed system.

Always acquire locks in a consistent and predetermined order. For example, if you have multiple tables involved in a transaction, establish a standardized order in which you acquire locks on those tables. This prevents scenarios where transactions lock resources in different sequences, potentially leading to deadlocks. Keep transactions as short as possible. Prolonged transactions increase the likelihood of encountering deadlocks, as the duration for which resources are locked is extended. Well-designed indexes can significantly reduce the time it takes to acquire locks, minimizing the risk of contention. Improperly designed indexes can lead to lock escalation, where coarser-grained locks are acquired, potentially causing conflicts.

Consider your database schema and design. Normalize tables to reduce redundancy, but denormalize for performance if necessary. The goal is to minimize contention on frequently accessed data.

In my opinion I disagree with this option as when you escalate a row level lock to table level lock, it can avoid deadlock but will bring row lock contentions. In Oracle this will be a classic case of enq-TM contention . All the other queries which may not be related to the rows you want to interact with will also have to wait as the lock is escalated to table level.

Lock escalation is a technique used by relational database management systems (RDBMS) to improve performance by reducing the number of locks that need to be maintained. When a transaction acquires a large number of locks, the RDBMS may escalate some of those locks to a higher level, such as a page lock or a table lock. This can significantly reduce the number of locks that need to be tracked and managed, which can improve performance. However, lock escalation can also have some negative consequences. Increased blocking: Degraded data consistency: Here are some tips for avoiding lock escalation: Use appropriate lock granularity: Use row-level locking:

In this era of OLTP (Online Transaction Processing Systems), fairly architected and robust IT systems are required to cater the online workload. Database Locks prevent the data to be corrupt or inconsistent, specially in the presence of millions of transactions executing in the small piece of time.

Timeout with locks will be a risky approach as it would beat the purpose of the locking, locking is a mechanism to ensure serialization and data integrity, we should follow best practices avoid dead lock situation but proper design and queries.

This is really interesting to implement. But one caveat is that it will timeout the lock of both the sessions, which either will re-enter in the deadlock or cancel the transaction.

Good practice is to enable following timeouts on server by default, while giving some users ability to tune them where needed on connection basis: 1) idle in transaction timeout, keep it relatively low, force developers to have only short transactioms in OLTP. 2) statement timeout. Often your users and load balancers will have latency tolerance limits. In no case you want to be in situation where load balancer already told customer that backend timed out, but both app and database have no idea about it and continue computation which will be thrown out when finished with client connection already closed. 3) lastly, as mentioned, lock timeout. Application must resolve some errors with retry+random back-off, and escalate others to the caller.

Lock Timeout is the strategy, where a session holds the lock during a transaction and the other session is waiting for it's lock to end or timeout. This mechanism helps to identify the areas where locks are growing and causing delays in the transactions, which in result the system is chocking or halt. After identification, the solution may be device to overcome the issue by performance tunning or update the architect/design.

I got a few cases where I implemented this. It was a hybrid kind of, where the timeout period was a random value within a given acceptable range (like 10 to 20 seconds) for each thread. Then for those threads that would have timedout will have a retry mechanism to retry x times every y times (where x and y were again random values within an acceptable range.) It was an overkill: I admit, but the application was responsive, reliable, predicatable and stable ever after.

In my experience, you cant fix the problem until you what the problem is, most of the time once we know what the problem is we can easily fix the problem either by optimizing code or adding indexes

In my experience , This is the only option left at runtime . Use a proper deadlock detection mechanism and abort the transaction having less priority . Of course , recovery must be implemented well in advance , to handle aborted transactions further .

In my experience, it is proper indexing in place which helps in avoiding deadlocks which causes intermittent issues on the application.

I personally best option to avoid deadlock is to implement serialization in application. Deadlock happens when two or more session hold cyclic lock on resources needs by other session. Most of the databases automatically handles the deadlock and they just shared the information in logs that deadlock is detected.

In my experience: - Study environment right before implementation of RDBMS. - Make Master DB for write and Salve DB for read. - Load Balancer is must for Salve DB. -Backups and migration plans must be considered in risk management. - Distribution of DB and using Peer to Peer or Async between Master and Salve. This resulted in good performance, high availability, scalability and maintainability of the system.

- Design your schema to ensure reasonably fast writes - not all transactions need to be serializable (this is usually slowest mode, and should be used only then really needed), consider which of transaction isolation modes work best for your product. - sometimes applications middleware, not product owners, decide that each web request needs to be single transaction. Often it means that statistical things (update last login field on requestor) are not relevant to user story, but slow down whole transaction of "add product X to the basket Y". Be sure that in your system transactions are used when needed, not by default. (In most relational databases single statement is also atomic change, even if not wraped in transaction).

After doing everything right and to the book, close attention mustbe paid to the criteria we use to select OR modify records. Watch out to never deploy anything with a criteria(filter) that result in a table scan. We had to review all our Datasets for this and managed to resolve some locking issues on a billing-system-implementation. Another useful approach is to understand how often records are updated. Then for all tables where records are rarely updated consider an approach without any locking at all for such tables. We reaped the benefits when such tables started hitting hundred-thousands of records.